Oracle 表串連方式介紹（SML + NL + HJ）

Join是一種試圖將兩個表結合在一起的謂詞，一次只能串連2個表，表串連也可以被稱為表關聯。在後面的敘述中，我們將會使用”row source”來代替”表”，因為使用row source更嚴謹一些，並且將參與串連的2個row source分別稱為row source1和row source 2。Join過程的各個步驟經常是串列操作，即使相關的row source可以被並行訪問，即可以並行的讀取做join串連的兩個row source的資料，但是在將表中符合限制條件的資料讀入到記憶體形成row source後，join的其它步驟一般是串列的。有多種方法可以將2個表串連起來，當然每種方法都有自己的優缺點，每種連線類型只有在特定的條件下才會發揮出其最大優勢。

　　row source(表)之間的串連順序對於查詢的效率有非常大的影響。通過首先存取特定的表，即將該表作為驅動表，這樣可以先應用某些限制條件，從而得到一個較小的row source，使串連的效率較高，這也就是我們常說的要先執行限制條件的原因。一般是在將表讀入記憶體時，應用where子句中對該表的限制條件。

　　根據2個row source的串連條件的中操作符的不同，可以將串連分為等值串連(如WHERE A.COL3 = B.COL4)、非等值串連(WHERE A.COL3 > B.COL4)、外串連(WHERE A.COL3 = B.COL4(+))。上面的各個串連的串連原理都基本一樣，所以為了簡單起見，下面以等值串連為例進行介紹。

　　在後面的介紹中，都以：
　　SELECT A.COL1, B.COL2
　　FROM A, B
　　WHERE A.COL3 = B.COL4;
　　為例進行說明，假設A表為Row Soruce1，則其對應的串連操作關聯列為COL 3；B表為Row Soruce2，則其對應的串連操作關聯列為COL 4；

　　連線類型：目前為止，無論串連操作符如何，典型的連線類型共有3種：
　　排序合并串連(Sort Merge Join (SMJ) )
　　嵌套迴圈(Nested Loops (NL) )
　　雜湊串連(Hash Join)

　　排序合并串連(Sort Merge Join, SMJ)

　　內部串連過程：
　　1) 首先產生row source1需要的資料，然後對這些資料按照串連操作關聯列(如A.col3)進行排序。
　　2) 隨後產生row source2需要的資料，然後對這些資料按照與sort source1對應的串連操作關聯列(如B.col4)進行排序。
　　3) 最後兩邊已排序的行被放在一起執行合併作業，即將2個row source按照串連條件串連起來

　　下面是串連步驟的圖形表示：
　　MERGE
　　/\
　　SORTSORT
　　||
　　Row Source 1Row Source 2

　　如果row source已經在串連關聯列上被排序，則該串連操作就不需要再進行sort操作，這樣可以大大提高這種串連操作的連線速度，因為排序是個極其費資源的操作，特別是對於較大的表。預先排序的row source包括已經被索引的列(如a.col3或b.col4上有索引)或row source已經在前面的步驟中被排序了。儘管合并兩個row source的過程是串列的，但是可以並行訪問這兩個row source(如並行讀入資料，並行排序).

　　SMJ串連的例子：

　　SQL> explain plan for
　　select /*+ ordered */ e.deptno, d.deptno
　　from emp e, dept d
　　where e.deptno = d.deptno
　　order by e.deptno, d.deptno;

　　Query Plan
　　-------------------------------------
　　SELECT STATEMENT [CHOOSE] Cost=17
　　MERGE JOIN
　　SORT JOIN
　　TABLE ACCESS FULL EMP [ANALYZED]
　　SORT JOIN
　　TABLE ACCESS FULL DEPT [ANALYZED]

　　排序是一個費時、費資源的操作，特別對於大表。基於這個原因，SMJ經常不是一個特別有效串連方法，但是如果2個row source都已經預先排序，則這種串連方法的效率也是蠻高的。

　　嵌套迴圈(Nested Loops, NL)

　　這個串連方法有驅動表(外部表格)的概念。其實，該串連過程就是一個2層嵌套迴圈，所以外層迴圈的次數越少越好，這也就是我們為什麼將小表或返回較小row source的表作為驅動表(用於外層迴圈)的理論依據。但是這個理論只是一般指導原則，因為遵循這個理論並不能總保證使語句產生的I/O次數最少。有時不遵守這個理論依據，反而會獲得更好的效率。如果使用這種方法，決定使用哪個表作為驅動表很重要。有時如果驅動表選擇不正確，將會導致語句的效能很差、很差。

　　內部串連過程：
　　Row source1的Row 1 ---------------- Probe ->Row source 2
　　Row source1的Row 2 ---------------- Probe ->Row source 2
　　Row source1的Row 3 ---------------- Probe ->Row source 2
　　…….
　　Row source1的Row n ---------------- Probe ->Row source 2

　　從內部串連過程來看，需要用row source1中的每一行，去匹配row source2中的所有行，所以此時保持row source1儘可能的小與高效的訪問row source2(一般通過索引實現)是影響這個串連效率的關鍵問題。這隻是理論指導原則，目的是使整個串連操作產生最少的物理I/O次數，而且如果遵守這個原則，一般也會使總的物理I/O數最少。但是如果不遵從這個指導原則，反而能用更少的物理I/O實現串連操作，那儘管違反指導原則吧！因為最少的物理I/O次數才是我們應該遵從的真正的指導原則，在後面的具體案例分析中就給出這樣的例子。

　　在上面的串連過程中，我們稱Row source1為驅動表或外部表格。Row Source2被稱為被探查表或內部表。

　　在NESTED LOOPS串連中，Oracle讀取row source1中的每一行，然後在row sourc2中檢查是否有匹配的行，所有被匹配的行都被放到結果集中，然後處理row source1中的下一行。這個過程一直繼續，直到row source1中的所有行都被處理。這是從串連操作中可以得到第一個匹配行的最快的方法之一，這種類型的串連可以用在需要快速響應的語句中，以響應速度為主要目標。

    如果driving row source(外部表格)比較小，並且在inner row source(內部表)上有唯一索引，或有高選擇性非唯一索引時，使用這種方法可以得到較好的效率。NESTED LOOPS有其它串連方法沒有的的一個優點是：可以先返回已經串連的行，而不必等待所有的串連操作處理完才返回資料，這可以實現快速的回應時間。

　　如果不使用並行操作，最好的驅動表是那些應用了where 限制條件後，可以返回較少行資料的的表，所以大表也可能稱為驅動表，關鍵看限制條件。對於並行查詢，我們經常選擇大表作為驅動表，因為大表可以充分利用並行功能。當然，有時對查詢使用並行操作並不一定會比查詢不使用並行操作效率高，因為最後可能每個表只有很少的行符合限制條件，而且還要看你的硬體設定是否可以支援並行(如是否有多個CPU，多個硬碟控制器)，所以要具體問題具體對待。

　　NL串連的例子：
　　SQL> explain plan for
　　select a.dname,b.sql
　　from dept a,emp b
　　where a.deptno = b.deptno;

　　Query Plan
　　-------------------------
　　SELECT STATEMENT [CHOOSE] Cost=5
　　NESTED LOOPS
　　TABLE ACCESS FULL DEPT [ANALYZED]
　　TABLE ACCESS FULL EMP [ANALYZED]

　　雜湊串連(Hash Join, HJ)

　　這種串連是在oracle 7.3以後引入的，從理論上來說比NL與SMJ更高效，而且只用在CBO最佳化器中。

1 2 下一頁