Database Reference
In-Depth Information
Figure 10-3.
Parent-child relationships between stand-alone operations
This execution plan consists only of stand-alone operations. By applying the rules described earlier, you find out
that the execution plan carries out the operations as follows:
1.
Operations 0, 1 and 2 have a single child each (1, 2 and 3, respectively); they can't be the
first operations being executed. Therefore, the execution starts with operation 3.
2.
Operation 3 scans the
emp_job_i
index by applying the
"J0B"='CLERK'
access predicate.
In doing so, it extracts four rowids (this information is given in the
A-Rows
column) from
the index and passes them to its parent (2).
Operation 2 accesses the
emp
table through the four rowids passed from operation 3. For
each rowid, a row is read. Then, it applies the
"SAL"<1200
filter predicate. This filter leads
to the exclusion of one row. The data of the remaining three rows is passed to its parent (1).
3.
Operation 1 performs a
GROUP BY
on the rows passed from operation 2. The resulting set is
reduced to two rows and passed to its parent (0).
4.
5.
Operation 0 sends the data to the caller.
Notice how the
Starts
column clearly shows that each operation is executed only once.
One of the rules states that child operations are entirely executed before parent operations. This is generally
true, but there are situations where smart optimizations are introduced. What can happen is that a parent decides
that it makes no sense to completely execute a child or even that it makes no sense to execute it at all. In other words,
parents control the execution of children. Let's take a look at two common cases. Note that both examples are excerpts
of the output generated by the
stand-alone.sql
script.
Optimization of the COUNT STOPKEY Operation
The
COUNT STOPKEY
operation is commonly used to execute top-n queries. Its aim is to stop the processing as soon as
the required number of rows has been returned to the caller. For example, the aim of the following query is to return
only the first ten rows found in the
emp
table:
SELECT *
FROM emp
WHERE rownum <= 10
-----------------------------------------------------
| Id | Operation | Name | Starts | A-Rows |
-----------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 |
|* 1 | COUNT STOPKEY | | 1 | 10 |
| 2 | TABLE ACCESS FULL| EMP | 1 | 10 |
-----------------------------------------------------
1 - filter(ROWNUM<=10)