Optimizing Joins - Troubleshooting Oracle Performance - page 534

In practice, for the Oracle Database versions covered in this topic, this type of execution plan is used only when
either the outer loop or the inner loop is based on an index unique scan (here, the t1_n index is unique). Let's see
what happens if the t1_n index on column n is defined as follows (nonunique):
CREATE INDEX t1_n ON t1 (n)
With this index in place, the following execution plan is used. Notice the different position of the rowid access on
table t2 . In the previous plan, it's operation 4, whereas in the following, it's operation 1. It's peculiar that the child of
the rowid access (operation 1) is the nested loops join (operation 2). Even though from a functional point of view the
two execution plans are equivalent, the database engine uses execution plans with the following shape in order to take
advantage of table prefetching:
--------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID | T2 |
| 2 | NESTED LOOPS | |
| 3 | TABLE ACCESS BY INDEX ROWID| T1 |
|* 4 | INDEX RANGE SCAN | T1_N |
|* 5 | INDEX RANGE SCAN | T2_T1_ID |
--------------------------------------------------
4 - access("T1"."N"=19)
5 - access("T1"."ID"="T2"."T1_ID")
From version 11.1 onward, you can control the usage of the previous execution plan through the nlj_prefetch
and no_nlj_prefetch hints.
From version 11.1 onward, for further optimizing nested loops joins, table prefetching is superseded by batching.
As a result, the following execution plan should be observed instead of one from the previous section:
--------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | NESTED LOOPS | |
| 3 | TABLE ACCESS BY INDEX ROWID| T1 |
|* 4 | INDEX RANGE SCAN | T1_N |
|* 5 | INDEX RANGE SCAN | T2_T1_ID |
| 6 | TABLE ACCESS BY INDEX ROWID | T2 |
--------------------------------------------------
4 - access("T1"."N"=19)
5 - access("T1"."ID"="T2"."T1_ID")
Note that even though the query is always the same (that is, a two-table join), the execution plan contains two
nested loops joins! To control batching, the nlj_batching and no_nlj_batching hints are available.