Database Reference
In-Depth Information
Even if the operations of this type share the same characteristics, each of them has, to some extent, its own
behavior. Let's take a look at an example for each of them (except for
BITMAP KEY ITERATION
, which is covered in
Chapter 14). Note that all examples provided in the following subsections are excerpts of the output generated by the
related-combine.sql
script.
The NESTED LOOPS Operation
This operation is used to join two sets of rows. Consequently, it always has two children, no more, no less. The child
with the smallest ID is called the
outer loop
or
driving row source
. The second child is called the
inner loop
. The
particular characteristic of this operation is that the inner loop is executed once for each row returned by the outer
loop (Chapter 14 explains in detail how nested loops joins are executed).
The following query and its execution plan are an example (Figure
10-5
shows a graphical representation of its
parent-child relationships):
SELECT *
FROM emp, dept
WHERE emp.deptno = dept.deptno
AND emp.comm IS NULL
AND dept.dname != 'SALES'
------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows |
------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 |
| 1 | NESTED LOOPS | | 1 | 8 |
|* 2 | TABLE ACCESS FULL | EMP | 1 | 10 |
|* 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 10 | 8 |
|* 4 | INDEX UNIQUE SCAN | DEPT_PK | 10 | 10 |
------------------------------------------------------------------
2 - filter("EMP"."COMM" IS NULL)
3 - filter("DEPT"."DNAME"<>'SALES')
4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
Figure 10-5.
Parent-child relationships of the
NESTED LOOPS
operation
In this execution plan, both children of the
NESTED LOOPS
operation are stand-alone operations. By applying the
rules described earlier, you can see that the execution plan carries out the operations as follows:
1.
Operation 0 has one child (1). It can't be the first one being executed.
