X

A blog about Oracle Technology Network Japan

  • November 5, 2011

図でイメージするOracle DatabaseのSQL全集 第6回 階層問い合わせ

Guest Author

図でイメージするOracle DatabaseのSQL全集 indexページ▶▶

 


Oracle SQLの各機能をイメージ図を交えて解説

Oracle ACE
山岸 賢治(やまぎし けんじ)ACE

SQLの初心者から上級者までを広く対象読者として、Oracle SQLの各機能の典型的な使用例を、学習効率が高いと思われる順序で、SQLのイメージ図を交えて解説します。
SQLをイメージつきで理解することで、素早くイメージからSQLを考えられるようになることを目標とします。

目次

 

今回のテーマ

今回は、下記の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のイメージは下記となります。

start with句の段階のイメージ

次に、connect by句で、ノード同士の親子条件が判定されます。
prior ID = OyaIDを満たせば、親子関係があると判定されます。

prior演算子は、親の行の値であることを意味します。
prior ID = OyaIDは、(親の行の)ID = (子の行の)OyaID という意味になります。

connect by句での、親子条件を元に、木の根からの全ての子孫が行データとして返されます。
connect by句の段階での、SQLのイメージは下記となります。

connect by句の段階のイメージ

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のイメージは下記となります。

start with句の段階のイメージ

そして、connect by句で指定した、下記の親子条件を満たせば親子関係があると判定されます。

-- 親子条件
    prior ID  = ID
and prior Seq = Seq-1

connect by句の段階での、SQLのイメージは下記となります。
connect by句にprior ID = IDがありますので、IDごとに区切る赤線をイメージしてます。

connect by句の段階のイメージ

参考までに、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のイメージは下記となります。

start with句の段階のイメージ

そして、connect by句で指定した、prior Val = Val-1を満たせば親子関係があると判定されます。
connect by句の段階での、SQLのイメージは下記となります。木ごとに区切る赤線をイメージしてます。

connect by句の段階のイメージ

参考までに、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擬似列のイメージは下記となります。

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関数のイメージは、下記となります。

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のイメージは下記となります。木ごとに区切る赤線をイメージしてます。

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疑似列のイメージ

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演算子のイメージは、下記となります。
木ごとに区切る赤線をイメージして、根であるノードに茶色を塗ってます。

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のイメージは下記となります。木ごとに区切る赤線をイメージしてます。

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 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のイメージは下記となります。有向グラフをイメージしてます。

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のイメージは下記となります。木を区切る赤線をイメージしてます。

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のイメージは下記となります。木を区切る赤線をイメージしてます。

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のイメージは下記となります。木を区切る赤線をイメージしてます。

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のイメージは下記となります。木を区切る赤線をイメージしてます。

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パズルの運営者。


ページトップへ戻る▲

 

図でイメージするOracle DatabaseのSQL全集 indexページ▶▶

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.