1、NL的解释
Nested Loops Join(嵌套循环)，是指两个数据集被称为驱动表(outer loop or driving row source)和被驱动表(inner loop),驱动表只执行一次，被驱动表执行的次数等于驱动表返回的行数。如下图所示，
2、 NL特点
1、驱动表执行1次，被驱动表执行N次(N等于驱动表返回的行数)。注：驱动表返回多少行，被驱动表执行多少次。
2、在所有数据处理完之前，就可以返回结果集的第一条记录。
3、可以有效利用索引来处理限制条件和连接条件。
4、支持所有类型的连接。
3、NL的解释(2)
We fetch data from tables as we need it. Here -- for the nested loops join -- it was processed much like this:
for x in ( select * from t t1 )
loop
for y in ( select * from t t2 where t2.object_id = X.OBJECT_ID )
loop
-- output to client --
end loop
end loop
4、NL示例
--drop table ht.c_cons_nl;
--create table ht.c_cons_nl as select * from ht.c_cons;
--create table ht.a_amt_nl as select * from ht.a_amt;
--update ht.c_cons_nl set cons_name='nl_test' where rownum<100;
--commit;
请优化以下语句：
select /*+ use_nl(c,a) */c.cons_no,c.cons_name,c.org_name,a.AMT_YM,a.amt
from ht.c_cons_nl c,ht.a_amt_nl a
where c.cons_no=a.cons_no
and c.cons_name='nl_test';
set line 200
set heading off
alter session set statistics_level=all;
select /*+ use_nl(c,a) */c.cons_no,c.cons_name,c.org_name,a.AMT_YM,a.amt
from ht.c_cons_nl c,ht.a_amt_nl a
where c.cons_no=a.cons_no
and c.cons_name='nl_test';
select * from table(DBMS_XPLAN.DISPLAY_CURSOR(null,null,'allstats last'));
Plan hash value: 942926597
------------------------------------------------------------------------------------------
| Id | Operation
| Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 |
| 54 |00:00:00.05 | 2109 |
| 1 | NESTED LOOPS
|
| 1 | 56 | 54 |00:00:00.05 | 2109 |
|* 2 | TABLE ACCESS FULL| C_CONS_NL | 1 | 9 |
9 |00:00:00.01 | 62 |
|* 3 | TABLE ACCESS FULL| A_AMT_NL | 9 | 6 | 54 |00:00:00.05 | 2047 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("C"."CONS_NAME"='nl_test')
3 - filter("C"."CONS_NO"="A"."CONS_NO")
create index ht.idx_c_cons_name on ht.c_cons_nl(cons_name);
create index ht.idx_a_amt_no on ht.a_amt_nl(cons_no);
Plan hash value: 2953888364
-------------------------------------------------------------------------------------------------------------------
| Id | Operation
| Name
| Starts | E-Rows | A-Rows | A-Time
| Buffers | Reads |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT
|
| 1 |
|
54 |00:00:00.01 | 80 |
1 |
| 1 | NESTED LOOPS
|
| 1 | 56 |
54 |00:00:00.01 | 80 |
1 |
| 2 | NESTED LOOPS
|
| 1 | 56 |
54 |00:00:00.01 | 26 |
1 |
| 3 | TABLE ACCESS BY INDEX ROWID| C_CONS_NL
| 1 |
9 |
9 |00:00:00.01 | 11 |
0 |
|* 4 | INDEX RANGE SCAN
| IDX_C_CONS_NAME | 1 |
9 |
9 |00:00:00.01 |
6 |
0 |
|* 5 | INDEX RANGE SCAN
| IDX_A_AMT_NO
| 9 |
6 |
54 |00:00:00.01 | 15 |
1 |
| 6 | TABLE ACCESS BY INDEX ROWID | A_AMT_NL
| 54 |
6 |
54 |00:00:00.01 | 54 |
0 |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("C"."CONS_NAME"='nl_test')
5 - access("C"."CONS_NO"="A"."CONS_NO")