Database Reference
In-Depth Information
9.
Operation 3 gets the rows passed from its children and returns them to its parent (1).
Operation 1 applies the
"MGR" IS NOT NULL
filter predicate. Then, the operation returns
14 rows to its parent (0).
10.
11.
Operation 0 sends the data to the caller.
The execution plan generated up to and including version 10.2.0.3 is slightly different. As can be seen in the
following example, the
CONNECT BY WITH FILTERING
operation has a third child (operation 8). In this case, it wasn't
executed, however. The value in the
Starts
column for operation 8 confirms this. Actually, the third child is executed
only when the
CONNECT BY WITH FILTERING
operation uses temporary space. When that happens, performance might
degrade considerably. This problem, which is fixed as of version 10.2.0.4, is known as bug 5065418:
---------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows |
---------------------------------------------------------------------
|* 1 | CONNECT BY WITH FILTERING | | 1 | 14 |
|* 2 | TABLE ACCESS FULL | EMP | 1 | 1 |
| 3 | NESTED LOOPS | | 4 | 13 |
| 4 | BUFFER SORT | | 4 | 14 |
| 5 | CONNECT BY PUMP | | 4 | 14 |
| 6 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 13 |
|* 7 | INDEX RANGE SCAN | EMP_MGR_I | 14 | 13 |
| 8 | TABLE ACCESS FULL | EMP | 0 | 0 |
---------------------------------------------------------------------
The UNION ALL (RECURSIVE WITH) Operation
The
UNION ALL (RECURSIVE WITH)
operation is available as of version 11.2. It was added to implement the recursive
subquery factoring clause. Hence, it's used for hierarchical queries. Note that there are actually two operations that
are related:
UNION ALL (RECURSIVE WITH) BREADTH FIRST
UNION ALL (RECURSIVE WITH) DEPTH FIRST
As their names suggest, the difference is due to the search clause that you can specify as either
BREADTH FIRST BY
or
DEPTH FIRST BY
.
Here's a sample query and its execution plan:
WITH
e (xlevel, empno, ename, job, mgr, hiredate, sal, comm, deptno)
AS (
SELECT 1, empno, ename, job, mgr, hiredate, sal, comm, deptno
FROM emp
WHERE mgr IS NULL
UNION ALL
SELECT mgr.xlevel+1, emp.empno, emp.ename, emp.job, emp.mgr, emp.hiredate, emp.sal,
FROM emp, e mgr
WHERE emp.mgr = mgr.empno
)
SELECT *
