Database Reference
In-Depth Information
Operation 4 scans the
dual
table and returns one row to its parent (1).
7.
8.
Operation 1 builds a single result set of 19 rows based on all data received from its children
and returns them to its parent (0).
9.
Operation 0 sends the data to the caller.
Notice how the
Starts
column clearly shows that each operation is executed only once.
In Table
10-1
I mention that there are cases where the meaning of the
Starts
column is a bit different. Instead
of the number of executions, the column sometimes provides the number of times a specific memory structure is
accessed. The
MERGE JOIN
operation can be used to show such a case, as the following example illustrates. Notice
how the
Starts
column has the value 4 for operation 4. However, it makes no sense to sort the data four times. What
is happening is that the memory structure is being accessed four times, and hence the value 4 in the
Starts
column.
The structure is accessed once for each row extracted from the
dept
table (Chapter 14 explains in detail how merge
joins are executed):
---------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 14 |
| 1 | MERGE JOIN | | 1 | 14 | 14 |
| 2 | SORT JOIN | | 1 | 4 | 4 |
| 3 | TABLE ACCESS FULL| DEPT | 1 | 4 | 4 |
|* 4 | SORT JOIN | | 4 | 14 | 14 |
| 5 | TABLE ACCESS FULL| EMP | 1 | 14 | 14 |
---------------------------------------------------------------
4 - access("E"."DEPTNO"="D"."DEPTNO")
filter("E"."DEPTNO"="D"."DEPTNO")
All other operations listed earlier have the same behavior as the
UNION-ALL
operation shown in this section. In
short, an unrelated-combine operation sequentially executes its children once. Obviously, the processing performed
by the unrelated-combine operation itself is different.
Related-Combine Operations
I refer to all operations having multiple children where one of the children controls the execution of all other children
as
related-combine operations
. The following operations are of this type:
NESTED LOOPS
,
FILTER
,
UPDATE
,
CONNECT
BY WITH FILTERING
,
UNION ALL (RECURSIVE WITH)
, and
BITMAP KEY ITERATION
. The rules governing the working
of related-combine operations are the ones described in the “Parent-Child Relationship” section with the following
additions:
•
The child with the smallest ID controls the execution of the other children.
•
Children are executed going from the one with the smallest ID to the one with the highest
ID. Contrary to unrelated-combine operations, however, they aren't executed sequentially.
Instead, a kind of interleaving is performed.
•
Only the first child is executed at most once. All other children may be executed several times
or not executed at all.
