Oracle 表串連方式詳解

在查看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方式效率更高。 

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

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

過程：將兩個表排序，然後將排序後兩個表合并。 

1 2 下一頁