Database Reference
In-Depth Information
15.
Block J is basically executed for each row returned by block G. In other words, it's executed
for each row returned by operation 4 to its parent operation (3). In this block, operation 15
scans a table at first and returns the rows to its parent operation (14). Then, operation 16
scans a table and returns the rows to its parent operation (14). After that, operation 14 puts
the rows returned by its children together and returns the result to its parent operation
(13). Finally, operation 13 removes some duplicate rows. Note that this block doesn't
return data to its parent. In fact, the parent is a
FILTER
operation, and the second child is
used to apply a restriction only.
16.
Once operation 3 has applied the filter with the block J, it returns the result to its parent
operation (2).
Operation 2 performs a
GROUP BY
and returns the result to its parent operation (1).
17.
18.
Operation 1 applies a filter and returns the result to the caller.
In summary, note that blocks are executed according to their identifier (from A up to J). Some blocks (A, C, E, F,
and G) are executed once at most, and others (B, D, H, I, and J) might be executed several times (or never), depending
on how many rows are returned by the operations driving them.
Special Cases
The rules described in the previous sections apply to almost all execution plans. Nevertheless, there are some special
cases. Usually you can find out what's going on by looking at the operations, the predicates they apply, the tables
on which they're executed, and their runtime behavior (especially the
Starts
and
A-Rows
columns). The following
subsections cover three examples that are taken from among many possible cases. Note that all examples provided in
the following subsections are excerpts of the output generated by the
special_cases.sql
script.
Subquery in the SELECT Clause
This example shows what an execution plan for a query containing a subquery in the
SELECT
clause looks like. The
query and its execution plan are the following:
SELECT ename, (SELECT dname
FROM dept
WHERE dept.deptno = emp.deptno)
FROM emp
-----------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 |
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 3 | 3 |
|* 2 | INDEX UNIQUE SCAN | DEPT_PK | 3 | 3 |
| 3 | TABLE ACCESS FULL | EMP | 1 | 14 |
-----------------------------------------------------------------
2 - access("DEPT"."DEPTNO"=:B1)
