Database Reference
In-Depth Information
-------------------------------------------------
| Id | Operation | Name |
-------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 |
|* 3 | INDEX UNIQUE SCAN | T1_N |
| 4 | TABLE ACCESS BY INDEX ROWID| T2 |
|* 5 | INDEX RANGE SCAN | T2_T1_ID |
-------------------------------------------------
3 - access("T1"."N"=19)
5 - access("T1"."ID"="T2"."T1_ID")
In summary, if the inner loop is executed several (or many) times, only access paths that are sensible in case of
strong selectivity and that lead to very few logical reads make sense.
Four-Table Join
The following execution plan is an example of a typical left-deep tree, implemented with nested loops joins (refer
to Figure
14-2
for a graphical representation). Notice how each table is accessed through indexes. The example also
shows how to force nested loops joins by using the
ordered
and
use_nl
hints. The former specifies to access the tables
in the same order as they appear in the
FROM
clause. The latter specifies which join method is used to join the tables
referenced by the hint to the first table or to the result sets of the previous join operations:
SELECT /*+ ordered use_nl(t2 t3 t4) */ t1.*, t2.*, t3.*, t4.*
FROM t1, t2, t3, t4
WHERE t1.id = t2.t1_id
AND t2.id = t3.t2_id
AND t3.id = t4.t3_id
AND t1.n = 19
---------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | NESTED LOOPS | |
| 3 | NESTED LOOPS | |
| 4 | TABLE ACCESS BY INDEX ROWID| T1 |
|* 5 | INDEX RANGE SCAN | T1_N |
| 6 | TABLE ACCESS BY INDEX ROWID| T2 |
|* 7 | INDEX RANGE SCAN | T2_T1_ID |
| 8 | TABLE ACCESS BY INDEX ROWID | T3 |
|* 9 | INDEX RANGE SCAN | T3_T2_ID |
| 10 | TABLE ACCESS BY INDEX ROWID | T4 |
|* 11 | INDEX RANGE SCAN | T4_T3_ID |
---------------------------------------------------
