SHOEISHA iD

※旧SEメンバーシップ会員の方は、同じ登録情報(メールアドレス&パスワード)でログインいただけます

CodeZine編集部では、現場で活躍するデベロッパーをスターにするためのカンファレンス「Developers Summit」や、エンジニアの生きざまをブーストするためのイベント「Developers Boost」など、さまざまなカンファレンスを企画・運営しています。

Oracleの階層問い合わせ

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

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

  • X ポスト
  • このエントリーをはてなブックマークに追加

ダウンロード SourceCode (959.0 B)

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

  • X ポスト
  • このエントリーをはてなブックマークに追加

はじめに

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

会員登録無料すると、続きをお読みいただけます

新規会員登録無料のご案内

  • ・全ての過去記事が閲覧できます
  • ・会員限定メルマガを受信できます

メールバックナンバー

次のページ
2. connect_by_IsCycle疑似列

この記事は参考になりましたか?

  • X ポスト
  • このエントリーをはてなブックマークに追加
Oracleの階層問い合わせ連載記事一覧

もっと読む

この記事の著者

山岸 賢治(ヤマギシ ケンジ)

趣味が競技プログラミングなWebエンジニアで、OracleSQLパズルの運営者。AtCoderの最高レーティングは1204(水色)。

※プロフィールは、執筆時点、または直近の記事の寄稿時点での内容です

この記事は参考になりましたか?

この記事をシェア

  • X ポスト
  • このエントリーをはてなブックマークに追加
CodeZine(コードジン)
https://codezine.jp/article/detail/2691 2009/11/10 14:00

おすすめ

アクセスランキング

アクセスランキング

イベント

CodeZine編集部では、現場で活躍するデベロッパーをスターにするためのカンファレンス「Developers Summit」や、エンジニアの生きざまをブーストするためのイベント「Developers Boost」など、さまざまなカンファレンスを企画・運営しています。

新規会員登録無料のご案内

  • ・全ての過去記事が閲覧できます
  • ・会員限定メルマガを受信できます

メールバックナンバー

アクセスランキング

アクセスランキング