はじめに
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はデータ構造が有向グラフや無向グラフである場合によく使われます。サンプルを見てみましょう。
| ID | NextID |
| 1 | 3 |
| 3 | 5 |
| 5 | null |
| 20 | 23 |
| 23 | 25 |
| 25 | 20 |
(親の行の)NextID = (子の行の)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を列挙してみます。
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を使うと、親子関係があるけど経路上で訪問済であるノードへの再訪問を防いだ階層問い合わせを行うことができます。
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となります。下記がサンプルです。
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疑似列のイメージは、下記となります。赤色のバツ印で親子関係があるけど経路上で訪問済であるノードへの再訪問を防いでます。
