リスト6 実行計画の階層問い合わせ
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