Database Reference
In-Depth Information
Operation 2 scans the
emp
table, applies the
"EMP"."COMM" IS NULL
filter predicate and
passes the data of ten rows to its parent (1).
3.
For each row returned by operation 2, the second child of the
NESTED LOOPS
operation,
the inner loop, is executed once. This is confirmed by comparing the
A-Rows
column of
operation 2 with the
Starts
column of operations 3 and 4.
4.
5.
The inner loop is composed of two stand-alone operations. Based on the rules that apply
to this type of operation, operation 4 is executed before operation 3.
Operation 4 scans the
dept_pk
index by applying the
"EMP"."DEPTNO"=
"DEPT"."DEPTNO"
access predicate. In doing so, it extracts ten rowids from the index over
the ten executions and passes them to its parent (3).
6.
Operation 3 accesses the
dept
table through the ten rowids passed from operation 4. For
each rowid, a row is read. Then it applies the
"DEPT"."DNAME"<>'SALES'
filter predicate.
This filter leads to the exclusion of two rows. It passes the data of the remaining eight rows
to its parent (1).
7.
8.
Operation 1 passes the eight rows to its parent (0)
9.
Operation 0 sends the data to the caller.
The FILTER Operation
The particular characteristic of this operation is that it supports a varying number of children. If it has a single child,
it's considered a stand-alone operation. If it has two or more children, its function is similar to the
NESTED LOOPS
operation. The first child drives the execution of the other children.
To illustrate, the following query and its execution plan are given (Figure
10-6
shows a graphical representation of
its parent-child relationships):
SELECT *
FROM emp
WHERE NOT EXISTS (SELECT 0
FROM dept
WHERE dept.dname = 'SALES' AND dept.deptno = emp.deptno)
AND NOT EXISTS (SELECT 0
FROM bonus
WHERE bonus.ename = emp.ename)
------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows |
------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 |
|* 1 | FILTER | | 1 | 8 |
| 2 | TABLE ACCESS FULL | EMP | 1 | 14 |
|* 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 3 | 1 |
|* 4 | INDEX UNIQUE SCAN | DEPT_PK | 3 | 3 |
|* 5 | TABLE ACCESS FULL | BONUS | 8 | 0 |
------------------------------------------------------------------
