Bizarreness / Oddity of START WITH … CONNECT BY in Oracle SQL


Usually, the “start with .. connect by” clause can be used to select data that has a hierarchical relationship (usually some sort of parent->child (boss->employee or thing->parts). For example, see this data hierarchical :

000
    001
        100
            110
                111
                112
    002
        200
        250
    004
999

then the sql scripts like this :
create table tmp_test_connectby (
ORG_ID varchar2(3 byte),
org_parent varchar2(3 byte),
constraint c01 unique (org_id)
);

insert into tmp_test_connectby values (‘000’, null);
insert into tmp_test_connectby values (‘999’, null);

insert into tmp_test_connectby values (‘001’, ‘000’);
insert into tmp_test_connectby values (‘002’, ‘000’);
insert into tmp_test_connectby values (‘004’, ‘000’);

insert into tmp_test_connectby values (‘100’, ‘001’);
insert into tmp_test_connectby values (‘110’, ‘001’);

insert into tmp_test_connectby values (‘111’, ‘110’);
insert into tmp_test_connectby values (‘112’, ‘110’);

insert into tmp_test_connectby values (‘200’, ‘002’);
insert into tmp_test_connectby values (‘250’, ‘002’);

commit;

select lpad(‘ ‘,2*(level-1)) || to_char(org_id) s
from tmp_test_connectby
start with org_parent is null
connect by prior org_id = org_parent;
———————————————
From here, if i want to get result

002
    200
    250

i run this sql,
SELECT org_id
FROM tmp_test_connectby
WHERE org_id IN (
SELECT org_id
FROM tmp_test_connectby
WHERE org_id = :b1
UNION ALL
SELECT org_id
FROM tmp_test_connectby
CONNECT BY org_id = org_parent
START WITH org_parent IN (
SELECT org_id
FROM tmp_test_connectby
WHERE org_id = :b1
UNION ALL
SELECT org_id
FROM tmp_test_connectby
CONNECT BY org_id = org_parent
START WITH org_parent = :b1))
ORDER BY org_id;

but when i make an update to record like this
update tmp_test_connectby
set org_parent = ‘999’
where org_id = ‘999’;

and run this again,
SELECT org_id
FROM tmp_test_connectby
WHERE org_id IN (
SELECT org_id
FROM tmp_test_connectby
WHERE org_id = :b1
UNION ALL
SELECT org_id
FROM tmp_test_connectby
CONNECT BY org_id = org_parent
START WITH org_parent IN (
SELECT org_id
FROM tmp_test_connectby
WHERE org_id = :b1
UNION ALL
SELECT org_id
FROM tmp_test_connectby
CONNECT BY org_id = org_parent
START WITH org_parent = :b1))
ORDER BY org_id;

then you may have your database memory consume to nothing,
you may got this error too :

ORA-04030: out of process memory when trying to allocate 80 bytes (kxs-heap-w,cursor work heap)
ORA-04031: unable to allocate 8224 bytes of shared memory…

so be carefull about using “START WITH … CONNECT BY”, if something wrong, check your data

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: