嵌套循环连接(Nested Loops Join)是一种两个表在做表连接时依靠两层嵌套循环（分别为外层循环和内存循环）来得到连接结果集的表连接方法。即外层循环对应的驱动结果集有多少条记录，遍历被驱动表的内层循环就要做多少次，这就是所谓的“嵌套循环”的含义。
对于嵌套循环连接的优缺点及适用场景如下：
a,如果驱动表所对应的驱动结果集的记录数较少，同时在被驱动表的连接列上又存在唯一性索引（或者在被驱动表的连接列上存在选择性好的非唯一性索引），那么此时使用嵌套循环连接的执行效率就会非常高；但如果驱动表所对应的驱动结果集的记录数很多，即便在被驱动表的连接列上存在索引，此时使用嵌套循环连接的执行效率也不会很高。
b,大表也可以作为嵌套循环连接的驱动表，关键是看目标SQL中指定的谓词条件(如果有的话)能否将驱动结果集的记录集数量大幅度的降下来。
c,嵌套循环连接有其他连接方法所没有的一个优点：嵌套循环连接可以实现快速响应。因为排序合并连接需要等到排序完后做合并操作时才能开始返回数据，而哈希连接则也等到驱动结果集所对应的HASH TABLE全部构建完后才能开始返回数据。
oracle表之间的连接之嵌套循环连接(Nested Loops Join),其特点如下：
1，驱动表返回几天记录，被驱动表就被访问多少次。
2，嵌套循环表连接的表有驱动顺序。
3，嵌套循环表连接的表无需要排序。
4，嵌套循环表连接的表没有任何限制场景，即任何SQL语句都可以用嵌套循环表连接的表都可以用嵌套循环连接进行操作数据库。
5，其SQL语句的优化原则是：驱动表的限制条件的字段上需要有索引，被驱动表的连接条件的字段上需要有索引。
下面我来做个实验来证实如上的结论：
<-----------------------下面是实验的基础数据------------------------------------------------------->
drop table T1 cascade constraints purge;
CREATE TABLE T1(id number not null,num number,information VARCHAR2(4000));
drop table T2 cascade constraints purge;
CREATE TABLE T2(id number not null,T1_ID number not null, num number,information VARCHAR2(4000));
SQL> execute dbms_random.seed(0);
PL/SQL procedure successfully completed
SQL> insert into T1 select rownum,rownum, dbms_random.string('X',100) from dual
2 connect by level<=100 order by dbms_random.random;
100 rows inserted
SQL>
SQL> insert into T2 select rownum,rownum,rownum, dbms_random.string('Y',100) from dual
2 connect by level<=100000 order by dbms_random.random;
100000 rows inserted
SQL> COMMIT;
Commit complete
SQL> select count(*) from T1;
COUNT(*)
----------
100
SQL> select count(*) from T2;
COUNT(*)
----------
100000
<-----------------------上面是实验的基础数据----end --------------------------------------------------->
1, 驱动和被驱动表的访问次数
下面测试表的访问次数：
Nested Loops Join，T2表被访问100次
SQL> set linesize 1000;
SQL> alter session set statistics_level=all;
Session altered
SQL> select /*+ leading(T1) use_nl(T2)*/ * from T1,T2 where T1.ID=T2.T1_ID;
--此处省略记录结果
SQL> select sql_id, child_number, sql_text from v$sql where sql_text like '%leading(t1)%';
SQL_ID CHILD_NUMBER SQL_TEXT
------------- ------------ --------------------------------------------------------------------------------
901dhc61y4u01 0 select sql_id, child_number, sql_text from v$sql where sql_text like '%leading(
901dhc61y4u01 1 select sql_id, child_number, sql_text from v$sql where sql_text like '%leading(
ggu0wqwqzpw8d 0 explain plan for select /*+leading(t1) use_nl(t2)*/ * from t1,t2 where t1.id=t2
8v44uh08hk303 0 select /*+leading(t1) use_nl(t2)*/ * from t1,t2 where t1.id=t2.t1_id
8v44uh08hk303 1 select /*+leading(t1) use_nl(t2)*/ * from t1,t2 where t1.id=t2.t1_id
SQL> select * from table(dbms_xplan.display_cursor('8v44uh08hk303',1,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 8v44uh08hk303, child number 1
-------------------------------------
select /*+leading(t1) use_nl(t2)*/ * from t1,t2 where t1.id=t2.t1_id
Plan hash value: 1967407726
--------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buff
--------------------------------------------------------------------------------
| 1 | NESTED LOOPS | | 1 | 100 | 100 |00:00:00.60 |
| 2 | TABLE ACCESS FULL | T1 | 1 | 100 | 100 |00:00:00.01 |
|* 3 | TABLE ACCESS FULL| T2 | 100 | 1 | 100 |00:00:00.60 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."ID"="T2"."T1_ID")
Note
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----
- dynamic sampling used for this statement
23 rows selected
Note:E-ROWS表示优化器评估的行数(Evaluation Rows)，A-ROWS表示实际的行数(Aactual Rows)。
从上面的执行计划可以看出，T1表被执行了一次(Starts这一列表示表被访问的次数)，T2表被访问了100次！
Nested Loops Join，T2表被访问2次
SQL> select /*+leading(t1) use_nl(t2)*/ * from t1,t2 where t1.id=t2.t1_id and t1.num in(20,30);
ID NUM INFORMATION ID T1_ID NUM INFORMATION
---------- ---------- -------------------------------------------------------------------------------- ---------- ---------- ---------- --------------------------------------------------------------------------------
20 20 TDX8UJ2WUQIUUWSN9BZ3HEAKWFHENQC57VZ6PZU3L6RZ4120DO48OQ1QSEMH9E22MH0KVMQUHR2LGDLA 20 20 20 TIUTKBCQDOTYVDYYPBTPGTMPSIDWJPGTSXJUBQOWFWGMZEBQRXABOXOLQYPURIJVMCTWTNUUYZCFXOFK
30 30 0IU7YCLXJQ93Q3B6FPTS07W1T53OFF0YZH9FVYFG67WCZIIS6GEH65ITOXWDRLVJ7IJM1QMLXP40PETZ 30 30 30 JZNXYHPTRYYIDXUAGKPUCSBXIDOFSYTGUIPJRYPGFXZDHMSTPSXWFUPRCCCQFIZMGNRUVJGMHPXKEUQY
SQL> select sql_id, child_number, sql_text from v$sql where sql_text like '%t1.num in(20,30)%';
SQL_ID CHILD_NUMBER SQL_TEXT
------------- ------------ --------------------------------------------------------------------------------
btydr0p4zft1m 0 select sql_id, child_number, sql_text from v$sql where sql_text like '%t1.num i
atcnxaa1ffvjd 0 select /*+leading(t1) use_nl(t2)*/ * from t1,t2 where t1.id=t2.t1_id and t1.num
SQL> select * from table(dbms_xplan.display_cursor('atcnxaa1ffvjd',0,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID atcnxaa1ffvjd, child number 0
-------------------------------------
select /*+leading(t1) use_nl(t2)*/ * from t1,t2 where t1.id=t2.t1_id and
t1.num in(20,30)
Plan hash value: 1967407726
--------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buff
--------------------------------------------------------------------------------
| 1 | NESTED LOOPS | | 1 | 2 | 2 |00:00:00.01 | 3
|* 2 | TABLE ACCESS FULL| T1 | 1 | 2 | 2 |00:00:00.01 |
|* 3 | TABLE ACCESS FULL| T2 | 2 | 1 | 2 |00:00:00.01 | 3
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("T1"."NUM"=20 OR "T1"."NUM"=30))
3 - filter("T1"."ID"="T2"."T1_ID")
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
25 rows selected
从上面的执行计划可以看出，T1表被执行了一次(Starts这一列表示表被访问的次数)，T2表被访问了2次！
Nested Loops Join，T2表被访问1次
SQL> select /*+leading(t1) use_nl(t2)*/ * from t1,t2 where t1.id=t2.t1_id and t1.num=20;
ID NUM INFORMATION ID T1_ID NUM INFORMATION
---------- ---------- -------------------------------------------------------------------------------- ---------- ---------- ---------- --------------------------------------------------------------------------------
20 20 TDX8UJ2WUQIUUWSN9BZ3HEAKWFHENQC57VZ6PZU3L6RZ4120DO48OQ1QSEMH9E22MH0KVMQUHR2LGDLA 20 20 20 TIUTKBCQDOTYVDYYPBTPGTMPSIDWJPGTSXJUBQOWFWGMZEBQRXABOXOLQYPURIJVMCTWTNUUYZCFXOFK
SQL> select sql_id, child_number, sql_text from v$sql where sql_text like '%t1.num=20%';
SQL_ID CHILD_NUMBER SQL_TEXT
------------- ------------ --------------------------------------------------------------------------------
471w5yr5rack1 0 select sql_id, child_number, sql_text from v$sql where sql_text like '%t1.num=2
5jdf02xk6rj0x 0 select /*+leading(t1) use_nl(t2)*/ * from t1,t2 where t1.id=t2.t1_id and t1.num
SQL> select * from table(dbms_xplan.display_cursor('5jdf02xk6rj0x',0,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 5jdf02xk6rj0x, child number 0
-------------------------------------
select /*+leading(t1) use_nl(t2)*/ * from t1,t2 where t1.id=t2.t1_id and
t1.num=20
Plan hash value: 1967407726
--------------------------------------------------------------------------------
| Id | Operation | Name | Starts| E-Rows | A-Rows | A-Time | Buff
--------------------------------------------------------------------------------
| 1 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 1
|* 2 | TABLE ACCESS FULL| T1 | 1 | 1 | 1 |00:00:00.01 |
|* 3 | TABLE ACCESS FULL| T2 | 1 | 1 | 1 |00:00:00.01 | 1
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T1"."NUM"=20)
3 - filter("T1"."ID"="T2"."T1_ID")
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
25 rows selected
从上面的执行计划可以看出，T1表被执行了一次(Starts这一列表示表被访问的次数)，T2表被访问了1次！
Nested Loops Join，T2表被访问0次
SQL> select /*+leading(t1) use_nl(t2)*/ * from t1,t2 where t1.id=t2.t1_id and t1.num=888888888;
ID NUM INFORMATION ID T1_ID NUM INFORMATION
---------- ---------- -------------------------------------------------------------------------------- ---------- ---------- ---------- --------------------------------------------------------------------------------
SQL> select sql_id, child_number, sql_text from v$sql where sql_text like '%t1.num=8888888%';
SQL_ID CHILD_NUMBER SQL_TEXT
------------- ------------ --------------------------------------------------------------------------------
6z2jrdf4snd59 0 select /*+leading(t1) use_nl(t2)*/ * from t1,t2 where t1.id=t2.t1_id and t1.num
1h86xkxg3psb6 0 select sql_id, child_number, sql_text from v$sql where sql_text like '%t1.num=8
SQL> select * from table(dbms_xplan.display_cursor('6z2jrdf4snd59',0,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 6z2jrdf4snd59, child number 0
-------------------------------------
select /*+leading(t1) use_nl(t2)*/ * from t1,t2 where t1.id=t2.t1_id and
t1.num=888888888
Plan hash value: 1967407726
--------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buff
--------------------------------------------------------------------------------
| 1 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 1 | 0 |00:00:00.01 |
|* 3 | TABLE ACCESS FULL| T2 | 0 | 1 | 0 |00:00:00.01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T1"."NUM"=888888888)
3 - filter("T1"."ID"="T2"."T1_ID")
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
25 rows selected
从上面的执行计划可以看出，T1表被执行了一次(Starts这一列表示表被访问的次数)，T2表被访问了0次！
通过上面的实验可以得出如下的结论：
T1表的查询返回多少条记录，T2 表就被访问多少次。第一次T1表返回100条记录，是因为T1表全表就是100条记录，无条件查询T1表，所以T1表的100条记录都返回了；而第二次的限制条件and t1.num in (20,30)的条件让T1只返回2条记录，所以T2被访问2次；第三次t1.num=20的条件让T1表只返回1条记录，所以T2只查询一次；最后一次and t1.num=88888888这个条件从T1中找不到记录，所以T2表就干脆不访问了。
下面来证明下上面T1表的返回记录数：
说明T2表为什么被访问100次。
SQL> select count(*) from t1;
COUNT(*)
----------
100
说明T2表为什么被访问2次。
SQL> select count(*) from t1 where t1.num in (20,30);
COUNT(*)
----------
2
说明T2表为什么被访问1次。
SQL> select count(*) from t1 where t1.num=20;
COUNT(*)
----------
1
说明T2表为什么被访问0次。
SQL> select count(*) from t1 where t1.num=888888888;
COUNT(*)
----------
0
上面我使用的/*+ leading(t1) use_nl(t2)*/这个HINT的含义，USE_NL表示强制ORACLE的优化器使用嵌套循环的链接方式，leading(t1)表示T1作为驱动表。
通过上面的实验可以这个结论：在嵌套循环连接中，驱动表返回多少条记录，被驱动表就被访问多少次！！！
2, 驱动和被驱动表访问顺序影响性能
下面是T1表先访问的执行计划：
SQL> select sql_id,child_number,sql_text from v$sql where sql_text like '%leading(t1)%'
2 ;
SQL_ID CHILD_NUMBER
------------- ------------
SQL_TEXT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
f8za409wps8u4 0
select sql_id,child_number,sql_text from v$sql where sql_text like '%leading(t1)%'
4w8m0xv027nz2 0
select /*+ leading(t1) use_nl(t2)*/ * from t1,t2 where t1.id=t2.t1_id and t1.num=20
下面是执行计划：
SQL> select * from table(dbms_xplan.display_cursor('4w8m0xv027nz2',0,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 4w8m0xv027nz2, child number 0
-------------------------------------
select /*+ leading(t1) use_nl(t2)*/ * from t1,t2 where t1.id=t2.t1_id and
t1.num=20
Plan hash value: 1967407726
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 1 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 3464 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 2 | TABLE ACCESS FULL| T1 | 1 | 1 | 1 |00:00:00.01 | 7 |
|* 3 | TABLE ACCESS FULL| T2 | 1 | 1 | 1 |00:00:00.01 | 3457 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T1"."NUM"=20)
3 - filter("T1"."ID"="T2"."T1_ID")
Note
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----
- dynamic sampling used for this statement
25 rows selected.
下面是T2表先访问，作为驱动表。
select /*+ leading(t2) use_nl(t1)*/ * from t1,t2 where t1.id=t2.t1_id and t1.num=20;
SQL> select sql_id,child_number,sql_text from v$sql where sql_text like '%leading(t2)%';
SQL_ID CHILD_NUMBER
------------- ------------
SQL_TEXT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
f0n69rt7v5gu6 0
select sql_id,child_number,sql_text from v$sql where sql_text like '%leading(t2)%'
bgnx742r31spv 0
select /*+ leading(t2) use_nl(t1)*/ * from t1,t2 where t1.id=t2.t1_id and t1.num=20
SQL> select * from table(dbms_xplan.display_cursor('bgnx742r31spv',0,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID bgnx742r31spv, child number 0
-------------------------------------
select /*+ leading(t2) use_nl(t1)*/ * from t1,t2 where t1.id=t2.t1_id and
t1.num=20
Plan hash value: 4016936828
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 1 | NESTED LOOPS | | 1 | 1 | 1 |00:00:01.14 | 603K|
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 2 | TABLE ACCESS FULL| T2 | 1 | 87396 | 100K|00:00:00.10 | 3457 |
|* 3 | TABLE ACCESS FULL| T1 | 100K| 1 | 1 |00:00:00.94 | 600K|
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("T1"."NUM"=20 AND "T1"."ID"="T2"."T1_ID"))
Note
-----
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- dynamic sampling used for this statement
24 rows selected.
从上面的两个执行计划可以清楚的看出，T1表先访问的情况下NESTED LOOPS的BUFFER是3464，而T2表先访问的情况下，BUFFER是603K。相差近100倍。T1作为驱动表的情况下，T1，T2都只被访问了1次，而T2作为驱动表的时候，T1 被访问100K，即T2 表的记录数次数。
结论：嵌套循环连接要特别注意驱动表的顺序，小的结果集先访问，大的结果集后访问，才能保证被驱动表的访问次数降低最低，从而提升性能。
如果我不用HINT，ORACLE 的优化器会选择什么表的连接方式呢？ 请看如下实验：
SQL〉select * from t1,t2 where t1.id=t2.t1_id and t1.num=20;
SQL> select * from table(dbms_xplan.display_cursor('8kg1wzjq5yk0u',0,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8kg1wzjq5yk0u, child number 0
-------------------------------------
select * from t1,t2 where t1.id=t2.t1_id and t1.num=20
Plan hash value: 1838229974
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN | | 1 | 1 | 1 |00:00:00.22 | 3480 | 764K| 764K| 299K (0)|
|* 2 | TABLE ACCESS FULL| T1 | 1 | 1 | 1 |00:00:00.01 | 6 | | | |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 3 | TABLE ACCESS FULL| T2 | 1 | 87396 | 100K|00:00:00.01 | 3457 | | | |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."ID"="T2"."T1_ID")
2 - filter("T1"."NUM"=20)
Note
-----
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- dynamic sampling used for this statement
24 rows selected.
从上面的实验可以看出ORACLE 选择的是HASH JOIN连接的方式。
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
