Gblog

おもにTips

階層問い合わせ

訳あって階層問い合わせをいじってみました。

 

サンプルデータは、

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;

こんな感じ。

f:id:gooosuke:20180920171337j:plain

 今回は、わかりやすいように 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$

 

 ・・・どうなんだろう。ユーザオブジェクトの階層が深いと便利か?

 

このSQLDBMS_STATS とかで試すと、ORA-01436 が発生します。

ORA-01436: CONNECT BY loop in user data

 これ nocycle オプションを付与すると回避できるのですが、nocycle をつけて DBMS_STATS で試すと、膨大な量のレコードが出力されます。。。
予防のため nocycle は付けずにいます。

 

ご利用は自己責任でお願いします。