表の結合で階層問い合わせを使う
では、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
階層レベルを条件式に指定することにより、複雑な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の15〜19行、connect by句でそれぞれの関係性を指定します。ここでは、idとparent_idを関連付けるだけでは、非常に時間がかかってしまうため、hash_valueとaddressについても関連付けています。
また、階層問い合わせの場合、order by句やgroup by句を使用すると階層が壊れてしまう場合があります。その場合、order by句を使用するなら、siblingsキーワードを使用して階層を維持したままソートする必要があります。
少しのキーワードを追加するだけで、問い合わせの結果がとても見やすくなります。もっといろいろな使い方があると思いますので、工夫してみてください。（次回に続く）
筆者紹介
Oracleに特化した製品開発、コンサルティングを手掛けるエンジニア集団。大道隆久は緊迫したトラブル現場でも常に冷静沈着であり、スマートに解決へと導いていくシステムコンサルタント。
Copyright © ITmedia, Inc. All Rights Reserved.