Shoeisha Technology Media

CodeZine(コードジン)

特集ページ一覧

Oracleの階層問い合わせ(4)
(connect by nocycle)

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

  • LINEで送る
  • このエントリーをはてなブックマークに追加
2009/11/10 14:00

ダウンロード SourceCode (959.0 B)

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

目次

はじめに

 Oracleの階層問い合わせについて、基本事項から使用例まで、SQLのイメージを交えて解説します。本稿では、connect by nocycleconnect_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 nocycleconnect_by_IsCycle疑似列のイメージは、下記となります。赤色のバツ印で親子関係があるけど経路上で訪問済であるノードへの再訪問を防いでます。

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

  • LINEで送る
  • このエントリーをはてなブックマークに追加

著者プロフィール

バックナンバー

連載:Oracleの階層問い合わせ
All contents copyright © 2005-2019 Shoeisha Co., Ltd. All rights reserved. ver.1.5