Principle of nested loop join

These three table join methods are the most basic connection methods for Oracle:
Hash join Principle
Principle of sortmerge join

Nested loop join)

Number of visits: the number of times the driver table returns and the number of times it is accessed by the driver table.

Whether the driver table is sequential: Yes.

Sort or not: No.

Application scenarios: 1. A table in the association is small;

2. The associated fields of the joined table are indexed;

3. The index key value should not have a high repetition rate.

If you have developed it, you can think of it as a two-layer nested for loop.

Let's create an experiment:

SQL> create table test1 as select * from dba_objects where rownum <= 100;

SQL> create table test2 as select * from dba_objects where rownum <= 1000;
SQL> exec dbms_stats.gather_table_stats (user, 'test1 ');

SQL> exec dbms_stats.gather_table_stats (user, 'test2 ');

SQL> alter session set statistics_level = all;

SQL> select/* + leading (t1) use_nl (t2) */count (*)
2 from test1 t1, test2 t2
3 where t1.object _ id = t2.object _ id;
COUNT (*)
----------
100
SQL> select * from table (dbms_xplan.display_cursor (null, null, 'allstats last '));

PLAN_TABLE_OUTPUT
Bytes ---------------------------------------------------------------------------------------------------------
SQL _ID 3v5gu7ppdsz67, child number 0
-------------------------------------
Select/* + leading (t1) use_nl (t2) */count (*) from test1 t1, test2 t2 where
T1.object _ id = t2.object _ id

Plan hash value: 1459699139

---- Explanations:

Starts is the number of times the SQL statement is executed.

E-Rows is the expected number of Rows in the execution plan.

A-Rows indicates the number of Rows actually returned. When comparing A-Rows with E-Rows, you can determine which step of the execution plan has A problem.
A-Time indicates the actual execution Time of each step (HH: MM: SS. FF). You can know the Time consumed by the SQL statement based on this line.
Buffers performs logical read or consistent read for each step.
Reads is a physical read.
OMem and 1Mem are the memory evaluation values required for execution, 0Mem is the evaluation value for the memory required for the optimal execution mode, and 1Mem is the evaluation value for the memory required for the one-pass mode.
0/1/M is the optimal number of times/one-pass/multipass is executed.
Used-Mem memory consumption

 

Bytes ---------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Time | Buffers |
Bytes ---------------------------------------------------------------------------------------
| 1 | sort aggregate | 1 | 1 | 1 | 00:00:00. 01 | 1504 |
| 2 | nested loops | 1 | 100 | 100 | 00:00:00. 01 | 1504 |
| 3 | table access full | TEST1 | 1 | 100 | 100 | 00:00:00. 01 | 4 |
| * 4 | table access full | TEST2 | 100 | 1 | 100 | 00:00:00. 01 | 1500 |

Bytes ---------------------------------------------------------------------------------------

Predicate Information (identified by operation id ):
---------------------------------------------------
4-filter ("T1". "OBJECT_ID" = "T2". "OBJECT_ID ")


SQL> select/* + leading (t1) use_nl (t2) */count (*)
2 from test1 t1, test2 t2
3 where t1.object _ id = t2.object _ id
4 and t1.object _ id in (10, 11, 12 );
COUNT (*)
----------
3
SQL> select * from table (dbms_xplan.display_cursor (null, null, 'allstats last '));

PLAN_TABLE_OUTPUT
Bytes ---------------------------------------------------------------------------------------------------------
SQL _ID 0skx6hyjtsncu, child number 0
-------------------------------------
Select/* + leading (t1) use_nl (t2) */count (*) from test1 t1, test2 t2 where
T1.object _ id = t2.object _ id and t1.object _ id in (10, 11, 12)
Bytes ---------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Time | Buffers |
Bytes ---------------------------------------------------------------------------------------
| 1 | sort aggregate | 1 | 1 | 1 | 00:00:00. 01 | 49 |
| 2 | nested loops | 1 | 3 | 3 | 00:00:00. 01 | 49 |
| * 3 | table access full | TEST1 | 1 | 3 | 3 | 00:00:00. 01 | 4 |
| * 4 | TABLE accesskey FULL | TEST2 | 3 | 1 | 3 | 00:00:00. 01 | 45 |
Bytes ---------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
3-filter ("T1". "OBJECT_ID" = 10 OR "T1". "OBJECT_ID" = 11 OR
"T1". "OBJECT_ID" = 12 ))
4-filter (INTERNAL_FUNCTION ("T2". "OBJECT_ID") AND
"T1". "OBJECT_ID" = "T2". "OBJECT_ID "))

SQL> select/* + leading (t1) use_nl (t2) */count (*)
2 from test1 t1, test2 t2
3 where t1.object _ id = t2.object _ id
4 and t1.object _ id = 10;
COUNT (*)
----------
1

For more details, please continue to read the highlights on the next page:

1 2 Next Page