Oracleの階層問い合わせ（4）
（connect by nocycle）

有向グラフ、無向グラフへの階層問い合わせ

　Oracleの階層問い合わせについて、基本事項から使用例まで、SQLのイメージを交えて解説します。本稿では、connect by nocycle、connect_by_IsCycle疑似列を扱います。

はじめに

　Oracleの階層問い合わせについて、基本事項から使用例まで、SQLのイメージを交えて解説します。本稿では、connect by nocycle、connect_by_IsCycle疑似列を扱います。

対象読者

• Oracleの階層問い合わせを使いたい方
• OracleのSQLの理解を深めたい方

　connect by nocycleを使用する階層問い合わせは、有向グラフや無向グラフなどのグラフ構造の知識を必要としますので、『グラフ理論 - Wikipedia』などを読まれてからの方が理解しやすいと思います。

必要な環境

　本稿で扱うSQLは、Oracle 10.2.0.1.0で動作確認しました。

1. connect by nocycle

　cycleという英単語は名詞で、意味は「ひと巡り、一巡、周期」です。connect by nocycleを使うと、経路上で訪問済であるノードへの再訪問を防いだ階層問い合わせを行うことができます。

　connect by nocycleはデータ構造が有向グラフや無向グラフである場合によく使われます。サンプルを見てみましょう。

RosenMap

ID	NextID
1	3
3	5
5	null
20	23
23	25
25	20

　(親の行の)NextID = (子の行の)IDを親子条件とし、ID=1の行を木の根として、到達可能なIDを列挙してみます。

ID=1の行を木の根として、到達可能なIDを列挙

select ID,NextID,Level,
sys_connect_by_path(to_char(ID),'-') as path
  from RosenMap
start with ID = 1
connect by prior NextID = ID;

出力結果

ID	NextID	Level	path
1	3	1	-1
3	5	2	-1-3
5	null	3	-1-3-5

　次は、ID=20の行を木の根として、到達可能なIDを列挙してみます。

ORA-01436: ユーザー・データでCONNECT BYのループが発生しました。

select ID,NextID,Level,
sys_connect_by_path(to_char(ID),'-') as path
  from RosenMap
start with ID = 20
connect by prior NextID = ID;
ERROR:
ORA-01436: ユーザー・データでCONNECT BYのループが発生しました。

　start with ID = 20によって、ID=20の行から階層問い合わせが開始されますが、ID=20の行の子供がID=23の行。ID=23の行の子供がID=25の行。ID=25の行の子供がID=20の行。といったデータになっていて、経路上で訪問済であるノードへの再訪問が行われるため、ORA-01436が発生してしまいます。ちなみに、sys_connect_by_path関数で経路を表示してます。

　このようなデータの時には、connect by nocycleを使うと、親子関係があるけど経路上で訪問済であるノードへの再訪問を防いだ階層問い合わせを行うことができます。

ID=20の行を木の根として、到達可能なIDを列挙

select ID,NextID,Level,
sys_connect_by_path(to_char(ID),'-') as path
  from RosenMap
start with ID = 20
connect by nocycle prior NextID = ID;

出力結果

ID	NextID	Level	path
20	23	1	-20
23	25	2	-20-23
25	20	3	-20-23-25

　connect_by_IsCycle疑似列のサンプルも紹介しておきます。connect_by_IsCycle疑似列は、経路上で訪問済であるノードを子供に持てば1、そうでなければ0となります。下記がサンプルです。

connect_by_IsCycle疑似列のサンプル

select ID,NextID,Level,
sys_connect_by_path(to_char(ID),'-') as path,
connect_by_IsCycle as IsCycle
  from RosenMap
start with ID = 20
connect by nocycle prior NextID = ID;

出力結果

ID	NextID	Level	path	IsCycle
20	23	1	-20	0
23	25	2	-20-23	0
25	20	3	-20-23-25	1

　connect by nocycleとconnect_by_IsCycle疑似列のイメージは、下記となります。赤色のバツ印で親子関係があるけど経路上で訪問済であるノードへの再訪問を防いでます。

connect by nocycleとconnect_by_IsCycle疑似列のイメージ