Database Reference
In-Depth Information
The important thing to notice in this execution plan is that the number of rows returned by operation 2 is limited
to ten. This is true even if operation 2 is a full table scan of a table containing more than 10 rows (actually the table
contains 14 rows). What happens is that operation 1 stops the processing of operation 2 as soon as the necessary
number of rows has been processed. Be careful, though, because blocking operations can't be stopped. In fact, they
need to be fully processed before returning rows to their parent operation. For example, in the following query, all
rows (14) of the
emp
table are read because of the
ORDER BY
clause:
SELECT *
FROM (
SELECT *
FROM emp
ORDER BY sal DESC
)
WHERE rownum <= 10
----------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows |
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 |
|* 1 | COUNT STOPKEY | | 1 | 10 |
| 2 | VIEW | | 1 | 10 |
|* 3 | SORT ORDER BY STOPKEY| | 1 | 10 |
| 4 | TABLE ACCESS FULL | EMP | 1 | 14 |
----------------------------------------------------------
1 - filter(ROWNUM<=10)
3 - filter(ROWNUM<=10)
Optimization of the FILTER Operation
The
FILTER
operation not only applies a filter when its child passes data to it, but in addition, it could decide to
completely avoid the execution of a child and all the dependent operations (grandchild and so on) as well. For
example, in the following query, a filter predicate applied from operation 1 checks whether the value of the bind
variables leads to an empty result set or not. In fact, the query can return rows only if the
:SAL_MIN<=:SAL_MAX
filter
predicate is fulfilled:
SELECT *
FROM emp
WHERE sal BETWEEN :sal_min AND :sal_max
-----------------------------------------------------
| Id | Operation | Name | Starts | A-Rows |
-----------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 0 |
|* 1 | FILTER | | 1 | 0 |
|* 2 | TABLE ACCESS FULL| EMP | 0 | 0 |
-----------------------------------------------------
1 - filter(:SAL_MIN<=:SAL_MAX)
