oracle表串連

標籤：style   http   color   io   os   使用   ar   for   strong   

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方式效率更高。
         對於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.    排序合并串連的兩個資料集可以平行處理,而嵌套迴圈和雜湊串連不能.

註： 整理自網路

NLJ：
根據串連鍵，把小表的每一行，和大筆的每一行做對比。 一般情況下會對大表串連鍵上建index。
成本計算：讀小表的行+（小表的每一行×讀取大表的行）

SMJ:
讀取小表和大表讀的行，根據串連鍵排序，然後根據排序後的資料集（小表的和大表的）合進行串連。
理想狀態：2個表的排序操作都能在記憶體進行
常規情況：2階段進行：
          1.sort run階段：資料讀取到記憶體，排序，寫出到暫存資料表空間。直到所有的row sourse完成排序。
          2.merge階段：之前每次寫到暫存資料表空間的資料（即sort run）被重新讀入到記憶體，進行merge。
成本計算：讀取小表的行+寫小表的run sort到temp資料表空間+
          讀取大表的行+寫大表的run sort到temp資料表空間+
          cpu對小表和大表的排序消耗

join串連中的並行機制：
能在NLJ和SMJ中使用。並發查詢的執行計畫是一個樹形結構（DFO），每個樹上的DFO節點是一個sql操作過程，並且能把該操作過程能指派到一個query slave進程中。

Hash Join：
用在條件為等號的環境下，hash串連的效率要比SMJ和NLJ要高（如果索引的blevel比較高），且hash join不要求一定要有索引。
hash join的基本演算法是在記憶體中建立hash table，小表叫做build input，理想狀態下，build input在記憶體中；大表叫做probe input。
在實際的情況下，build input不一定能完全放在記憶體中，此時，和probe input一樣，build input的溢出部分，會在磁碟上用hash函數分割成小的不連續的分區。
hash串連分2個階段進行：
1.partitioning階段：即在記憶體中存放build input，若放不下，則和probe input一樣，在磁碟上利用hash函數將input分割成小的不連續的分區。
1.join階段：在相同的索引值上，將build input和probe input的分區進行一一配對，並且join。
以上的hash串連的演算法也叫grace join。

hash演算法的限制：該演算法是假設hash之後串連值傾斜度（skew）不高，使得每個partition上保持大約相同數量的rows。但是事實上不可能保證每個partition有大約相同數量的rows。

hybrid hash join是在oracle 7.3之後應用的比較高效的hash演算法，它是在grace join的基礎上，盡量在記憶體在搭建build input。
但是由於不可能在每個partition中保證相同的rows，後來有出來一些技術如bit-vector filtering、role reversal和histograms。我們將在後面的章節講到這些技術。
分區的數量，我們叫做fan-out。fan-out太多會導致partition較多，從而影響IO，但是如果fan-out太少又會造成數量較少的大partition，這些大partition無法放在hash記憶體中。因此選擇一個合適的fan-out和partition 大小，是hash join調優的關鍵。

當partitioning之後，build input或者probe input如果在記憶體中無法放下，hash table的溢出部分將會做nested-loops hash join。
hash table是由部分（在記憶體內的）build input partition和所有的probe input串連構成。剩餘的不在記憶體中的build input將通過迭代的方式繼續擷取，直到所有的build input被迭代完。

hash join 規則：
假設有2個表：
S = { 1, 1, 1, 3, 3, 4, 4, 4, 4, 5, 8, 8, 8, 8, 10 }
B = { 0, 0, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 3, 8, 9, 9, 9, 10, 10, 11}
首先會根據hash_area_size確定小表是否能做build table。如果build input不能完全的在記憶體中，那麼build input就被會分區，分區的數量我們稱為fan-out，
fan-out是由hash_area_size和cluster size決定的，cluster size是指分區中還沒被寫出到暫存資料表空間的連續塊的數量。
cluster size=db_block_size * hash_multiblock_io_count，hash_multiblock_io_count在oracle9i中是隱含參數
hash演算法會把S和B表的串連列分成不串連的桶（bucket），桶也叫做分區（partition），hash演算法是盡量的減少資料的傾斜度，使得資料盡量均勻的分布。

以上面的S表和B表為例，如果我們簡單的假設hash演算法是取餘，則：
S的分區為:{0,1,3,4,5,8}
B的分區為:{0,1,2,3,8,9}
經過這樣的分區之後，只需要相應的分區之間做join即可（也就是所謂的partition pairs），如果有一個分區為NULL的話，則相應的分區join即可忽略。即他們可以在0,1,3,8上做串連。
相應的如果我們用SMJ或者NLJ，在串連上的消耗要高很多。

當build input被讀入hash area記憶體準備進行分區的時候，build input表中的唯一列值被作為串連鍵構建起來，即所謂的位元影像向量（bitmap vector）。
按照上面的例子，bitmap vector為：{1,3,4,8,10}。
bitmap vector用來決定在partitioning 階段和大表（probe input）進行串連的時候，哪些行是需要的，哪些是不需要的，不需要的將被丟棄，這就是我們上面說的bit-vector filtering技術。
當對B表進行分區時，將每一個串連鍵上的值與位元影像向量相比較，如果不在其中，則將其記錄丟棄。在我們這個例子中，B表中以下資料將被丟棄
這個例子中，B表中以下資料將被丟棄{0,0,2,2,2,2,2,2,9,9,9,9,9}。

當第一個S分區和B分區做完串連後，需要將第i個S分區和B分區讀入記憶體中做串連，此時會根據分區的大小，自動的選擇哪個做build input，哪個做probe input。這就是動態角色轉換技術，即我們前面所說的role reversal。

總體說來，hash演算法為如下步驟（以下考慮的是hash area size不夠大，需要寫出到磁碟的情況）：
1.決定fanout的數量，即分區的數量。分區數量×cluster大小<=記憶體中能用的hasharea比例×hashareasize大小
2.讀取S表，根據內部的hash演算法（我們暫時稱作hash_fun_1），將串連列上的值map到分區。在此步驟，還利用另一個hash函數（我們稱作hash_fun_2）產生另一個hash值，和串連鍵一起存放。該值將在後續的構建hashtable中用到。
3.為S表的獨立的串連鍵形成的bitmap向量
4.根據partition的大小排序，使得盡量多（也就是盡量小的partition進入記憶體。這也就是之前要根據partition大小排序的原因）的分區放入記憶體來構建hashtable。如果記憶體不夠放下所有的parittion，則輸出到tempsegment上）。
5.利用之前的hash值，構建S表的hashtable。
6.讀取B表，根據位元影像向量過濾，如果通過hash演算法後B的值與位元影像向量比較不在其中，則丟棄該行。
7.將過濾後B表的行，利用內部的hash_fun_1和串連鍵，形成partition。
8.如果B表的行能在記憶體中形成分區，就利用內部的hash_fun_2執行串連，並且形成合適的hash桶。
9.如果不能在記憶體中形成分區，則將S的分區、串連鍵、B表的剩餘行寫出到磁碟。
10.從磁碟中讀取未處理的S表和B表的分區。利用內部hash_fun_2值，構建hashtable，在構建時將使用動態角色轉換技術。在第一次迴圈中，最佳化器將先使用小表做buildinput，大表做probeinput，角色轉換技術僅在第一次迴圈後使用。
11.如果probeinput或者buildinput中（已經經過角色轉換了）較小的那個還是不能放入到記憶體中，則將讀取較小的那個buildinput到記憶體chunk中，並且迴圈的和probeinput做hash串連。這我們叫做nestedhashloopsjoin。

hash join的成本計算：
1.最簡單的情況，hash area足夠大能放下S表分區後的所有的build input：
cost（HJ）=read（S）+build hash table in memory（cpu）+read（B）+perform. in memory join（cpu）
如果忽略cpu的成本，cost（HJ）無限接近於 read（s）+read（b）

2.當hash area（後面用M表示）不夠大，不能容納build input，S，它將會寫出到磁碟。當然了，表B也會寫出到磁碟：
total cost 無限接近於 cost（HJ迴圈1）+cost（HJ迴圈2）
其中cost（HJ迴圈1） 無限接近於 read（S）+read（B）+write（（S-M）+（B-B*M/S））
即上述2至9步。

由於HJ迴圈2使用了nested hash loops join，hash join的演算法處理Si和Bi分區。當每個build input的chunk被讀取時，probe input將被多次讀。
因此cost（HJ迴圈2） 無限接近於 read（（S-M）+n×（B-B*M/S））
即上述10至11步。
n為進行nested hash loops join的次數，n一般在10以上，也就是需要構建的partition大於10倍的hash area。

oracle表串連