嵌套迴圈串連(nested loops join)原理

這三類表串連方式是Oracle最基本的串連方式：
雜湊串連(hash join) 原理
排序合并串連(sort merge join)的原理

嵌套迴圈串連(nested loops join)

訪問次數：驅動表返回幾條，被驅動表訪問多少次。

驅動表是否有順序：有。

是否要排序：否。

應用情境： 1.關聯中有一個表比較小；

2.被關聯表的關聯欄位上有索引；

3.索引的索引值不應該重複率很高。

如果你做過開發，就把它看成兩層嵌套的for迴圈。

下面我們來做個實驗：

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

----解釋一下：

Starts為該sql執行的次數。

E-Rows為執行計畫預計的行數。

A-Rows為實際返回的行數。A-Rows跟E-Rows做比較，就可以確定哪一步執行計畫出了問題。
A-Time為每一步實際執行的時間（HH：MM：SS.FF），根據這一行可以知道該sql耗時在了哪個地方。
Buffers為每一步實際執行的邏輯讀或一致性讀。
Reads為物理讀。
OMem、1Mem為執行所需的記憶體評估值，0Mem為最優執行模式所需記憶體的評估值，1Mem為one-pass模式所需記憶體的評估值。
0/1/M 為最優/one-pass/multipass執行的次數。
Used-Mem耗的記憶體

 

---------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------
| 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 |

---------------------------------------------------------------------------------------

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
---------------------------------------------------------------------------------------------------------
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)
---------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------
| 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 ACCESS FULL| TEST2 | 3 | 1 | 3 |00:00:00.01 | 45 |
---------------------------------------------------------------------------------------
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

更多詳情見請繼續閱讀下一頁的精彩內容：

1 2 下一頁