oracle多表串連方式Hash Join Nested Loop Join Merge Join

標籤：

在查看sql執行計畫時，我們會發現表的串連方式有多種，本文對錶的串連方式進行介紹以便更好看懂執行計畫和理解sql執行原理。 
一、串連方式：
        嵌套迴圈（Nested  Loops （NL））
      （散列）雜湊串連（Hash Join （HJ））
    （歸併）排序合并串連(Sort Merge Join (SMJ) )

二、串連說明：
    1.Oracle一次只能串連兩個表。不管查詢中有多少個表，Oracle 在串連中一次僅能操作兩張表。
    2.當執行多個表的串連時，最佳化器從一個表開始，將它與另一個表串連；然後將中間結果與下一個表串連，以此類推，直到處理完所有表為止。

三、表串連詳解：
     1.嵌套迴圈（Nested  Loops （NL））：
        嵌套迴圈實現機制(虛擬碼)：
          For r1 in (select rows from table_1 where colx={value})
          loop
                for r2 in (select rows from table_2 that match current row from table_1)
               loop
                  output values from current row of table_1 and current row of table_2;
               end loop;
         End loop;
   這段代碼由兩個迴圈構成。
   嵌套迴圈中的這兩個表通常稱為外部表格（outer table)和內部表(inner table)。
   在嵌套迴圈串連中,外部表格又稱為驅動表(driver table)
  虛擬碼中：table_1為驅動表，table_2為內表
  從虛擬碼中可以看出該串連過程就是一個2層嵌套迴圈，所以外層迴圈的次數越少越好，這也就是我們為什麼將小表或返回較小結果集的表作為驅動表的原因。
  NEST LOOP JOIN COST =   從第一個表取得資料的成本 +    從第一個表得到結果的基數 Х 對第二個表訪問一次的成本
所以嵌套迴圈一般適合於驅動表記錄集比較少（<10000）且內表有高效索引訪問方式。
  使用USE_NL(table_1 table_2)可強制CBO 執行嵌套迴圈串連。
  驅動表確定：驅動表【select rows from table_1 where colx={value} 】一般為根據where條件能得到較小結果集的表，而不一定是整個表記錄比較小的表。
         
    2.（散列）雜湊串連（Hash Join （HJ））：
        Hash join一般用於一張小表和一張大表進行join時。在絕大多數情況下，hash join效率比其他join方式效率更高。
         對於hash join的詳細理解，可參看網上一篇文章寫的比較透徹：http://www.hellodba.com/reader.php?ID=144&lang=cn

    3.排序合并串連(Sort Merge Join (SMJ) )：

       通常情況下散列串連的效果都比排序合并串連要好，然而如果行源已經被排過序，在執行排序合并串連時不需要再排序了，這時排序合并串連的效能會優於散列串連。可以使用USE_MERGE(table_1 table_2)來強制使用排序合并串連。
過程：將兩個表排序，然後將排序後兩個表合并。

四、串連方式總結：
1)）嵌套迴圈(nest loop):
          對於被串連的資料子集較小的情況，嵌套迴圈串連是較好的選擇。在嵌套迴圈中，外表驅動內表，外表返回的每一行都要在內表中檢索找到它匹配的行，因此整個查詢返回的結果集不能太大（大於10000不合適），要把返回子集較小的表作為外表（驅動表），而且在內表的串連欄位上一定要有索引。
2）雜湊串連(hash join):
         雜湊串連是大資料集串連時常用的方式，最佳化器使用兩個表中較小的表，利用串連鍵在記憶體中建立散列表，然後掃描較大的表並探測散列表，找出與散列表匹配的行。
        這種方式適用於較小的表完全可以放入記憶體的情況，這樣成本就是訪問兩個表的成本之和。但是在表很大的情況下並不能完全放入記憶體，這時最佳化器將它分割成若干不同的分區，不能放入記憶體的部分就把該分區寫入磁碟的臨時段。
        雜湊串連只能應用於等值串連(如WHERE A.COL3 = B.COL4)、非等值串連(WHERE A.COL3 > B.COL4)、外串連(WHERE A.COL3 = B.COL4(+))。
3）排序合并串連（Sort Merge Join ）
          通常情況下雜湊串連的效果都比排序合并串連要好。然而如果行源已經被排過序，在執行排序合并串連時不需要再排序了，這時排序歸併串連的效能會憂於雜湊串連。

五、串連方式應用情境：
1.　雜湊串連只適用於等值串連。
2.　嵌套迴圈是行源串連方式，只適合小量資料連線。
    　雜湊串連和排序合并串連是集合串連方式，適合大量資料連線。
3.　在等值串連方式下，返回少量記錄（<10000）且內部表在串連列上存在索引，適合嵌套迴圈串連。若返回大量記錄則適合雜湊串連。
4.　在等值串連方式下，兩個行源集合都很大，若串連列是高基數列，則適合雜湊串連，否則適合排序合并串連。
5.　嵌套迴圈串連可以先返回已經串連的行，而不必等待所有的串連操作處理完才返回資料。而其它兩種串連方式則不行。
6.    排序合并串連的兩個資料集可以平行處理,而嵌套迴圈和雜湊串連不能.

摘自: http://www.itpub.net/thread-1611025-1-1.html

 

oracle多表串連方式Hash Join Nested Loop Join Merge Join