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)
 
Search WWH ::




Custom Search