Execution Plans - Troubleshooting Oracle Performance - page 332

Here's a sample query and its execution plan (Figure 10-8 shows a graphical representation of its parent-child
relationships). Note that the execution plan was generated on version 11.2 (the reason is explained later):

SELECT level, rpad('-',level-1,'-')||ename AS ename, prior ename AS manager
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr

---------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 |
|* 1 | CONNECT BY WITH FILTERING | | 1 | 14 |
|* 2 | TABLE ACCESS FULL | EMP | 1 | 1 |
| 3 | NESTED LOOPS | | 4 | 13 |
| 4 | CONNECT BY PUMP | | 4 | 14 |
| 5 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 13 |
|* 6 | INDEX RANGE SCAN | EMP_MGR_I | 14 | 13 |
---------------------------------------------------------------------

1 - access("MGR"=PRIOR "EMPNO")
2 - filter("MGR" IS NULL)
6 - access("connect$_by$_pump$_002"."PRIOR empno"="MGR")
filter("MGR" IS NOT NULL)

■ the preceding query represents another situation where the v$sql_plan and v$sql_plan_statistics_all
views give wrong information. in this case, EXPLAIN PLAN shows the correct predicates shown above, the wrongly
displayed predicate is the one associated with operation 1:

Caution

1 - access("MGR"=PRIOR NULL)
Furthermore, the access predicate associated with operation 6 is wrong up to and including version 11.1.

Figure 10-8. Parent-child relationships of the CONNECT BY WITH FILTERING operation

In this execution plan, the first child of the CONNECT BY WITH FILTERING operation is a stand-alone operation.
Instead, the second child is itself a related-combine operation. To read an execution plan in such a situation, you