Database Reference
In-Depth Information
Figure 14-6.
Overview of the processing performed by a nested loops join
Nested loops joins have the following specific characteristics:
•
The left input (outer loop) is executed only once. The right input (inner loop) is potentially
executed many times.
•
They're able to return the first row of the result set before completely processing all rows.
•
They can take advantage of indexes to apply both restrictions and join conditions.
•
They support all types of joins.
Two-Table Join
The following is a simple execution plan processing a nested loops join between two tables. The example also shows
how to force a nested loops join by using the
leading
and
use_nl
hints. The former indicates the order in which
the tables are accessed. In other words, it specifies which table is accessed in the outer loop (
t1
) and which table is
accessed in the inner loop (
t2
). The latter specifies which join method is used to join the data returned by the inner
loop (that is, table
t2
) to table
t1
. It's essential to note that the
use_nl
hint contains no reference to table
t1
:
SELECT /*+ leading(t1 t2) use_nl(t2) full(t1) full(t2) */ *
FROM t1, t2
WHERE t1.id = t2.t1_id
AND t1.n = 19
-----------------------------------
| Id | Operation | Name |
-----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
|* 2 | TABLE ACCESS FULL| T1 |
|* 3 | TABLE ACCESS FULL| T2 |
-----------------------------------
2 - filter("T1"."N"=19)
3 - filter("T1"."ID"="T2"."T1_ID")
