兩表連線一：巢狀迴圈連線

一、前言：對於一名有志於成為SQL調優的開發人員或SQL的DBA，就很有必要了解下ORACLE資料庫在對兩個表進行連線時的執行機制，因為再複雜的執行計劃也是每次分解成兩個表的連線去執行的。ORACLE資料庫有常見的三種連線表的方法：巢狀迴圈連線、合併連線、雜湊連線。本文件透過實驗的方法總結這三種連線的應用和影響效能的因素；

 

二、巢狀迴圈連線的預備知識：本文件主要介紹巢狀迴圈連線，因此先了解巢狀迴圈連線的一些基礎知識，打好基礎知識，才更容易學習。

1、概念：巢狀迴圈連線處理的兩個資料集被稱為外部迴圈（也叫驅動表）和內部迴圈，當外部迴圈執行一次的時候，內部迴圈需要針對外部迴圈返回的每條記錄執行一次；

 

2、特性：在所有的資料返回之前，就可以返回結果的提一條資料；

         可以有效的利用索引來處理限制條件與連線條件；

         支援所有型別的連線；

3、優化器會按照一定的規則來決定兩張表誰是驅動表、誰是被驅動表。

   

二、測試環境說明：資料庫版本：11.2.0.3

表A1     NUM_ROWS    3,658,250（百萬級別）,沒有索引指令碼：create table hr.A1 as select * from all_objects;(然後執行以下指令碼幾次，產生大量的資料   insert into hr.a1 select * from hr.a1）

表B1    NUM_ROWS  100CREATE TABLE HR.B2(ID  NUMBER)

 

 

測試方法：

透過HINT去改變ORACLE兩表連線產生的執行計劃，並對比幾種執行計劃的效率；（友情提示：執行計劃的檢視方法，請在本部落格中查詢）

 

測試一：b1為內表，執行巢狀迴圈連線

指令碼：

select /*+ ordered use_nl(b1) */ *

from a1,b1

where a1.object_id=b1.id ;

 

執行計劃：

解讀：

外表A1執行一次後，執行一次B1的全表掃描，然後再根據條件進行過濾，外部表A1合計執行3658次的外部迴圈；

計劃時間：16：35：25

 

測試二：a1為內表，執行巢狀迴圈連線

指令碼：

select /*+ ordered use_nl(a1) */ *

from a1,b1

where a1.object_id=b1.id ;

 

執行計劃：

解讀：

外表B1執行一次後，執行一次A1的全表掃描，然後再根據條件進行過濾，外部表B1合計執行100次的外部迴圈；

計劃時間：00：02：53

 

結論一：在最簡單的兩表的巢狀迴圈連線過程中，行數較少的表應該為驅動表，會有更高的執行效率，但是這個行為是ORACLE本身來決定，而決定的這個動作主要由各表的統計資訊，所以當對整個執行計劃有疑問時，請檢查統計資訊是否正確；

 

測試三：連線條件增加索引，在表A1的object_id列上面增加索引；

指令碼：

select  /*+ USE_NL_WITH_INDEX(a1 A1_ID) */  *

from a1,b1

where a1.object_id=b1.id ;

解讀：當A1表增加索引後，外部表迴圈B1完成一次後，內表可以透過這個值去搜尋索引，根據索引後的結果再到A1表獲取資料，避免了全表掃描；

 

結論二：在巢狀迴圈連線中，連線條件中可以用到索引，如果內表的選擇性很強，那麼在調優的過程中，可以增加連線條件為索引；