微課sql最佳化(14)、表的連線方法(3)-關於Nested Loops Join(巢狀迴圈)

orastar發表於2020-03-04
SQLOOP巢狀

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")


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31442014/viewspace-2678273/，如需轉載，請註明出處，否則將追究法律責任。

相關文章