Database Reference
In-Depth Information
5 - access("T1"."N"=19)
7 - access("T1"."ID"="T2"."T1_ID")
9 - access("T2"."ID"="T3"."T2_ID")
11 - access("T3"."ID"="T4"."T3_ID")
The processing of this type of execution plan can be summarized as follows (this description assumes that no row
prefetching is used):
1.
When the first row is fetched (in other words, not when the query is parsed or executed), the
processing starts by getting the first row that fulfills the
t1.n = 19
restriction from table
t1
.
2.
Based on the data found in table
t1
, table
t2
is looked up. Note that the database engine
takes advantage of the
t1.id = t2.t1_id
join condition to access table
t2
. In fact, no
restriction is applied to that table. Only the first row that fulfills the join condition is
returned to the parent operation.
3.
Based on the data found in table
t2
, table
t3
is looked up. Also in this case, the database
engine takes advantage of a join condition,
t2.id = t3.t2_id
, to access table
t3
. Only the
first row that fulfills the join condition is returned to the parent operation.
4.
Based on the data found in table
t3
, table
t4
is looked up. Here, too, the database engine
takes advantage of a join condition,
t3.id = t4.t3_id
, to access table
t4
. The first row
that fulfills the join condition is immediately returned to the client.
5.
When the subsequent rows are fetched, the same actions are performed that were
performed for the first fetch. Obviously, the processing is restarted from the position
following the last match (that could be the second row that matches in table
t4
, if any).
It's essential to stress that data is returned as soon as a row that fulfills the request is found.
It isn't necessary to fully execute the join before returning the first row.
Buffer Cache Prefetches
Basically, each access path, except for full scans, leads to single-block physical reads in the event of a cache miss.
For nested loops joins, especially when many rows are processed, these single-block reads can be very inefficient.
In fact, it's not unusual for nested loops joins to access blocks with many single-block physical reads.
To improve the efficiency of nested loops joins, the database engine is able to take advantage of optimization
techniques that substitute single-block physical reads with multiblock physical reads. Three features use such an
approach:
table prefetching
,
batching
, and
buffer cache prewarm
. The first two are associated with the execution plans
presented in this section; the latter happens shortly after an instance bounce for any nested loops join. Note that these
optimization techniques take place while accessing both indexes and tables.
The “Two-Table Join” section shows an execution plan based on a nested loops join that has the following shape:
-------------------------------------------------
| 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 |
-------------------------------------------------
