一、概述
單表執行計劃是理解多表執行計劃的基礎。
兩張表的連接有三種執行方式：1)嵌套循環連接;2)散列連接;3)歸並連接。兩張表連接時選擇這三種中的哪一種呢?這取決於索引、以及連接的代價。在該系列的第三篇(本文)文章中講解嵌套循環連接，第四篇文章中講解散列連接，第五篇文章中講解歸並連接。在第六篇以後會分析IN子查詢以及EXISTS子查詢。
達夢數據庫、Oracle數據庫、SQL Server數據庫在數據庫執行計劃方面並無本質區別，因此上篇文章使用達夢數據庫作為實例數據庫進行分析，這篇文章我們選擇Oracle 10g作為實例數據庫。
讀完本文後，應該能夠讀懂這三個數據庫的嵌套循環連接執行計劃。
另外需要申明一點的是：因為Oracle的源代碼是不公開的，我這裡描寫的是根據執行計劃、成本代價以及10053文件進行反推的結果，盡管這樣，從大的方向上講，不會出現問題，僅做拋磚引玉。
二、深入理解嵌套循環執行計劃
Oracle數據庫常用的顯示執行計劃的方式有兩種：
1) set autotrace on 命令;
2) explain plan for 命令;
舉例說明使用set autotrace命令：
SQL> create table t1(c1 int,c2 int);
Table created.
SQL> create index it1c1 on t1(c1);
Index created.
SQL> insert into t1 values(1,1);
1 row created.
SQL> insert into t1 values(2,2);
1 row created.
SQL> commit;
Commit complete.
SQL> set autotrace on explain;
SQL> select c1 from t1 where c1=1;
我們可以看到，執行了“set autotrace on explain;”語句之後，接下來的查詢、插入、更新、刪除語句就會顯示執行計劃，直到執行“set autotrace off;”語句。如果是設置了“set autotrace on;”，除了會顯示執行計劃之外，還會顯示一些有用的統計信息。本系列文章不涉及查詢代價的評估分析。
我們從上一段代碼中，我們發現在顯示“select c1 from t1 where c1=1;”執行計劃之前顯示了該執行語句的查詢結果。這說明：顯示執行計劃之前就真正地將該查詢語句執行了一遍。這樣會帶來一個不好後果，假設我們現在有一條語句，執行的時間需要半個小時，即使我們僅僅需要知道該語句的執行計劃，此種情況下，我們必須等待半個小時。因此，如果查詢的性能很慢，我們可以選擇選擇使用explain plan for命令。
舉例說明explain plan for命令：
SQL> explain plan for select c1 from t1 where c1=1;
Explained.
SQL> select * from table(DBMS_XPLAN.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2624316456
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IT1C1 | 1 | 13 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identifIEd by Operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - Access("C1"=1)
Note
-----
- dynamic sampling used for this statement
17 rows selected.
SQL>
用“explain plan for 查詢語句;”生成執行計劃，然後使用“select * from table(DBMS_XPLAN.display);”語句顯示執行計劃。
下面的內容，將通過一些例子來理解嵌套理解執行計劃：
1.不帶索引的嵌套連接的執行計劃該如何理解?
構造處測試場景：
查詢語句為：
select /*+ USE_NL(t2) */ c1,c2 from t1 inner join t2 on c1=d2;
該語句中“/*+ USE_NL(t2) */”是我們常說的hint提示，這裡的USE_NL告訴優化程序使用嵌套連接對表進行連接，t2為內部表。此查詢語句的執行計劃為：
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=2 Bytes=78)
1 0 NESTED LOOPS (Cost=4 Card=2 Bytes=78)
2 1 TABLE Access (FULL) OF 'T1' (TABLE) (Cost=2 Card=2 Bytes
=52)
3 1 TABLE Access (FULL) OF 'T2' (TABLE) (Cost=1 Card=1 Bytes
=13)
“Execution Plan”顯示優化程序用來執行查詢的步驟。每一步都被賦予一個ID值(以0開始)。第二個數字顯示當前操作符的父結點。在這個執行計劃中，“NESTED LOOPS”的父結點是“SELECT STATEMENT”，“TABLE ACCESS (FULL) OF 'T1' (TABLE)”與“TABLE ACCESS (FULL) OF 'T2' (TABLE)”的父結點都是“NESTED LOOPS”。也可能稱為，操作符“SELECT STATEMENT”的孩子結點是“NESTED LOOPS”，操作符“NESTED LOOPS”的第一個孩子結點是“TABLE ACCESS (FULL) OF 'T1' (TABLE)”，操作符“NESTED LOOPS”的第二個孩子結點是“TABLE Access (FULL) OF 'T2' (TABLE)”。
第二行表示，對表T1進行全表掃描，括號中的三個值是該步驟的成本代價，這裡不作闡述。第三行表示，對T2進行全表掃描，這裡還隱藏了一個細節：此處進行了c1=d1的判斷。參考explain plan for生成的執行計劃：
SQL> explain plan for select /*+ USE_NL(t2) */ c1,c2 from t1 inner join t2 on c
1=d2;
Explained.
SQL> select * from table(DBMS_XPLAN.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4033694122
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 78 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 2 | 78 | 4 (0)| 00:00:01 |
| 2 | TABLE Access FULL| T1 | 2 | 52 | 2 (0)| 00:00:01 |
|* 3 | TABLE Access FULL| T2 | 1 | 13 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identifIEd by Operation id):
---------------------------------------------------
3 - filter("C1"="D2")
Note
-----
- dynamic sampling used for this statement
19 rows selected.
SQL>
這裡顯示的步驟0、1、2、3與前面通過set autotrace on命令顯示的執行計劃在意義上是一樣的。紅顏色表明t2只能掃描到符合過濾條件c1=d1的記錄才會將控制權傳給父節點“NESTED LOOPS”。
對於該查詢語句的執行，如果用代碼可以描述成這樣：
for (rec1 is t1’s first record; rec1!=NULL; rec1=rec1->next)
for(rec2 is t2’s first record; rec2!=NULL; rec2=rec->next)
{
if(rec1.c1==rec2.d1)
put result(rec1.c1,rec1.c2) into result set;
}
也就是說，t1與t2先生成笛卡爾集，然後過過濾條件c1=d1過濾該笛卡爾集。
其實，數據庫執行該語句的步驟也是類似的，下面是執行該語句的步驟：
1） TAF(T1)(“TABLE Access (FULL) OF 'T1'”的簡寫)取得T1的第一條記錄（1,1）傳遞給NL（“NESTED LOOPS”的簡寫），將控制權傳遞給操作符NL。
2） 操作符NL將控制權傳給第二個孩子TAF(T2)（“TABLE Access (FULL) OF 'T2'”的簡寫）。
3） TAF(T2)取得T2的第一條記錄(1,1)，符合過濾條件c1=d1，將控制權傳給操作符NL。
4） NL將記錄（1,1）傳給SS(“SELECT STATEMENT”的簡寫)，將控制權傳給SS。
5） SS將記錄(1,1)放入結果集合，將控制權限傳給NL。
6） NL將控制權限傳給TAF(T2)。
7） TAF(T2)取得T2表的下一條記錄（2，2），不符合條件c1=d1；取得下一條記錄（3,3），不符合條件（4，4）。取得下一條記錄，取不到記錄。T2表掃描結束。將控制權限傳遞給NL。
8） NL將控制權限傳給第一個孩子TAF(T1)。
9） TAF（T1）取得T1表的下一條記錄（2，2）傳遞給NL,將控制權傳給NL。
10） NL將控制權傳給第二個孩子TAF(T2)。
11） TAF(T2)取得T2的第一條（1，1），不符合過濾條件c1=d1；取得下一條記錄（2，2），滿足條件c1=d1，將控制權傳給操作符NL。
12） NL將記錄（2，2）傳給SS，將控制權傳給SS。
13） SS將記錄（2，2）放入結果集，將控制權傳給NL。
14） NL將控制權限傳給TAF(T2)。
15） TAF(T2)取得T2的下一條記錄（3,3），不符合過濾條件c1=d1；取得下一條記錄（4，4），不符合過濾條件c1=d1；取得下一條記錄，取不到記錄。T2表掃描結束。將控制權限傳遞給NL。
16） NL將控制權限傳給第一個孩子TAF(T1)。
17） TAF(T1)取得T1表的下一條記錄，取不到記錄，T1表掃描結束。將控制權傳給NL，通知NL掃描結束。
18） NL將控制權限傳給SS，通知SS操作結束。
19） SS將結果集（包含記錄（1，1）、（2，2））發送給客戶端。
在上面的例子中，只查詢顯示t1的列，如果要顯示t2的列，情況是一樣，只是TAF(T2)需要將符合條件的T2記錄傳遞給NL，然後NL組合成符合條件的(c1,c2,d1,d2)傳遞給SS。
select /*+ USE_NL(t2) */ c1,c2,d1,d2 from t1 inner join t2 on c1=d2;
對應的執行計劃：
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=2 Bytes=104
)
1 0 NESTED LOOPS (Cost=4 Card=2 Bytes=104)
2 1 TABLE Access (FULL) OF 'T1' (TABLE) (Cost=2 Card=2 Bytes
=52)
3 1 TABLE Access (FULL) OF 'T2' (TABLE) (Cost=1 Card=1 Bytes
=26)
2.使用非唯一索引的嵌套連接的執行計劃該如何理解?
測試數據與1中描述的一樣。
查詢語句：
select /*+ index(t2) */ c1,c2,d1 from t1 inner join t2 on c1=d1;
對應的執行計劃：
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=2 Bytes=78)
1 0 NESTED LOOPS (Cost=4 Card=2 Bytes=78)
2 1 TABLE Access (FULL) OF 'T1' (TABLE) (Cost=2 Card=2 Bytes
=52)
3 1 INDEX (RANGE SCAN) OF 'IT2D1' (INDEX) (Cost=1 Card=1 Byt
es=13)
使用explain plan對應的執行計劃：
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2841753667
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 78 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 2 | 78 | 4 (0)| 00:00:01 |
| 2 | TABLE Access FULL| T1 | 2 | 52 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IT2D1 | 1 | 13 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identifIEd by Operation id):
---------------------------------------------------
3 - Access("C1"="D1") //此處的Access表示使用值3去命中索引IT2D1對應的B樹。
Note
-----
- dynamic sampling used for this statement
19 rows selected.
SQL>
對於該查詢語句的執行，如果用代碼可以描述成這樣：
for (rec1 is t1’s first record; rec1!=NULL; rec1=rec1->next)
for(rec2 in t2’s first record that match c1=d1; d1=c1; rec2=rec->next)
{
put result(rec1.c1,rec1.c2，rec2.d1) into result set;
}
數據庫執行該執行語句的步驟也是類似的，下面是執行該執行語句的步驟：
1) TAF(T1)(“TABLE Access (FULL) OF 'T1'”的簡寫)取得表T1的第一條記錄(1,1)傳遞給NL（“NESTED LOOPS”的簡寫），將控制權傳遞給NL。
2) 操作符NL將控制權傳遞給第二個孩子IRS(IT2D1)(“INDEX (RANGE SCAN) OF 'IT2D1'”的簡寫)。
3) IRS(IT2D1)使用鍵值(1)去命中索引IT2D1對應的B樹，得到索引記錄(1,rowid1)。將d1對應的數據(1)傳遞給NL，將控制權傳遞給NL。注意，在本例中，將d1的數據上傳是因為select中出現了d1，也就是說要將d1的值傳給客戶端，如果select中沒有d1，此處就和上例中是一樣的，不需要傳遞d1給上層。
4) 操作NL組合生成記錄(1,1,1)（對應select項(c1,c2,d1)）傳給SS，將控制權傳給SS。
5) 操作符SS將記錄(1,1,1)放入結果集，將控制權傳給NL。
6) NL將控制權傳給IRS(IT2D1)。此處傳給IRS(IT2D1)的原因是，it2d1是非唯一索引，可能有兩條以上的記錄符合d1=1。
7) IRS(IT2D1)取得下一條記錄(2,rowid2)，因為2!=1，所以對應d1=1的索引查找已經結束，通知NL,將控制權限傳遞給NL。
8) NL控制權傳給TAF(T1)。
9) TAF(T1)取得下一條記錄(2,2)傳遞給NL，將控制權傳給NL。
10) NL將控制權傳給IRS(IT2D1)。
11) IRS(IT2D1)使用鍵值(2)去命中索引IT2D1對應的B樹，得到索引記錄(2,rowid2)。將d1對應的數據(2)傳遞給NL，將控制權傳遞給NL。
12) 操作NL組合生成記錄(2,2,2)傳給SS，將控制權傳給SS。
13) 操作符SS將記錄(2,2,2)放入結果集，將控制權傳給NL。
14) NL將控制權傳給IRS(IT2D1)。
15) IRS(IT2D1)取得下一條記錄(3,rowid3)，因為3!=2，所以對應d1=2的索引查找已經結束，通知NL查找結束,將控制權限傳遞給NL。
16) NL控制權傳給TAF(T1)。
17) TAF(T1)取得下一條記錄，發現已經掃描結束，通知NL掃描結束，將控制權傳給NL。
18) NL通知SS掃描結束，將控制權傳給SS。
19) SS將結果集（包含記錄（1，1，1）、（2，2，2））發送給客戶端。
3.使用唯一索引的嵌套連接的執行計劃該如何理解?
測試數據與1中描述的一樣。刪除原來的非唯一索引，建立唯一索引：
drop index it2d1;
create unique index iut2d1 on t2(d1);
查詢語句：
select /*+ index(t2) */ c1,c2,d1 from t1 inner join t2 on c1=d1;
對應的執行計劃：
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=2 Bytes=78)
1 0 NESTED LOOPS (Cost=2 Card=2 Bytes=78)
2 1 TABLE Access (FULL) OF 'T1' (TABLE) (Cost=2 Card=2 Bytes
=52)
3 1 INDEX (UNIQUE SCAN) OF 'IUT2D1' (INDEX (UNIQUE)) (Cost=0
Card=1 Bytes=13)
數據庫執行該執行語句的步驟也是類似的，下面是執行該執行語句的步驟：
1) TAF(T1)(“TABLE Access (FULL) OF 'T1'”的簡寫)取得表T1的第一條記錄(1,1)傳遞給NL（“NESTED LOOPS”的簡寫），將控制權傳遞給NL。
2) 操作符NL將控制權傳遞給第二個孩子IUS(IUT2D1)(“INDEX (UNIQUE SCAN) OF 'IUT2D1''”的簡寫)。
3) IUS(IUT2D1)使用鍵值(1)去命中索引IUT2D1對應的B樹，得到索引記錄(1,rowid1)。將d1對應的數據(1)傳遞給NL，將控制權傳遞給NL。
4) 操作NL組合生成記錄(1,1,1)（對應select項(c1,c2,d1)）傳給SS，將控制權傳給SS。
5) 操作符SS將記錄(1,1,1)放入結果集，將控制權傳給NL。
6) NL控制權傳給TAF(T1)。因為iut2d1是唯一索引，所以只可能有一條記錄滿足d1=1，所以此時不需要將控制權限再傳給IUS(IUT2D1)。
7) TAF(T1)取得下一條記錄(2,2)傳遞給NL，將控制權傳給NL。
8) NL將控制權傳給IRS(IUT2D1)。
9) IUS(IUT2D1)使用鍵值(2)去命中索引IUT2D1對應的B樹，得到索引記錄(2,rowid2)。將d1對應的數據(2)傳遞給NL，將控制權傳遞給NL。
10) 操作NL組合生成記錄(2,2,2)傳給SS，將控制權傳給SS。
11) 操作符SS將記錄(2,2,2)放入結果集，將控制權傳給NL。
12) NL控制權傳給TAF(T1)。
13) TAF(T1)取得下一條記錄，發現已經掃描結束，通知NL掃描結束，將控制權傳給NL。
14) NL通知SS掃描結束，將控制權傳給SS。
15) SS將結果集（包含記錄（1，1，1）、（2，2，2））發送給客戶端。
