訳あって階層問い合わせをいじってみました。
サンプルデータは、
create table test (id number, next number);
insert into test values ( 1 , 2 );
insert into test values ( 1 , 3 );
insert into test values ( 1 , 4 );
insert into test values ( 4 , NULL );
insert into test values ( 2 , 5 );
insert into test values ( 5 , NULL );
insert into test values ( 2 , 6 );
insert into test values ( 6 , NULL );
insert into test values ( 3 , 5 );
insert into test values ( 3 , 7 );
insert into test values ( 7 , 8 );
insert into test values ( 8 , NULL );
commit;
こんな感じ。
今回は、わかりやすいように next = NULL のデータも入れておきます。
データの持ち方はケースによります。
まずは基本形。
select id,next,Level
from test
start with id = 1
connect by prior next = id;
prior next = id は、「親のnext は子のid」という意味になります。
結果、
ID NEXT LEVEL
---------- ---------- ----------
1 2 1
2 5 2
5 3
2 6 2
6 3
1 3 1
3 5 2
5 3
3 7 2
7 8 3
8 4
1 4 1
4 2
ちなみに、next=NULL のデータをいれていない場合は、next=NULL の行は表示されなくなります。
次は、sys_connect_by_path
select id,next,Level,sys_connect_by_path(to_char(id),'/') as Path
from test
start with id = 1
connect by prior next = id;
結果は、
ID NEXT LEVEL PATH
---------- ---------- ---------- ------------
1 2 1 /1
2 5 2 /1/2
5 3 /1/2/5
2 6 2 /1/2
6 3 /1/2/6
1 3 1 /1
3 5 2 /1/3
5 3 /1/3/5
3 7 2 /1/3
7 8 3 /1/3/7
8 4 /1/3/7/8
1 4 1 /1
4 2 /1/4
上の例では、sys_connect_by_path に id を渡していますが、next を渡すと。。。
SQL> select id,next,Level,sys_connect_by_path(to_char(next),'/') as Path 2 from test 3 start with id = 1 4 connect by prior next = id; ID NEXT LEVEL PATH ---------- ---------- ---------- ------------ 1 2 1 /2 2 5 2 /2/5 5 3 /2/5/ 2 6 2 /2/6 6 3 /2/6/ 1 3 1 /3 3 5 2 /3/5 5 3 /3/5/ 3 7 2 /3/7 7 8 3 /3/7/8 8 4 /3/7/8/ 1 4 1 /4 4 2 /4/
ふむふむ。
最後に、connect_by_IsLeaf
select id,next,Level,sys_connect_by_path(to_char(id),'/') as Path
from test
where connect_by_IsLeaf = 1
start with id = 1
connect by prior next = id;
結果、途中までの階層は表示されなくなります。
ID NEXT LEVEL PATH
---------- ---------- ---------- ------------
5 3 /1/2/5
6 3 /1/2/6
5 3 /1/3/5
8 4 /1/3/7/8
4 2 /1/4
いろいろたたいてみるとわかるかも。
で、これをもとに何をしたかったかというと。。。。
DBA_DEPENCENCIES の階層問い合わせ
set verify off
define downer = &1
define dname = &2
select
upper('&downer') || '.' || upper('&dname') || sys_connect_by_path(referenced_owner||'.'||referenced_name,'/') DEPENDENCY
from
DBA_dependencies
where connect_by_IsLeaf = 1
start with owner = upper('&downer') and name = upper('&dname')
connect by prior referenced_owner = owner and prior referenced_name = name
/
これをファイルに保存して、引数に参照しているオブジェクトを調べたい OWNER, OBJECT_NAME を渡してあげると。。。
SQL> @dependency sys dba_objects DEPENDENCY -------------------------------------------------------------------------------- SYS.DBA_OBJECTS/SYS.LINK$ SYS.DBA_OBJECTS/SYS.SUM$ SYS.DBA_OBJECTS/SYS.USER$ SYS.DBA_OBJECTS/SYS._CURRENT_EDITION_OBJ/SYS.OBJ$ SYS.DBA_OBJECTS/SYS._CURRENT_EDITION_OBJ/SYS.USER$
・・・どうなんだろう。ユーザオブジェクトの階層が深いと便利か？
このSQL、DBMS_STATS とかで試すと、ORA-01436 が発生します。
ORA-01436: CONNECT BY loop in user data
これ nocycle オプションを付与すると回避できるのですが、nocycle をつけて DBMS_STATS で試すと、膨大な量のレコードが出力されます。。。
予防のため nocycle は付けずにいます。
ご利用は自己責任でお願いします。
