Database Reference
In-Depth Information
To help you understand the execution plan with a hierarchical query more easily, it's useful to look at the data
returned by the query as well:
LEVEL ENAME MANAGER
----- -------- -------
1 KING
2 -JONES KING
3 --SCOTT JONES
4 ---ADAMS SCOTT
3 --FORD JONES
4 ---SMITH FORD
2 -BLAKE KING
3 --ALLEN BLAKE
3 --WARD BLAKE
3 --MARTIN BLAKE
3 --TURNER BLAKE
3 --JAMES BLAKE
2 -CLARK KING
3 --MILLER CLARK
Applying the rules described earlier, you can see that the execution plan carries out the operations as follows:
1.
Operation 0 has one child (1). It can't be the first operation being executed.
2.
Operation 1 has two children (2 and 3), and operation 2 is the first of them in ascending
order. Therefore, the execution starts with operation 2.
Operation 2 scans the
emp
table, applies the
"MGR" IS NULL
filter predicate, and returns
the root of the hierarchy (KING) to its parent (1).
3.
4.
Operation 3 is the second child of operation 1. It's therefore executed for each level of the
hierarchy—in this case, four times. Naturally, the rules previously discussed for the
NESTED
LOOPS
operation apply for operation 3. The first child (4) is executed and, for each row
it returns, the inner loop (composed of operations 5 and 6) is executed once. Notice, as
expected, the match between the
A-Rows
column of operation 4 with the
Starts
column of
operations 5 and 6.
For the first execution, operation 4 gets the root of the hierarchy through the
CONNECT BY
PUMP
operation. In this case, there's a single row (KING) at level 1. With the value in the
mgr
column, operation 6 does a scan of the
emp_mgr_i
index by applying the
"MGR"=PRIOR
"EMPNO"
access predicate (shown as
"connect$_by$_pump$_002"."PRIOR empno"="MGR"
),
applies the filter predicate
"MGR" IS NOT NULL
, extracts the rowids, and returns them to its
parent (5). Operation 5 accesses the
emp
table with the rowids and returns the rows to its
parent (3).
5.
6.
For the second execution of operation 4, everything works the same as for the first execution.
The only difference is that the data from level 2 (JONES, BLAKE, and CLARK) is passed to
operation 4 for the processing (one by one, each row causing a start of operation 4).
7.
For the third execution of operation 4, everything works like in the first one. The only
difference is that level 3 data (SCOTT, FORD, ALLEN, WARD, MARTIN, TURNER, JAMES,
and MILLER) is passed to operation 4 for the processing.
