Database Reference
In-Depth Information
According to the rules described earlier, operation 2 should be the first fully executed operation in the execution
plan shown. In reality, looking at the
Starts
column tells you that only operations 0 and 1 are executed. The
optimization simply avoids processing operation 2, because the data has no chance of going through the filter applied
by operation 1 anyway.
Iterative Operations
I identify all operations that have at most one child that can be executed more than once as
iterative operations
. You
can consider them as operations that implement a sort of loop in an execution plan. The
INLIST ITERATOR
and most
of the operations that have the
PARTITION
suffix (for example,
PARTITION RANGE ITERATOR
; refer to Chapter 13 for a
detailed description of these operations) are of this type. The rules governing the working of iterative operations are
the ones described in the “Parent-Child Relationship” section with, in addition, the following:
•
A child operation may be executed several times or not executed at all.
Here's an example of a query and its execution plan based on the output generated by the
iterative.sql
script:
SELECT *
FROM emp
WHERE job IN ('CLERK', 'ANALYST')
--------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 |
| 1 | INLIST ITERATOR | | 1 | 6 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 2 | 6 |
|* 3 | INDEX RANGE SCAN | EMP_JOB_I | 2 | 6 |
--------------------------------------------------------------------
3 - access(("JOB"='ANALYST' OR "JOB"='CLERK'))
The execution plan is similar to the one discussed for stand-alone operations. The only difference is that part of
the execution plan, because of the
INLIST ITERATOR
operation, can be executed several times. Specifically, the child
of an iterative operation can be executed several times. In this case, operations 2 and 3 are executed once for every
distinct value specified in the
IN
condition.
Unrelated-Combine Operations
I call all operations having multiple children that are independently executed
unrelated-combine operations
. The
following operations are of this type:
AND-EQUAL
,
BITMAP AND
,
BITMAP OR
,
BITMAP MINUS
,
CONCATENATION
,
CONNECT BY
WITHOUT FILTERING
,
HASH JOIN
,
INTERSECTION
,
MERGE JOIN
,
MINUS
,
MULTI-TABLE INSERT
,
SQL MODEL
,
TEMP TABLE
TRANSFORMATION
, and
UNION-ALL
. The rules governing the working of unrelated-combine operations are the ones
described in the “Parent-Child Relationship” section, with the following additions:
•
Children are executed sequentially, starting from the one with the smallest ID and going to the
one with the highest ID. Before starting the processing of a subsequent child, the current one
must be completely executed.
•
A child is executed at most once and independently from all other children.
