Oracleの階層問い合わせ

Oracleの階層問い合わせ（5）
（ListAgg関数を模倣）

sys_connect_by_path関数の応用例

山岸賢治[著]

2010/01/19 14:00

ポスト

ダウンロード SourceCode (1.3 KB)

Page 1
Page 2

2. ListAgg関数を模倣（内部結合あり）

　前問と似たような問題ではありますが、今度は内部結合を行ってから、wmsys.wm_concat関数でキーに紐づく行の文字列をまとめたSQLを模倣してみます。サンプルを見てみましょう。

MainTable

ID	Name
1	Mike
2	Jane
3	Greg

SubTable

ID	Val
1	AAA
1	CCC
2	EEE
2	GGG
2	III
3	KKK
4	BBB
4	DDD

　下記のwmsys.wm_concat関数を使ったSQLと同じ結果を取得します。なお、ConcatVal列のValの順序は任意とします。

wmsys.wm_concat関数を使ったSQL

select a.ID,a.Name,wmsys.wm_concat(b.Val) as ConcatVal
  from MainTable a,SubTable b
 where a.ID = b.ID
group by a.ID,a.Name;

出力結果

ID	Name	ConcatVal
1	Mike	AAA,CCC
2	Jane	EEE,III,GGG
3	Greg	KKK

　前問と似た考え方ですが、内部結合してから分析関数のRow_Number関数を使って行に連番を付与して、階層問い合わせを行って葉だけを出力すればいいと考えて、下記が答えとなります。

connect_by_IsLeafで葉か判断する方法

select ID,Name,substr(sys_connect_by_path(Val,','),2) as ConcatVal
  from (select a.ID,a.Name,b.Val,
        Row_Number() over(partition by a.ID order by b.Val) as Rn
          from MainTable a,SubTable b
         where a.ID = b.ID)
 where connect_by_IsLeaf = 1
start with Rn = 1
connect by prior ID = ID
       and prior Rn = Rn-1;

　SQLのイメージは、下記となります。connect by句にprior ID = IDがありますのでIDごとに区切る赤線をイメージして、connect_by_IsLeaf疑似列のイメージとして葉であるノードに緑色を塗ってます。

SQLのイメージ

　connect_by_IsLeafは、Oracle 10g R1以降でないと使えませんので、Oracle 9iでも使えるSQLを紹介しておきます。

葉のレベルを事前に求めておく方法

select ID,Name,substr(sys_connect_by_path(Val,','),2) as ConcatVal
  from (select a.ID,a.Name,b.Val,
        Row_Number() over(partition by a.ID order by b.Val) as Rn,
        count(*) over(partition by a.ID) as MaxLevel
          from MainTable a,SubTable b
         where a.ID = b.ID)
 where Level = MaxLevel
start with Rn = 1
connect by prior ID = ID
       and prior Rn = Rn-1;

グループ化してmax関数を使う方法

select ID,Name,max(substr(sys_connect_by_path(Val,','),2)) as ConcatVal
  from (select a.ID,a.Name,b.Val,
        Row_Number() over(partition by a.ID order by b.Val) as Rn
          from MainTable a,SubTable b
         where a.ID = b.ID)
start with Rn = 1
connect by prior ID = ID
       and prior Rn = Rn-1
group by ID,Name;

3. 依存オブジェクトの追跡

　all_dependenciesデータディクショナリビューを参照すると、オブジェクト間の直接依存性を知ることができます。all_dependenciesデータディクショナリビューに対して階層問い合わせを使って、間接依存性まで調べてみます。サンプルを見てみましょう。

DDL文

create table TableA as select 1 as ColA from dual;
create or replace view VB as select * from TableA;
create or replace view VC as select * from VB;
create or replace view VD as select * from VC;
create or replace view VE as select * from VC;

create or replace view VF as
select * from VD union all
select * from VE;

依存の階層図

　TableAに直接依存するオブジェクトと、間接依存するオブジェクトの一覧を表示してみます。

all_dependenciesに対する階層問い合わせ

select referenced_owner || '.' || referenced_name as RefObj,type,
owner || '.' || name as Object,
decode(Level,1,'直接','間接') as "依存",
substr(sys_connect_by_path(referenced_name,'←'),2) || '←'
|| name as "依存リスト"
  from all_dependencies
start with referenced_name = upper('TableA')
connect by prior owner = referenced_owner
       and prior name  = referenced_name
       and prior type  = referenced_type
order siblings by owner,name,type;

出力結果

RefObj	type	Object	依存	依存リスト
TEST.TABLEA	VIEW	TEST.VB	直接	TABLEA←VB
TEST.VB	VIEW	TEST.VC	間接	TABLEA←VB←VC
TEST.VC	VIEW	TEST.VD	間接	TABLEA←VB←VC←VD
TEST.VD	VIEW	TEST.VF	間接	TABLEA←VB←VC←VD←VF
TEST.VC	VIEW	TEST.VE	間接	TABLEA←VB←VC←VE
TEST.VE	VIEW	TEST.VF	間接	TABLEA←VB←VC←VE←VF

　親子条件が複数ある場合には、下記のように複数列比較を使ってもいいです。

connect by句で、複数列比較を使用

select referenced_owner || '.' || referenced_name as RefObj,type,
owner || '.' || name as Object,
decode(Level,1,'直接','間接') as "依存",
substr(sys_connect_by_path(referenced_name,'←'),2) || '←'
|| name as "依存リスト"
  from all_dependencies
start with referenced_name = upper('TableA')
connect by  (prior owner     ,prior name     ,prior type     )
          =((referenced_owner,referenced_name,referenced_type))
order siblings by owner,name,type;