Grid without X

We just received new PCs. Brand new with XP. Nice? Let's see!

As usual, I started my Exceed and logged on my AIX server. I tried to start the Grid Control Engine (opmn). Hard luck. Failed to start OC4J instance :-(

The first problem is, when I installed the Grid, my old workstation DISPLAY name has been registered in opmn.xml!
The second problem, my server has no graphic card, and no X server running.
Actually, I do not need X on this server. But to let the Grid start, I will use Frame Buffer. In AIX, you install X11.vfb base package, then mkitab "xvfb:2:once:/usr/lpp/X11/bin/X -force -vfb :1 >tmp/x.txt 2>&1" to start it at the next reboot. Start it now as well.
Then edit your opmn.xml file and set the DISPLAY variable to localhost:1

<variable id="DISPLAY" value="localhost:1"/>

Recursive SQL

One of the most common school exercice about recursion is the factorial. Guess what, I am going to do it in sql with hierarchies!

I use the following ln property :
x1*...*xn = exp(ln(x1)+..+ln(xn)))

Ok, here it is

SQL> select n, (select exp(sum(ln(level))) from dual connect by level<=n) "N!" from t1;

N N!
- ---
4 24
6 720

Get disk space

I just read today on sun.com that checking disk space in java will be platform independent in the java.io.File class before 2007, add a few years until it is integrated in Oracle. But I cannot wait that long, so I decided to write my own code with "df" on my AIX box.

Ok, let's do java. The horrible regexp there is parsing df.

create or replace and compile
java source named "Df"
as
import java.io.*;
public class Df
{
public static int getFree(String args)
{
return Integer.parseInt(df(args).replaceAll("[^0-9]*[ ]*[1 ][0-9 ][0-9][0-9][%-][^ ]* [^ ]*[ ]*[^ ]*[ ]*[^ ]*[ ]*[^ ]*[ ]*[^ ]*[ ]*[^ ]*[ ]*[^ ]*[ ]*[^ ]*","").trim());
}
public static String getFS(String args)
{
return df(args).replaceAll("[^ ]*[ ]*[^ ]*[ ]*[^ ]*[ ]*[1 ][0-9 ][0-9][0-9][%-][^ ]* [^ ]*[ ]*[^ ]*[ ]*[^ ]*[ ]*[^ ]*[ ]*[^ ]*[ ]*[^ /]*","").trim();
}
public static String df(String args)
{
String rc = "";
try
{
Process p = Runtime.getRuntime().exec("/bin/df -kt "+args);
int bufSize = 4096;
BufferedInputStream bis = new BufferedInputStream(p.getInputStream(), bufSize);
int len;
byte buffer[] = new byte[bufSize];
while ((len = bis.read(buffer, 0, bufSize)) != 1)
rc += new String(buffer, 0, len-1);
p.waitFor();
}
catch (Exception e)
{
e.printStackTrace();
}
finally
{
return rc;
}
}
}
/


Now I create two functions

create or replace
function getFree( p_cmd in varchar2) return number
as
language java
name 'Df.getFree(java.lang.String) return int';
/

create or replace
function getFS( p_cmd in varchar2) return varchar2
as
language java
name 'Df.getFS(java.lang.String) return String';
/

Ok, let's see if my files can autoextend

select file_name, BYTES/1024 K, INCREMENT_BY*BYTES/BLOCKS/1024 INC, MAXBYTES/1024 MAXKBYTES, GETFREE(FILE_NAME) FREE, GETFS(FILE_NAME) FS
from dba_data_files

FILE_NAME K INC MAXKBYTES FREE FS
------------------------------------------ ---------- ---------- ---------- ---------- --------------
/dbms/oracle/LSC68/data/system01LSC68.dbf 332800 25600 2097152 3579528 /dev/lsc68
/dbms/oracle/LSC68/data/undo01LSC68.dbf 184320 2048 204800 3579528 /dev/lsc68
/dbms/oracle/LSC68/data/sysaux01LSC68.dbf 228352 25600 2097152 3579528 /dev/lsc68
/dbms/oracle/LSC68/data/users01LSC68.dbf 24576 2048 2097152 3579528 /dev/lsc68
/dbms/oracle/LSC68/data/sysaud01_LSC68.dbf 4096 5120 204800 3579528 /dev/lsc68
/app/oracle/product/10.1.0.3/dbs/t.dbf 1024 0 0 851784 /dev/ora10103

Sounds good! plenty of free space to let the files grow!

One example about hierarchies

Today morning I just received a question from a friend where I used hierarchies :


> Let's assume a couple of persons have bought some cakes togeher and they want to eat it:
>
> Create table cake_owners
> (owner# number,
> cake# number,
> constraint cake_pk primary key (owner#,cake#)
> using index);
>
> insert into cake_owners values (1,100);
> insert into cake_owners values (1,200);
> insert into cake_owners values (2,200);
> insert into cake_owners values (2,300);
> insert into cake_owners values (3,300);
> -----
> insert into cake_owners values (4,500);
> -----
> insert into cake_owners values (6,600);
> insert into cake_owners values (7,600);
> -----
> commit;
>
> So owner 1 owns cake 100 and a part of cake 200. Owner 2 owns a part of cake 200 and a part of cake 300 where the reset is owned by 3.
> Owner 4 owns cake 500 alone and cake 600 is owned by 2 persones 6 and 7.
>
> Now I want to place all owners on one table who share parts of their cake so that all cakes can be eaten compleatly without leaving the table.
> The table must be as small as possible and I want to know how many tables are needed and how big each one must be, or who is sitting at it.
> Of course a person can sit only at one table.
>
> In this much simplyfied example I need 3 tables the biggest one needs 3 chairs.

This is typically solved with PL/SQL, but with plain SQL, I need a hierachy (cake=prior cake and owner<>prior owner) or (cake<>prior cake and owner=prior owner). This is going to loop, with 10g I will use nocycle. With connect by root and count, I will found out the table master with the most guests.

select dense_rank() over (order by rootowner) tableno, owner#
from (
 select owner#,
  row_number() over
   (partition by owner# order by owner_c, rootowner) r,
  rootowner
 from (
  select
   rootowner,
   count(distinct owner#) over
    (partition by rootowner) owner_c,
   owner#
  from (
   select
    owner#, cake#,
    connect_by_root owner# rootowner
   from
    cake_owners
   connect by nocycle
    (owner#<>prior owner# and cake#=prior cake#)
    or
    (owner#=prior owner# and cake#<>prior cake#)
   )
  )
 ) where r=1
order by tableno, owner#;

TABLENO OWNER#
------- ------
1       1
1       2
1       3
2       4
3       6
3       7

Hierarchical queries

The typical hierarchical query is you want to select your boss, and the boss of your boss, etc.
It could look like

select prior ename ename, ename mgr
from emp
connect by prior mgr=empno
start with ename='SCOTT';

SCOTT
SCOTT JONES
JONES KING

I start with Scott and the hierarchy is built. I can use the pseudo column LEVEL in hierarchical queries.
One of the biggest problem in hierarchical queries is ORA-01436: CONNECT BY loop in user data.
If you are your own boss, or if you are the boss of your boss, then you have built a cycle. It is probably not wished to have this relation, but it cannot be enforced by a constraint and before 10g, it was difficult to detect. In 10g, you have a new clause, CONNECT BY NOCYCLE, which detect cycles and give flag.

Here I want to present an alternative way of using hierarchies.
Situation:
I have five boxes of five different sizes. Tiny up to 5 liters. Small up to 10 liters. Medium up to 15 liters. Big up to 20 liters. Hudge up to 25 liters.
I have 3 fluids, and I want to find the smallest boxes.
Yeah! very easy, you do select fluids.volume, min(box.capacity) from fluids, box where capacity>=volume group by volume, do not you?
Ok, but I do not want to mix the fluids! So I need 3 different containers.
This means, I will start with the first product, find the smallest box, go to the second, find the smallest free box, and so on.
Hmm... It seems an impossible task with analytics, least, min, keep, lag, lead, ???
Ok, I am going to build a hierarchy, based on volume > prior volume and capacity > prior capacity.
Then I do a min with the sys_connect_by_path function.

select max(sys_connect_by_path(volume,'/')) volume_path,
ltrim(min(lpad(sys_connect_by_path(capacity,'/'),999))) capacity_path
from box,
(select row_number() over (order by volume) r, count(*) over () c, volume from fluids)
where volume<=capacity and level=c
connect by capacity > prior capacity and r > prior r;

/7/11/14 /10/15/20


There is also one more CONNECT I would like to briefly mention, is the connect without prior.
select level from dual connect by level<11;
It is a special way of creating pivot tables, but it is fairly dangerous, and could make your session / database hang, depending on your oracle version.

Oracle analytics in basic sql queries

When I first saw analytics appearing in Oracle last century, I did not realised they were going to change my way of writting basic SQL queries.

Some (Variance, deviance) are truely mathematical and still reserved for statistical analysis.

Here I will try to describe ROW_NUMBER :

Back in Oracle 7, I remember to have written a lot of reports using in max subqueries, like in

select deptno,ename,sal from emp where (deptno,sal) in (select deptno,max(sal) from emp group by deptno);

With analytics, I can rewrite it with

select deptno,ename,sal from (select emp.*, rank() over (partition by deptno order by sal desc) r from emp) where r=1;

If I want to get exactly one row per deptno, I could then write something like

select deptno,ename,sal from (select emp.*, row_number() over (partition by deptno order by sal desc) r from emp) where r=1;

or better

select deptno,ename,sal from (select emp.*, row_number() over (partition by deptno order by sal desc, empno) r from emp) where r=1;

row_number will select only one row. I prefer the second, because empno is a primary key and the result will be constant over time. In the first solution, I cannot determine which row will be returned, and Oracle may choose one on the morning, and another one in the afternoon, depending on the execution plan (new index/new stats/different load/...).

Note that I can also select the 2nd biggest salary (r=2), or the top 5 (r<6)

It is also very performant, because you are doing only one full table scan instead of two