Optimization of execution plans of oracle nested loops

The two datasets processed by nested loop connection are called the External loop (that is, the driving data source, driving row soulce) and the internal loop (inner loop ). The External Loop is the left subnode, and the internal loop is the right subnode. 10 to 6. When the External Loop is executed once, the internal loop needs to be executed once for each record returned by the External Loop.
 
Nested loop connections have the following features: The left subnode (External Loop) of the mouth is executed only once, while the right subnode (internal loop) is generally executed many times.
The first record of the result set can be returned before all data is processed.
The interface can effectively use indexes to process restrictions and connection conditions.
Ports support all types of connections.
Www.2cto.com is a simple execution plan for two-Table nested loop join. It also describes how to use the prompt 1 eading and use_n1 to force nested loop connections. The Leading prompt requires that you first access the table tl, that is, which table is used as an external cyclic table. The use_nl prompt specifies the connection method used to connect the data returned by an internal loop (Table t2) to table t1. Note that the usenl prompt does not reference table t1.
SELECT/* + leading (t1) use_nl (t2) full (t1) full (t2 )*/*
FROM t1, t2 WHERE tl. id = t2.id AND tl. n = 19
 
The nested loops operation is a related union operation. It means that the execution of the second subnode (internal loop) is controlled by the first subnode (External Loop. In this example, the execution process of the execution plan is summarized as follows.
Port scans the entire table to access all records of table t1, and filters data ports by applying the restriction condition n = 19 during access.
The number of records returned in the previous step of the interface, and the number of full table scans performed on table t2.

Undoubtedly, when operation 2 returns more than one record, this execution plan is not very effective, so the query optimizer almost never chooses it. For this reason, you must specify two access prompts (full) to force the query optimizer to use this execution plan. On the other hand, if an External Loop returns only one record, and the internal cycle is highly selective, it may be good to perform a full table scan on table t2. To demonstrate this, we will create a unique index in the n field of table t1:
Www.2cto.com
Create unique index t1_n ON t1 (n)
With this index, the preceding statement performs the following operations. Because of index unique scan, the internal cycle can be executed only once.
SELECT/* + leading (t1) use_nl (t2) index (t1) full (t2 )*/*
FROM t1, t2 WHERE tl. id = t2.id AND tl. n = 19
 
If the selectivity of internal loops is low, it is a good choice to adapt to index scanning for internal loops. Nested loop connections are associated with associated operations. For internal loops, this location may also use the connection conditions. For example, in the following execution plan, operation 5 performs index search using the return value t1.id of Operation 3.
SELECT/* + leading (t1) use_nl (t2) index (t1) index (t2 )*/*
FROM t1, t2 WHERE tl. id = t2.id AND tl. n = 19
In this way, the best performance is achieved by establishing two indexes. Nested loops are suitable for the selection of two low-selective and efficient data volumes. When the data volume is large, the optimizer selects the hash join connection by default, unless we manually select the prompt, in addition, indexes are used to achieve high efficiency. In general, if the internal loop is executed multiple times, it is meaningful only for the selective access path and the path that causes a small number of logical reads.
Www.2cto.com Table 4 connection
The following execution plan is a typical left-depth tree implemented by nested loop connections. Note that the ordered prompt shows how to access each table through the index requires these tables to be accessed in the order they are in the FROM clause. When use_nl prompts that the following table be connected to the first table (or the result set of the previous operation), nested loop connections are used.

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
 
The execution process of this execution plan can be summarized as follows (the following description does not consider the use of row prefetch ):
(L) when reading the first record (that is, not when this statement is parsed or executed), the constraint tl on table t1 is applied. when n = 19 and the first record is obtained, the processing starts.

(2) Table t2 is searched based on the results found in Table t1. The database engine uses the connection condition tl. id = t2.t1 _ id to access table t2. In fact, there are no restrictions on table t2. Only the first record that meets the connection condition will be returned to the upper-level operation.
(3) Table t3 searches for database engines based on the results found in Table t2. It also uses the connection condition t2.id = t3.t2 _ id to access table t3. Only the first record that meets the connection condition will be returned to the upper-level operation.
(4) Table t4 searches based on the result found in Table t3. Similarly, the database engine uses the connection condition t3.id = t4.t3 _ id to access table t4. The first record that meets the conditions is immediately returned to the client.

(5) Subsequent operations follow the first recorded behavior. Obviously, the execution process starts at the location where a single match (or the second record matching in Table t4, if any) is performed. Note that records meeting the conditions will be returned to the client immediately. In other words, there is no need to complete the execution process before returning the first record.

Block prefetch
In general, when the cache does not hit, the access path based on a single block processing (such as rowid access and index range scanning) will lead to a single block of physical reads. For nested loop connections, especially when many rows of data need to be processed, the efficiency will be relatively poor. Actually, in many cases, nested loop connections also use multiple physical reads to access multiple adjacent blocks. The database engine can use the block prefetch function to improve the efficiency of nested loop connections. The objective of this optimization technique is to use multiple physical reads at a time for multiple adjacent blocks to replace multiple physical reads at a single block. Block prefetch is applicable to tables and indexes. You cannot view the execution plan to see whether the database engine uses the block prefetch function. The only way to see it is to view the physical reads executed by the server process, especially the wait events related to physical reads.
The db file sequential read is an event related to a single physical read. Therefore, if this event occurs, it means that the block prefetch is either not used or cannot be used (for example, because the requested block is already in the cache ).
Www.2cto.com
Port db file scattered read is an event related to multiple physical reads. Therefore, if ~ If you see this wait event in id access or index range scan, the block prefetch function is used.
Note that we cannot control the use of the block prefetch function. The database engine determines how to use the block prefetch function.
Other optional execution plans
You can use the following execution plan to execute nested loop connections.
 
In fact, in recent Oracle versions, this type of execution plan is used only when the internal or external loop is based on the unique index scan. Next let's take a look at what will happen if the index t1_n on column n is defined (not unique) as follows:
Www.2cto.com
Create index t1_n ON t1 (n)
When this index is changed, the following execution plan will be used. Note that rowid access on table t2 is in different locations. in the previous Execution Plan, it is in operation 4, but in the next execution plan, it is in operation 1. Specifically, the sub-operation of row record access (Operation l) is a nested loop connection (Operation 2 ). From our perspective, the two execution plans have done the same thing. This execution plan may be used to take advantage of some internal optimizations (such as block prefetch ).
 
In oracle 11g, the following execution plan may be used, instead of the previous one. Note that although the query is always a two-Table connection, the execution plan contains two nested loop connections! A simple performance test shows that using it can increase the performance by about 10 percentage points. This may be because a new internal optimization can only take effect in the new execution plan.