Oracle Table Connection-nested loop nested loop join

I. Nested loop principle

Nested loop connection (loop nested connection) refers to a two-table connection, which is followed by a two-tier nested loop to match sequentially, and finally gets the table join method that returns the result set.

If the table T1 and T2 in the following SQL statement are connected in a circular nested connection, T1 is the driver table

Select *from T1, T2where t1.id = t2.id and t1.name = ' David ';

Then translating the above SQL statement into pseudo code should look like this:.

For each row in (SELECT * from T1 where name = ' David ') loopfor (SELECT * from T2 where t2.id = outer.id) loopif match the N Pass the row on to the next stepif no match then discard the rowend loopend loop

Specifically, if the SQL statement above performs a looping nested connection, the actual execution should look like this:
(1) First, Oracle will determine which table is the driver table and which table is the driver table (assuming T1 is the driver table) based on certain rules (costing or hint enforcement based on statistical information).
(2) Query driver table "SELECT * from T1 where name = ' David '" then get drive result set Q1
(3) Traverse the drive result set Q1 and the driver table T2, take a record from the drive result set Q1, then traverse T2 and follow the connection condition t2.id = t1.id to determine if there is a matching record in the T2, if the match is preserved, the match is ignored, and then the Q Take the next record in 1, and then loop through the T2 to match it until all the records in the Q1 have been retrieved.

two. Nested loop features

Nested loops join has the following characteristics:

(1) Usually the driver table in SQL statements is accessed only once and is accessed multiple times by the driver table
(2) You can return some data that has already been processed before you do not have to wait for all lines to be processed
(3) Indexing on constraints and connection conditions can improve execution efficiency
(4) supports all types of connections (equivalent connections, non-equivalent connections, like, etc.)

Tectonic test Data

sql> CREATE TABLE T1 (2 ID number not NULL, 3 n number, 4 pad VARCHAR2 (4000), 5 CONSTRAINT t1_pk PRIMA RY KEY (ID) 6); Table created.   sql> CREATE TABLE T2 (2 ID number not NULL, 3 t1_id number is not NULL, 4 n number, 5 pad VARCHAR2 (4000), 6 CONSTRAINT T2_PK PRIMARY key (ID), 7 CONSTRAINT t2_t1_fk FOREIGN key (t1_id) REFERENCES T1 8); Table created.   sql> CREATE TABLE t3 (2 ID number not NULL, 3 t2_id number is not NULL, 4 n number, 5 pad VARCHAR2 (4000), 6 CONSTRAINT T3_PK PRIMARY key (ID), 7 CONSTRAINT t3_t2_fk FOREIGN key (t2_id) REFERENCES T2 8); Table created.   sql> CREATE TABLE t4 (2 ID number not NULL, 3 t3_id number is not NULL, 4 n number, 5 pad VARCHAR2 (4000), 6 CONSTRAINT T4_PK PRIMARY key (ID), 7 CONSTRAINT t4_t3_fk FOREIGN key (t3_id) REFERENCES T3 8); Table created. Sql> Execute dbms_random.seed (0) PL/SQL procedure successfully completed. sql> INSERT into T1 SELECT rownum, RownuM, dbms_random.string (' A ', ') from dual CONNECT by level <= ORDER by dbms_random.random;10 rows created.  Sql> INSERT into T2 SELECT 100+rownum, t1.id, 100+rownum, t1.pad from T1, T1 dummy ORDER by dbms_random.random;100 rows Created. sql> INSERT into T3 SELECT 1000+rownum, T2.id, 1000+rownum, t2.pad from T2, T1 dummy ORDER by dbms_random.random;1000 R oWS created. Sql> INSERT into T4 SELECT 10000+rownum, T3.id, 10000+rownum, t3.pad from T3, T1 dummy ORDER by dbms_random.random;1000 0 rows created. Sql> COMMIT; Commit complete.

use hint to let SQL statements connect through nested loop, and specify T3 as the driver table

Sql> Select/*+ Leading (T3) use_nl (T4) * * from T3, T4 2 where t3.id = t4.t3_id and T3.N = 1100;10 rows Selected.sql > select * FROM Table (Dbms_xplan.display_cursor (Null,null, ' allstats last ')); Plan_table_ OUTPUT------------------------------------------------------------------------------------------------------------------- -----------------------------------------------------------------------sql_id 89HNFWQAKJGHG, child number 0-------  ------------------------------Select/*+ Leading (T3) use_nl (T4) * * from T3, T4 where t3.id =t4.t3_id and T3.N = 1100Plan Hash value:1907878852-------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-rows |   A-rows | A-time |   Buffers |-------------------------------------------------------------------------------------| 0 |      SELECT STATEMENT |      |        1 |     |     10 |00:00:00.01 |   121 | |  1 |      NESTED LOOPS |      |     1 |     10 | 10 |00:00: 00.01 | 121 | |   * 2 | TABLE ACCESS full|      T3 |      1 |      1 |      1 |00:00:00.01 | 16 | |   * 3 | TABLE ACCESS full|      T4 |     1 |     10 |     10 |00:00:00.01 | -------------------------------------------------------------------------------------Predicate information ( identified by Operation ID):---------------------------------------------------2-filter ("T3". " N "=1100) 3-filter (" T3 "." ID "=" T4 "." t3_id ")

in the execution plan we can see the driver table T3 Access once, because the driver table has the predicate condition T3.N = 1100, the number of records driving the result set after the predicate condition is executed is 1, so T4 is also only visited once (starts column)

use hint to let SQL statements connect through nested loop, and specify T4 as the driver table

Sql> Select/*+ leading (T4) use_nl (T3) Full (T4) (T3) */* from T3, T4 2 where t3.id = t4.t3_id and T3.N = 1100; Sql> select * FROM table (Dbms_xplan.display_cursor (Null,null, ' allstats last ')); Plan_table_ OUTPUT-------------------------------------------------------------------------------------------------------------------  -------------------------------------------------------------------------------------------------sql_id 0YXM1MUQWRFQ2, child number 0-------------------------------------select/*+ leading (T4) use_nl (T3) full (T4) full (T3) * /* from T3, t4where t3.id = t4.t3_id and T3.N = 1100Plan hash value:3886808168------------------------------------------ -------------------------------------------| Id | Operation | Name | Starts | E-rows |   A-rows | A-time |   Buffers |-------------------------------------------------------------------------------------| 0 |      SELECT STATEMENT |      |        1 |     |     10 |00:00:00.25 |   150k| |  1 | NESTED LOOPS     |      |     1 |     10 |     10 |00:00:00.25 |   150k| |   2 | TABLE ACCESS full|      T4 |  1 |  10000 |     10000 |00:00:00.01 | 105 | |   * 3 | TABLE ACCESS full|  T3 |      10000 |     1 |     10 |00:00:00.21 | 150k|-------------------------------------------------------------------------------------Predicate Information (identified by Operation ID):---------------------------------------------------3-filter (("T3". ") N "=1100 and" T3 "." ID "=" T4 "." t3_id "))

in the execution plan we can see the driver table T4 Access once, because the driver table on the T4 result set of the number of records is 10000, so T4 access 10,000 times, buffers and a-time (actual execution time) are relatively high.

three. Nested loop optimization

nested loop is indexed on the join column on the driver table (the t3_id column of the T4 table)

Sql> CREATE index t4_t3_id on t4 (t3_id); index created. Sql> Select/*+ Leading (T3) use_nl (T4) * * from T3, T4 2 where t3.id = t4.t3_id and T3.N = 1100;10 rows Selected.sql > select * FROM Table (Dbms_xplan.display_cursor (Null,null, ' allstats last ')); Plan_table_ OUTPUT------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------------------------------sql_id 89HNFWQAKJGHG, child number 0------------------------------------- Select/*+ Leading (T3) use_nl (T4) */* from T3, T4 where t3.id =t4.t3_id and T3.N = 1100Plan hash value:2039660043------- -----------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-rows |   A-rows | A-time | Buffers | Reads |------------------------------------------------------------------------------------------------------------| 0 |          SELECT STATEMENT |      |        1 |     |      10 |00:00:00.01 |   29 |   1 | |  1 |          NESTED LOOPS |      |        1 |     |      10 |00:00:00.01 |   29 |   1 | |   2 |          NESTED LOOPS |      |     1 |     10 |      10 |00:00:00.01 |   19 | 1 | |    * 3 | TABLE ACCESS Full |      T3 |      1 |      1 |      1 |00:00:00.01 |   16 | 0 | |    * 4 | INDEX RANGE SCAN |      t4_t3_id |     1 |     10 |       10 |00:00:00.01 |   3 |   1 | |   5 | TABLE ACCESS by INDEX rowid|     T4 |     10 |     10 |      10 |00:00:00.01 |   10 | 0 |------------------------------------------------------------------------------------------------------------ predicate information (identified by Operation ID):---------------------------------------------------3-filter ("T3" ." N "=1100) 4-access (" T3 "." ID "=" T4 "." t3_id ")

In the execution plan, you can see that the buffer is dropped from 121 to the index on the connection column on the drive table .

on the predicate condition column of the driver table (n columns of the T3 table), add the index

Sql> CREATE index t3_n on t3 (n); index created. Sql> Select/*+ Leading (T3) use_nl (T4) * * from T3, T4 2 where t3.id = t4.t3_id and T3.N = 1100;10 rows Selected.sql > select * FROM Table (Dbms_xplan.display_cursor (Null,null, ' allstats last ')); Plan_table_ OUTPUT------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ----------------------------------------sql_id 89HNFWQAKJGHG, child number 0------------------------------------- Select/*+ Leading (T3) use_nl (T4) */* from T3, T4 where t3.id =t4.t3_id and T3.N = 1100Plan hash value:2304842513------- ------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-rows |   A-rows | A-time | Buffers | Reads |-------------------------------------------------------------------------------------------------------------| 0 |          SELECT STATEMENT |      |        1 |     |      10 |00:00:00.01 |   17 |   1 | |  1 |          NESTED LOOPS |      |        1 |     |      10 |00:00:00.01 |   17 |   1 | |   2 |          NESTED LOOPS |      |     1 |     10 |       10 |00:00:00.01 |   7 |   1 | |    3 | TABLE ACCESS by INDEX rowid|      T3 |      1 |      1 |       1 |00:00:00.01 |   4 | 1 | |     * 4 | INDEX RANGE SCAN |      T3_n |      1 |      1 |       1 |00:00:00.01 |   3 | 1 | |    * 5 | INDEX RANGE SCAN |      t4_t3_id |     1 |     10 |       10 |00:00:00.01 |   3 |   0 | |   6 | TABLE ACCESS by INDEX ROWID |     T4 |     10 |     10 |      10 |00:00:00.01 |   10 | 0 |------------------------------------------------------------------------------------------------------------- predicate information (identified by Operation ID):---------------------------------------------------4-access ("T3". N "=1100) 5-access (" T3 "." ID "=" T4 "." t3_id ")

In the execution plan, you can see that after adding an index to the predicate condition column on the driver table, buffer continues to fall from 29 to

Four. Summary

This shows that when SQL tuning is encountered, the connection to the table is nested loop:

First, make sure that the table with the smaller result set is the driver table, and the table with the result set is the driven table. This does not mean that a table with multiple records cannot be used as a driver table, as long as the result set filtered by the predicate condition is small and can also be used as a driver table.

Second, An index on the predicate condition column of the driver table and on the connection column of the driven table can significantly improve execution performance .

Finally, if the columns to be queried are in the index, the execution performance is further improved when the table query column information is not returned.

Oracle Table Connection-nested loop nested loop join