A blog about Oracle Technology Network Japan
- November 5, 2011
図でイメージするOracle DatabaseのSQL全集 第6回 階層問い合わせ
図でイメージするOracle DatabaseのSQL全集 indexページ▶▶
Oracle SQLの各機能をイメージ図を交えて解説
Oracle ACE
山岸 賢治（やまぎし けんじ）
SQLの初心者から上級者までを広く対象読者として、Oracle SQLの各機能の典型的な使用例を、学習効率が高いと思われる順序で、SQLのイメージ図を交えて解説します。
SQLをイメージつきで理解することで、素早くイメージからSQLを考えられるようになることを目標とします。
目次
- 今回のテーマ
- 階層問い合わせとは
- start with句とconnect by句
- connect by句で複数条件
- start with句の省略
- Level擬似列
- sys_connect_by_path関数
- order siblings by
- connect_by_IsLeaf擬似列
- connect_by_root演算子
- prior演算子
- connect by NoCycle
- connect_by_IsCycle疑似列
- 階層問い合わせでの枝切り
- Level擬似列で枝切り
- 探索終了条件を指定して枝切り
- 訪問経路の列挙
今回のテーマ
今回は、下記のOracleのSQL文の評価順序においての、3番目のstart with句と4番目のconnect by句で主に使われる、階層問い合わせの使用例と、私のSQLのイメージを解説します。
| 1番目 | from句 |
| 2番目 | where句 (結合条件) |
| 3番目 | start with句 |
| 4番目 | connect by句 |
| 5番目 | where句 (行のフィルタ条件) |
| 6番目 | group by句 |
| 7番目 | having句 |
| 8番目 | model句 |
| 9番目 | select句 |
| 10番目 | union、minus、intersectなどの集合演算 |
| 11番目 | order by句 |
動作確認環境
Oracle Database 11g Release 11.2.0.1.0 (windows 32ビット版)
階層問い合わせとは
木構造やグラフ構造に対するselect文
階層問い合わせは、データ構造が木構造の場合や、有向グラフや無向グラフなどのグラフ構造の場合の、select文で主に使用されます。
木構造や深さ優先探索や幅優先探索や有向グラフや無向グラフに関する知識があると、階層問い合わせを理解しやすいです。
参考リソース
@IT AVL木で木構造を学ぼう
@IT データ構造の選択
グラフ理論 - Wikipedia
最強最速アルゴリズマー養成講座 --- 「探索」虎の巻
start with句とconnect by句
木の根となる条件と親子条件
start with句では、木の根となる条件を指定し、connect by句では、ノード同士の親子条件を指定します。
create table BasicSample(ID primary key,OyaID) as select 1,null from dual union all select 2, 1 from dual union all select 3, 1 from dual union all select 4, 2 from dual union all select 5, 3 from dual union all select 10,null from dual union all select 11, 10 from dual union all select 12, 10 from dual;
-- 基本的な階層問い合わせ select ID,OyaID,Level from BasicSample start with OyaID is null connect by prior ID = OyaID;
出力結果 ID OyaID Level -- ----- ----- 1 null 1 2 1 2 4 2 3 3 1 2 5 3 3 10 null 1 11 10 2 12 10 2
階層問い合わせでは、最初に、start with句で木の根となる条件が判定されます。
OyaID is nullを満たすのは、IDが1の行と10の行です。
start with句の段階での、SQLのイメージは下記となります。
次に、connect by句で、ノード同士の親子条件が判定されます。
prior ID = OyaIDを満たせば、親子関係があると判定されます。
prior演算子は、親の行の値であることを意味します。
prior ID = OyaIDは、(親の行の)ID = (子の行の)OyaID という意味になります。
connect by句での、親子条件を元に、木の根からの全ての子孫が行データとして返されます。
connect by句の段階での、SQLのイメージは下記となります。
connect by句で複数条件
親子条件の指定で論理演算
start with句およびconnect by句では、andやorを使った論理演算を行うことができます。
create table ConnBySample( ID number, Seq number, primary key(ID,Seq)); insert into ConnBySample select 111,5 from dual union all select 111,6 from dual union all select 111,7 from dual union all select 222,5 from dual union all select 333,5 from dual union all select 333,6 from dual union all select 333,8 from dual;
木の根となる条件を、Seq = 5の行とし、親子条件を、同じIDで、子のSeqが親より1大きいこととして、階層問い合わせを行います。
-- connect by句で複数条件
select ID,Seq,Level
from ConnBySample
start with Seq = 5
connect by prior ID = ID
and prior Seq = Seq-1;
出力結果 ID Seq Level --- --- ----- 111 5 1 111 6 2 111 7 3 222 5 1 333 5 1 333 6 2
上記のSQLでは、start with句で、Seq = 5を木の根となる条件として指定してます。
start with句の段階での、SQLのイメージは下記となります。
そして、connect by句で指定した、下記の親子条件を満たせば親子関係があると判定されます。
-- 親子条件
prior ID = ID
and prior Seq = Seq-1
connect by句の段階での、SQLのイメージは下記となります。
connect by句にprior ID = IDがありますので、IDごとに区切る赤線をイメージしてます。
参考までに、connect by句で、必ずFalseとなる条件を指定した階層問い合わせを実行してみます。
(木の根だけが表示されます)
-- connect by句でFalseを指定 select ID,Seq,Level from ConnBySample start with Seq = 5 connect by 1=0;
出力結果 ID Seq Level --- --- ----- 111 5 1 222 5 1 333 5 1
start with句の省略
全ての行を、木の根とする
start with句を省略して階層問い合わせを行うことができます。
create table staWithSample(Val primary key) as select 5 from dual union all select 6 from dual union all select 7 from dual union all select 8 from dual;
-- start with句の省略 select Val,Level, sys_connect_by_path(to_char(Val),',') as Path from staWithSample connect by prior Val = Val-1;
出力結果 Val Level Path --- ----- -------- 5 1 ,5 6 2 ,5,6 7 3 ,5,6,7 8 4 ,5,6,7,8 6 1 ,6 7 2 ,6,7 8 3 ,6,7,8 7 1 ,7 8 2 ,7,8 8 1 ,8
start with句が省略されると、全ての行が、木の根となります。
start with句の段階での、SQLのイメージは下記となります。
そして、connect by句で指定した、prior Val = Val-1を満たせば親子関係があると判定されます。
connect by句の段階での、SQLのイメージは下記となります。木ごとに区切る赤線をイメージしてます。
参考までに、start with句で、必ずFalseとなる条件を指定してみます。
(結果は空集合になります)
-- start with句でFalseを指定 select Val,Level, sys_connect_by_path(to_char(Val),',') as Path from staWithSample start with 1=0 connect by prior Val = Val-1;
出力結果 -------------------------------- レコードが選択されませんでした。
Level擬似列
ノードのレベルを表示
Level擬似列は、ノードのレベルを表します。
start with句の条件を満たしたノードのレベルが1となり、子供はレベル2、孫はレベル3といった感じでレベルが増えていきます。
create table LevelSample(ID,NextID) as select 1, 2 from dual union all select 2, 3 from dual union all select 3, 4 from dual union all select 3, 5 from dual union all select 4,null from dual union all select 5, 6 from dual union all select 6,null from dual;
木の根となる条件を、ID = 1
親子条件を、親のNextID = 子のID
として階層問い合わせを行います。
-- Level擬似列の使用例1 select ID,NextID,Level, sys_connect_by_path(to_char(ID),',') as Path from LevelSample start with ID = 1 connect by prior NextID = ID;
出力結果 ID NextID Level Path -- ------ ----- ---------- 1 2 1 ,1 2 3 2 ,1,2 3 4 3 ,1,2,3 4 null 4 ,1,2,3,4 3 5 3 ,1,2,3 5 6 4 ,1,2,3,5 6 null 5 ,1,2,3,5,6
Level擬似列のイメージは下記となります。
下記のSQLのように、レベルの上限を3とした階層問い合わせを行ったり、where句でレベルが1または3の行を抽出するといったLevel擬似列の使用法もあります。
-- Level擬似列の使用例2
select ID,NextID,Level,
sys_connect_by_path(to_char(ID),',') as Path
from LevelSample
where Level in(1,3)
start with ID = 1
connect by prior NextID = ID
and Level <=3;
出力結果 ID NextID Level Path -- ------ ----- ------ 1 2 1 ,1 3 4 3 ,1,2,3 3 5 3 ,1,2,3
sys_connect_by_path関数
根からの経路を表示
sys_connect_by_path関数は、根からの経路を表します。
-- sys_connect_by_path関数の使用例 select ID,NextID,Level, sys_connect_by_path(to_char(ID),',') as Path from LevelSample start with ID = 1 connect by prior NextID = ID;
出力結果 ID NextID Level Path -- ------ ----- ---------- 1 2 1 ,1 2 3 2 ,1,2 3 4 3 ,1,2,3 4 null 4 ,1,2,3,4 3 5 3 ,1,2,3 5 6 4 ,1,2,3,5 6 null 5 ,1,2,3,5,6
sys_connect_by_path関数のイメージは、下記となります。
connect by句では、sys_connect_by_path関数を使うことはできません。
-- ORA-30002: ここではSYS_CONNECT_BY_PATH関数を使用できません
select ID,NextID
from LevelSample
start with ID = 1
connect by prior NextID = ID
and instr(sys_connect_by_path(to_char(ID),','),'1,2,3') = 0;
where句でも、sys_connect_by_path関数を使うことはできません。
-- ORA-30002: ここではSYS_CONNECT_BY_PATH関数を使用できません select ID,NextID from LevelSample where instr(sys_connect_by_path(to_char(ID),','),'1,2,3') = 0 start with ID = 1 connect by prior NextID = ID;
order siblings by
木の階層を崩さずにソート
siblingsというのは英語で、兄弟という意味です。
order by句で、order siblings byと指定すると、木の階層を崩さずにソートできます。
create table siblingsSample(ID primary key,OyaID) as select 1,null from dual union all select 2, 1 from dual union all select 3, 1 from dual union all select 4, 3 from dual union all select 5, 3 from dual union all select 20,null from dual union all select 21, 20 from dual union all select 22, 21 from dual union all select 23, 20 from dual;
-- order siblings byの使用例 select connect_by_root ID as treeID, ID,OyaID,Level, sys_connect_by_path(to_char(ID),',') as Path from siblingsSample start with OyaID is null connect by prior ID = OyaID order siblings by ID desc;
出力結果
treeID ID OyaID Level Path
------ -- ----- ----- ---------
20 20 null 1 ,20
20 23 20 2 ,20,23
20 21 20 2 ,20,21
20 22 21 3 ,20,21,22
1 1 null 1 ,1
1 3 1 2 ,1,3
1 5 3 3 ,1,3,5
1 4 3 3 ,1,3,4
1 2 1 2 ,1,2
SQLのイメージは下記となります。木ごとに区切る赤線をイメージしてます。
order siblings by ID descを指定してますので、下記の順序で行を返します。
start with句の条件を満たす根を選ぶ段階で、IDの大きいほうから選ばれる。
根からの深さ優先探索でも、子供が複数あったらIDの大きいほうから選ばれる。
そして深さ優先探索の行きがけ順で行を返す。
connect_by_IsLeaf擬似列
ノードが木の葉かを表す
connect_by_IsLeaf疑似列は、そのノードが木の葉であれば1、木の葉でなければ0となります。
create table IsLeafSample(ID primary key,OyaID) as select 1,null from dual union all select 2, 1 from dual union all select 3, 1 from dual union all select 4, 3 from dual union all select 5, 3 from dual union all select 10,null from dual union all select 11, 10 from dual;
-- connect_by_IsLeaf擬似列の使用例 select ID,OyaID,Level,connect_by_IsLeaf as IsLeaf, sys_connect_by_path(to_char(ID),',') as Path from IsLeafSample start with OyaID is null connect by prior ID = OyaID;
出力結果 ID OyaID Level IsLeaf Path -- ----- ----- ------ ------ 1 null 1 0 ,1 2 1 2 1 ,1,2 3 1 2 0 ,1,3 4 3 3 1 ,1,3,4 5 3 3 1 ,1,3,5 10 null 1 0 ,10 11 10 2 1 ,10,11
connect_by_IsLeaf疑似列のイメージは、下記となります。
木ごとに区切る赤線をイメージして、葉であるノードに緑色を塗ってます。
connect_by_IsLeaf疑似列の使用法としては、下記のSQLのように、where句でconnect_by_IsLeaf = 1を指定して、木の葉である行のみを取得することが多いです。
-- 木の葉である行のみを取得 select ID,OyaID,Level, sys_connect_by_path(to_char(ID),',') as Path from IsLeafSample where connect_by_IsLeaf = 1 start with OyaID is null connect by prior ID = OyaID;
出力結果 ID OyaID Level Path -- ----- ----- ------ 2 1 2 ,1,2 4 3 3 ,1,3,4 5 3 3 ,1,3,5 11 10 2 ,10,11
connect_by_root演算子
木の根である行の値を取得
connect_by_root演算子は、木の根である行の値を取得するのに使います。
下記のSQLでは、木の根である行のID列を、列別名rootIDとして取得しています。
-- connect_by_root演算子の使用例 select ID,OyaID, connect_by_root ID as rootID, sys_connect_by_path(to_char(ID),',') as Path from IsLeafSample start with OyaID is null connect by prior ID = OyaID;
出力結果 ID OyaID rootID Path -- ----- ------ ------ 1 null 1 ,1 2 1 1 ,1,2 3 1 1 ,1,3 4 3 1 ,1,3,4 5 3 1 ,1,3,5 10 null 10 ,10 11 10 10 ,10,11
connect_by_root演算子のイメージは、下記となります。
木ごとに区切る赤線をイメージして、根であるノードに茶色を塗ってます。
下記のSQLのように、connect_by_root演算子を使って、木ごとに幅優先探索順で出力するといった使用法もあります。
-- 木ごとに幅優先探索順に出力 select ID,OyaID,Level, sys_connect_by_path(to_char(ID),',') as Path from IsLeafSample start with OyaID is null connect by prior ID = OyaID order by connect_by_root ID,Level,ID;
出力結果 ID OyaID Level Path -- ----- ----- ------ 1 null 1 ,1 2 1 2 ,1,2 3 1 2 ,1,3 4 3 3 ,1,3,4 5 3 3 ,1,3,5 10 null 1 ,10 11 10 2 ,10,11
prior演算子
親の行の値を取得
prior演算子は、主にconnect by句で使われますが、select句で使用して、親の行の値を取得するといった使用法もあります。
create table PriorSample(ID primary key,OyaID,Name) as select 1,null,'AAAA' from dual union all select 2, 1,'BBBB' from dual union all select 3, 1,'CCCC' from dual union all select 4, 3,'DDDD' from dual union all select 5, 3,'EEEE' from dual union all select 10,null,'FFFF' from dual union all select 11, 10,'GGGG' from dual;
-- prior演算子の使用例 select ID,OyaID,Name,prior Name as OyaName, Level,sys_connect_by_path(to_char(ID),',') as Path from PriorSample start with OyaID is null connect by prior ID = OyaID;
出力結果 ID OyaID Name OyaName Level Path -- ----- ---- ------- ----- ------- 1 null AAAA null 1 ,1 2 1 BBBB AAAA 2 ,1,2 3 1 CCCC AAAA 2 ,1,3 4 3 DDDD CCCC 3 ,1,3,4 5 3 EEEE CCCC 3 ,1,3,5 10 null FFFF null 1 ,10 11 10 GGGG FFFF 2 ,10,11
SQLのイメージは下記となります。木ごとに区切る赤線をイメージしてます。
connect by NoCycle
再訪問を防いだ階層問い合わせ
cycleというのは英語で、「ひと巡り、一巡、周期」という意味です。
connect by NoCycleを使うと、経路上で訪問済であるノードへの再訪問を防いだ階層問い合わせを行うことができます。
connect by NoCycleはデータ構造が有向グラフや無向グラフである場合によく使われます。
create table NoCycleSample(ID primary key,NextID) as select 1, 2 from dual union all select 2, 3 from dual union all select 3,null from dual union all select 50, 51 from dual union all select 51, 52 from dual union all select 52, 50 from dual;
木の根となる条件を、ID = 1
親子条件を、親のNextID = 子のID
として階層問い合わせを行います。
-- 閉路のない階層問い合わせ select ID,NextID,Level, sys_connect_by_path(to_char(ID),',') as Path from NoCycleSample start with ID = 1 connect by prior NextID = ID;
出力結果 ID NextID Level Path -- ------ ----- ------ 1 2 1 ,1 2 3 2 ,1,2 3 null 3 ,1,2,3
次は、木の根となる条件を、ID = 50
親子条件を、親のNextID = 子のID
として階層問い合わせを行います。
-- 閉路のある階層問い合わせ1 select ID,NextID,Level, sys_connect_by_path(to_char(ID),',') as Path from NoCycleSample start with ID = 50 connect by prior NextID = ID;
出力結果 ERROR: ORA-01436: ユーザー・データでCONNECT BYのループが発生しました
start with ID = 50によって、ID=50の行を根とした階層問い合わせが行われますが、
ID=50の行の子供が、ID=51の行。
ID=51の行の子供が、ID=52の行。
ID=52の行の子供が、ID=50の行。
といったデータになっていて、経路上で訪問済であるノードへの再訪問が行われるため、
ORA-01436が発生してしまいます。
このように閉路のあるデータ構造の時には、connect by NoCycleを使うと、親子関係があるけど経路上で訪問済であるノードへの再訪問を防いだ階層問い合わせを行うことができます。
connect by NoCycleは、connect_by_IsCycle疑似列と併用できます。
connect_by_IsCycle疑似列は、経路上で訪問済であるノードを子供に持てば1、そうでなければ0となります。
-- 閉路のある階層問い合わせ2 select ID,NextID,Level, sys_connect_by_path(to_char(ID),',') as Path, connect_by_IsCycle as IsCycle from NoCycleSample start with ID = 50 connect by NoCycle prior NextID = ID;
出力結果 ID NextID Level Path IsCycle -- ------ ----- --------- ------- 50 51 1 ,50 0 51 52 2 ,50,51 0 52 50 3 ,50,51,52 1
connect by NoCycleとconnect_by_IsCycle疑似列のイメージは、下記となります。
赤色のバツ印で、親子関係があるけど経路上で訪問済であるノードへの再訪問を防いでます。
connect_by_IsCycle疑似列
訪問済ノードを子供に持つかを表す
移動先を複数持てる有向グラフを対象とした階層問い合わせで、connect_by_IsCycle疑似列を使ってみます。
create table IsCycleSample( ID primary key,NextID1,NextID2) as select 1, 2, 4 from dual union all select 2, 3,null from dual union all select 3, 4,null from dual union all select 4, 5,null from dual union all select 5, 2,null from dual;
木の根となる条件を、ID = 1
親子条件を、親のNextID1 = 子のID または 親のNextID2 = 子のID
として階層問い合わせを行い、
(同じノードへ再訪問しない)各ノードまでの経路を列挙します。
-- connect_by_IsCycle擬似列の使用例 select ID,Level,sys_connect_by_path(to_char(ID),',') as Path, connect_by_IsCycle as IsCycle from IsCycleSample start with ID = 1 connect by NoCycle ID in(prior NextID1,prior NextID2);
出力結果 ID Level Path IsCycle -- ----- ---------- ------- 1 1 ,1 0 2 2 ,1,2 0 3 3 ,1,2,3 0 4 4 ,1,2,3,4 0 5 5 ,1,2,3,4,5 1 4 2 ,1,4 0 5 3 ,1,4,5 0 2 4 ,1,4,5,2 0 3 5 ,1,4,5,2,3 1
上記の結果から、connect_by_IsCycle疑似列は、経路上で訪問済であるノードを子供に持てば1、そうでなければ0となることが確認できます。
SQLのイメージは下記となります。有向グラフをイメージしてます。
階層問い合わせでの枝切り
無駄な探索を打ち切る
深さ優先探索や幅優先探索において、無駄な探索を打ち切ることを「枝切り」もしくは「枝刈り」といいます。
Oracleの階層問い合わせでは、親子条件を満たす行を繰り返し取得していきますが、
connect by句で条件を指定して、無駄な繰り返しを打ち切る「枝切り」を行うことができます。
Oracleの階層問い合わせで使われる枝切りは、
- Level擬似列を使った枝切り
- 探索終了条件を指定した枝切り
- 上記2つの組み合わせ
となります。
参考リソース
Level擬似列で枝切り
ノードのレベルの制限
create table LevelEdaKiri(ID primary key,OyaID) as select 1,null from dual union all select 2, 1 from dual union all select 3, 2 from dual union all select 4, 2 from dual union all select 5, 3 from dual union all select 6, 3 from dual union all select 7, 4 from dual union all select 10,null from dual union all select 11, 10 from dual union all select 12, 11 from dual union all select 13, 12 from dual union all select 14, 13 from dual;
枝切りを行わない、connect by句で親子条件のみを指定した階層問い合わせを実行してみます。
-- connect by句で親子条件のみを指定 select ID,OyaID,Level, sys_connect_by_path(to_char(ID),',') as Path from LevelEdaKiri start with OyaID is null connect by prior ID = OyaID --親子条件 order by Path;
出力結果 ID OyaID Level Path -- ----- ----- --------------- 1 null 1 ,1 2 1 2 ,1,2 3 2 3 ,1,2,3 5 3 4 ,1,2,3,5 6 3 4 ,1,2,3,6 4 2 3 ,1,2,4 7 4 4 ,1,2,4,7 10 null 1 ,10 11 10 2 ,10,11 12 11 3 ,10,11,12 13 12 4 ,10,11,12,13 14 13 5 ,10,11,12,13,14
SQLのイメージは下記となります。木を区切る赤線をイメージしてます。
今度は、Level擬似列で枝切りを行う階層問い合わせを実行します。
親子条件であるprior ID = OyaIDと枝切り条件としてのLevel <= 3
をandでつなげた論理積をconnect by句で指定します。
これにより親子条件を満たしたとしても、Levelが4以上のノードは出力されなくなります。
なお、枝切りでは、枝を切る条件の否定条件をconnect by句に記述する必要があります。
例えば、Levelが8以上のノードを枝切りしたいのであれば、
connect by句には、Level <= 7と記述するか、Not述語を使って、Not(Level >= 8)
といったふうに、枝を切る条件の否定条件を記述する必要があります。
-- Level擬似列で枝切り
select ID,OyaID,Level,
sys_connect_by_path(to_char(ID),',') as Path
from LevelEdaKiri
start with OyaID is null
connect by prior ID = OyaID --親子条件
and Level <= 3 --枝切り条件
order by Path;
出力結果 ID OyaID Level Path -- ----- ----- --------- 1 null 1 ,1 2 1 2 ,1,2 3 2 3 ,1,2,3 4 2 3 ,1,2,4 10 null 1 ,10 11 10 2 ,10,11 12 11 3 ,10,11,12
SQLのイメージは下記となります。木を区切る赤線をイメージしてます。
探索終了条件を指定して枝切り
特定値を持つノードを訪問したら探索を打ち切る
create table PriorEdaKiri(ID primary key,OyaID) as select 1,null from dual union all select 2, 1 from dual union all select 3, 2 from dual union all select 4, 2 from dual union all select 5, 3 from dual union all select 6, 4 from dual union all select 50,null from dual union all select 51, 50 from dual union all select 52, 51 from dual;
枝切りを行わない、connect by句で親子条件のみを指定した階層問い合わせを実行してみます。
-- connect by句で親子条件のみを指定 select ID,OyaID,Level, sys_connect_by_path(to_char(ID),',') as Path from PriorEdaKiri start with OyaID is null connect by prior ID = OyaID --親子条件 order by Path;
出力結果 ID OyaID Level Path -- ----- ----- --------- 1 null 1 ,1 2 1 2 ,1,2 3 2 3 ,1,2,3 5 3 4 ,1,2,3,5 4 2 3 ,1,2,4 6 4 4 ,1,2,4,6 50 null 1 ,50 51 50 2 ,50,51 52 51 3 ,50,51,52
SQLのイメージは下記となります。木を区切る赤線をイメージしてます。
今度は、探索終了条件を指定して枝切りを行う階層問い合わせを実行します。
親子条件であるprior ID = OyaIDと枝切り条件としてのprior ID not in(2,51)
をandでつなげた論理積をconnect by句で指定します。
これにより親子条件を満たしたとしても、親ノードのIDが2または51であるノードとその子孫は出力されなくなります。
-- 探索終了条件を指定して枝切り
select ID,OyaID,Level,
sys_connect_by_path(to_char(ID),',') as Path
from PriorEdaKiri
start with OyaID is null
connect by prior ID = OyaID --親子条件
and prior ID not in(2,51) --枝切り条件
order by Path;
出力結果 ID OyaID Level Path -- ----- ----- ------ 1 null 1 ,1 2 1 2 ,1,2 50 null 1 ,50 51 50 2 ,50,51
SQLのイメージは下記となります。木を区切る赤線をイメージしてます。
訪問経路の列挙
枝切りの使用例
create table EdaKiriSample( ID primary key,NextID1,NextID2) as select 'A','B','E' from dual union all select 'B','C','D' from dual union all select 'C','D',null from dual union all select 'D','G','H' from dual union all select 'E','F',null from dual union all select 'F','B','G' from dual union all select 'G','H',null from dual union all select 'H','F',null from dual;
階層問い合わせでの枝切りの使用例として、下記の有向グラフでAからGに到着する経路を求めます。
同じノードへの再訪は不許可とし、AとG以外のノードは最大で3つしか訪問できないとします。
-- 訪問経路を列挙
select Level,sys_connect_by_path(ID,',') as Path
from EdaKiriSample
where ID = 'G'
start with ID = 'A'
connect by NoCycle ID in(prior NextID1,
prior NextID2)
and prior ID != 'G'
and Level <= 5
order by Level,Path;
出力結果
Level Path
----- ----------
4 ,A,B,D,G
4 ,A,E,F,G
5 ,A,B,C,D,G
Gに到着したら探索を打ち切るということで、connect by句でprior ID != 'G'を指定し、
AとG以外のノードは最大で3つしか訪問できないのでLevel <= 5を指定してます。
そして、where ID = 'G'を指定して、Gまでの経路を列挙してます。
参考リソース
"図でイメージするOracle DatabaseのSQL全集" インデックスに戻る
| Copyright © 2011, Oracle Corporation Japan. All rights reserved. 無断転載を禁ず |
| この文書はあくまでも参考資料であり、掲載されている情報は予告なしに変更されることがあります。日本オラクル社は本書の内容に関していかなる保証もいたしません。また、本書の内容に関連したいかなる損害についても責任を負いかねます。 Oracleは米国Oracle Corporationの登録商標です。文中に参照されている各製品名及びサービス名は米国Oracle Corporationの商標または登録商標です。その他の製品名及びサービス名はそれぞれの所有者の商標または登録商標の可能性があります。 |
山岸 賢治（やまぎし けんじ）
Oracle ACEの1人。
OracleSQLパズルの運営者。