SQLクリニック（6）　Page 2/2

SQLの抽出結果を階層構造で表示するテクニック

株式会社インサイトテクノロジー
林 優
2005/7/28

■表の結合で階層問い合わせを使う

　では、EMP表をDEPT表と結合して、各部門の部門長を選択するSQL文を記述してみます。

SQL> select
  2      e.empno,
  3      d.dname,
  4      e.ename ename
  5  from
  6      emp e,
  7      dept d
  8  where
  9      e.deptno = d.deptno and
 10      level = 2
 11  start with ename = ( select ename from emp where mgr is null )
 12  connect by prior empno = mgr
 13  /

     EMPNO DNAME          ENAME
---------- -------------- --------------------
      7782 ACCOUNTING     CLARK
      7566 RESEARCH       JONES
      7698 SALES          BLAKE
リスト5　EMP表とDEPT表を結合した階層問い合わせ

　階層レベルを条件式に指定することにより、複雑なSQL文を書かなくても所属長が分かるようになります。もちろん実際に扱うデータはもっと複雑なので一筋縄ではいきませんが、階層問い合わせを使うことにより、すっきりしたSQL文になることもあります。

■実行計画を階層問い合わせしてみる

　実行計画を確認する際に、よくV$SQL_PLANビューを参照します。しかし、トレースを取得して出力された実行計画に慣れてしまうと、V$SQL_PLANビューをSELECTしただけでは、どこで結合されているか分かりにくくなってしまいます。

　そこで、最後に階層問い合わせを使用して、V$SQL_PLANビューの結果を見やすく出力してみます

SQL> select
  2       id,
  3       depth,
  4       lpad(' ', depth) || operation ||
  5       decode(options,null,null,'(' || options || ')') ||
  6       decode(optimizer,null,null,' Optimizer = ' || optimizer )
 operation,
  7       object_name,
  8       cost
  9   from
 10       v$sql_plan
 11   where
 12       hash_value = 2999551167 and
 13       address =  '54ADC9CC'
 14   start with id = 0
 15   connect by
 16       ( prior id = parent_id
 17         and prior hash_value = hash_value
 18         and prior address = address
 19       )
 20  order siblings by id, position;

 ID  DEPTH OPERATION                                               
     OBJECT_NAME  COST
--- ------ --------------------------------------------------------
---- ----------- -----
  0      0 SELECT STATEMENT Optimizer = ALL_ROWS                   
                     4
  1      1  NESTED LOOPS                                           
                     4
  2      2   TABLE ACCESS(BY INDEX ROWID) Optimizer = ANALYZED     
     DEPT            1
  3      3    INDEX(UNIQUE SCAN) Optimizer = ANALYZED              
     PK_DEPT         0
  4      2   TABLE ACCESS(FULL) Optimizer = ANALYZED               
     EMP             3
リスト6　実行計画の階層問い合わせ（別ウィンドウでコードを表示）

　リスト6の15〜19行、connect by句でそれぞれの関係性を指定します。ここでは、idとparent_idを関連付けるだけでは、非常に時間がかかってしまうため、hash_valueとaddressについても関連付けています。

　また、階層問い合わせの場合、order by句やgroup by句を使用すると階層が壊れてしまう場合があります。その場合、order by句を使用するなら、siblingsキーワードを使用して階層を維持したままソートする必要があります。

　少しのキーワードを追加するだけで、問い合わせの結果がとても見やすくなります。もっといろいろな使い方があると思いますので、工夫してみてください。（次回に続く）


筆者プロフィール
株式会社インサイトテクノロジー
Oracleに特化した製品開発、コンサルティングを手掛けるエンジニア集団。林 優は殺伐とした対策現場でも柔らかな言動で問題点を切り分ける誠実な目の中に闘志を持つシステムコンサルタント。

　 2/2  

　Index
連載 SQLクリニック（6）
SQLの抽出結果を階層構造で表示するテクニック
  Page 1
・階層問い合わせの基本
Page 2
・表の結合で階層問い合わせを使う
・実行計画を階層問い合わせしてみる


SQLクリニック

TechTargetジャパン

Database Expert フォーラム 新着記事

＠ITメールマガジン　新着情報やスタッフのコラムがメールで届きます（無料）

RSSフィード

＠IT 新着記事

キャリアアップ

- PR -
＠IT Sepcial
→ ＠IT Special ヘ

イベントカレンダー

PickUpイベント

- PR -
もっと見る
- PR -

お勧め求人情報

ホワイトペーパー（TechTargetジャパン）

＠IT Sepcial
→ ＠IT Special ヘ
ソリューションFLASH