SQLの抽出結果を階層構造で表示するテクニック：SQLクリニック（6）（2/2 ページ）

» 2005年07月28日 00時00分公開

[林優，株式会社インサイトテクノロジー]

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

　では、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キーワードを使用して階層を維持したままソートする必要があります。

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