ORACLE Hash Join

tthero00boo發表於2013-07-08
Oracle
ORACLE Hash Join

引自：Oracle中hashjoin研究 2012年第27期 SCIENCE&TECHNOLOGYINFORMATION
http://hwhuang.iteye.com/blog/1479076
http://www.360doc.com/content/11/0625/17/2660674_129507672.shtml

tom大師視訊，hj.swf ：http://asktom.oracle.com/pls/apex/f?p=100:8:0::NO

1. hash join原理概述

雜湊連線是一種基於equi-join的技術。Oracle是從7.3版本開始引入雜湊連線，來替代sort-merge和nested-loopjoin方式，目的是為了oracle提高效率。當雜湊連線時，將大量消耗CPU(在記憶體中建立臨時的hash表，並進行hash計算)資源，反觀mergejoin，其主要消耗盤IO(掃描表或索引)資源上。hashjoin在並行系統中對CPU的消耗非常明顯。所以在系統CPU資源緊張時，最好的辦法是限制使用hash join

引入雜湊連線，主要目的是為了解決巢狀迴圈連線中大量隨機讀取的問題，同時又要解決排序合併連線中排序代價過大的問題。在不需要排序的情況下，雜湊函式就能夠把連線物件集中在一起。可以雜湊函式並不直接負責連線任務，而是負責把將要連線的物件提前集中在特定的位置。將具有相同雜湊值的資料行集中儲存在相同的空間上，該空間被稱之為“分割槽”。一般把必須要進行連線的兩個分割槽稱之為為“分割槽對”。

雜湊連線過程
當在記憶體中雜湊表構建完成後，進行下面的處理。雜湊連線過程是，首先進行掃描第二個大表；當大表不能完全cache到可用記憶體的時候，大表繼續被分成很多分割槽；大表的第一個分割槽裝入到cache記憶體；對大表第一個分割槽的資料進行掃描，並與雜湊表進行比較，這時如果有匹配的紀錄，新增到結果集裡面；其它的分割槽處理同第一個分割槽操作步驟一樣；所有的分割槽處理完後，ORACLE對產生的結果集進行歸併、彙總，產生最終的結果。
內部機制
執行雜湊連線時會獲得一個資料集，利用一個內部的雜湊函式作用於連線列上以產生雜湊鍵，我們可以將該資料集轉換為一個儲存器內單表雜湊簇(single-tablehashcluster)的等價形式(假設有足夠的記憶體)。然後我們開始獲取第二個表的資料，當讀取每一行時，在連線列上應用同樣的雜湊函式，並檢視是否在儲存器內雜湊簇中定位到一個匹配的行。

(a)限制條件(等值連線或notexists)
因為在連線列上使用了雜湊函式以使雜湊簇中的資料隨機分佈，因而只有當連線條件是等值條件的時候雜湊連線才能正常執行。notexists也是可以的，它是等值條件的反向使用而已。
(b)表命名
構建表(用它來“構建”儲存器中的雜湊簇)。探查表(用它來“探查”儲存器中的雜湊簇)。
(c)適用情況
雜湊連線適用於小結果集連線到大結果集(注意不是小表和大表，而是結果集)

生成“偽查詢”
優化器執行雜湊連線，首先將sql分解為完全分離的兩個部分
偽查詢中的列是選取連線列和所有引用的列。
估算兩個資料集的行數和行大小(選擇依據)
行大小bytes是根據user_tab_columns的avg_col_len列得到的。這兩個引數直接影響到資料集的大小進而會影響構建表的選擇。優化器會選擇較小的資料集作為構建表。需要注意的是，行大小並不是所有欄位的長度和，而是偽查詢中的欄位長度和。其中偽查詢涉及的欄位除了最終顯示的欄位外，還包括連線的欄位。這些欄位都是需要被讀取的，因此也要計算在內

當表連線中的小表能夠完全cache到可用記憶體的時候，雜湊連線的效果最佳。雜湊連線的成本只是兩個表從硬碟讀入到記憶體的成本。但是，如果雜湊表過大而不能全部cache到可用記憶體時，優化器將會把雜湊表分成多個分割槽，再將分割槽逐一cache到記憶體中。當表的分割槽超過了可用記憶體時，分割槽的部分資料就會臨時地寫到磁碟上的臨時表空間上

適用原則：
當連線的兩個表是用等值連線並且表的資料量比較大時，優化器才可能採用雜湊連線。
雜湊連線適用於連線兩個大的結果集或者一大一小兩個結果集。雜湊連線的優點在於返回所有其他記錄，尤其當hashtable能夠適合memory，其效能極其優越

雜湊連線優缺點(對比其他連線方式):
(a)優點
在缺少有用的索引時，雜湊連線比巢狀迴圈連線更加有效。雜湊連線可能比巢狀迴圈連線快，因為處理記憶體中的雜湊表比檢索B樹更加迅速。
雜湊連線可能比排序合併連線更快，因為這種情況下，只有一張源表需要排序。在排序合併連線中，兩張表的資料都需要先做排序，然後做MERGE操作，因此效率相對最差。
雜湊連線的效率最高，因為只要對兩張表掃描一次。在絕大多數情況下，雜湊連線效率比其他JOIN方式效率更高。
(b)缺點(限制條件)
和排序合併連線、群集連線一樣，雜湊連線只能用於等價連線。和排序合併連線一樣，雜湊連線使用記憶體資源，並且當用於排序記憶體不足時，會增加臨時表空間的IO(這將使這種連線方法速度變的極慢)。
雜湊連線返回第一條結果會慢，因為一個資料來源必須要hash到記憶體中或者記憶體和磁碟中。當請求快速返回記錄集時，Oracle傾向於使用NL

--形象的來說
假設我們有兩個資料集合A和B，當我們對兩個資料集做hash join，我們首先挑選一個資料集，比如是A，把A轉變成為記憶體中的一個hash table，這個hash table和我們前面介紹過的single hash cluster table是很類似的，只不過single hash cluster table是在磁碟上，hash join的hash table是放在記憶體裡。在這裡，我們使用的是oracle內部的hash函式，hash函式的hash key就是A和B做join的那個列。

然後我們開始從第二個結果集B裡面獲取資料，對於B裡的每一條資料使用相同的hash函式找到對應的A裡資料在hash table裡面的位置，在這個位置上檢查能不能找到匹配的資料。

在這裡，我們把資料集A稱為build table 構建表（也就是A被build到記憶體中建立hash table），資料集合B叫做probe table 探查表（也就是說我們用B去probe hash table）。

其實hash join就有點像outer table是普通表，inner table是一個single hash cluster table的nested loops，都是使用一張表上的資料，通過hash函式到另一個hash table裡面尋找匹配的資料。不過不同的是hash join裡面的hash table是放在使用者使用的專有記憶體，即UGA裡（10g以後hash join和sort使用的記憶體被分配到PGA裡），而single hash cluster table最多隻能cache到buffer cache裡，而對buffer cache的過多訪問會產生大量latch，這個問題在hash join裡就不存在。

hash join會選擇比較小的表做build table，這個小是看那個表上得到的結果集比較小，就把哪個表作為build table。
根據小的row sources (稱作build input，我們記較小的表為S，較大的表為B)  建立一個可以存在於hash area記憶體中的hash table，然後用大的row sources(稱作probe input) 來探測前面所建的hash table。如果hash area記憶體不夠大，hash table就無法完全存放在hash area記憶體中。針對這種情況，Oracle在連線鍵利用一個hash函式將build input和probe input分割成多個不相連的分割槽（分別記作Si和Bi），這個階段叫做分割槽階段；然後各自相應的分割槽，即Si和Bi再做Hash join，這個階段叫做join階段。 

引用連結2中的例子
select
bu.build_vc,
pb.probe_vc,
pb.probe_padding
from
build_tab bu,
probe_tab pb
where
bu.id between 1 and 500
and pb.id = bu.id_probe
;
把這個SQL拆分成2部分:
select
bu.id
bu.build_vc,
bu.id_probe
from
build_tab bu
where
bu.id between 1 and 500
;
和
select
pb.probe_vc,
pb.probe_padding,
pb.id
from
probe_tab pb
;
oracle會根據這兩個虛擬的查詢的返回結果決定到底哪個表的返回結果集表較小，而這個結果集的大小則是 = 結果集的行數 * user_tab_columns.sum(avg_col_len)計算得出的。

查詢user_tab_columns
SQL> select column_name, avg_col_len from user_tab_columns where table_name='PROBE_TAB';

COLUMN_NAME AVG_COL_LEN
-------------------- -----------
ID 5
N1 4
PROBE_VC 21
PROBE_PADDING 501

4 rows selected.

SQL> select column_name, avg_col_len from user_tab_columns where table_name='BUILD_TAB';

COLUMN_NAME AVG_COL_LEN
-------------------- -----------
ID 4
ID_PROBE 5
BUILD_VC 21
BUILD_PADDING 501

4 rows selected.

我們看到probe_tab的結果集是(5+21+521)*5000=2635000，
build_tab的結果集是(4+5+21)*500=15000，
所以把build_tab作為build table。

2. hash join工作模式
hash join有三種工作模式，分別是optimal模式，onepass模式和multipass模式

optimal:當驅動結果集生成的hash表全部可以放入PGA的hash area時，稱為optimal，大致過程如下： 
1.先根據驅動表，得到驅動結果集 
2.在hash area生成hash bulket，（這裡的hash bucket總是2的n次方，比如1024或4096），並將若干bulket分成一組，成為一個partition，還會生成一個bitmap的列表，每個bulket在上面佔一位 
3.對結果集的join鍵做hash運算，將資料分散到相應partition的bulket中，當運算完成後，如果鍵值唯一性較高的話，bulket裡的資料會比較均勻，也有可能有的桶裡面資料會是空的，這樣bitmap上對應的標誌位就是0，有資料的桶，標誌位會是1 
4.開始掃描第二張表，對jion鍵做hash運算，確定應該到某個partition的某個bulket去探測，探測之前，會看這個bulket的bitmap是否會1，如果為0，表示沒資料，這行就直接丟棄掉 
5.如果bitmap為1，則在桶內做精確匹配，判斷OK後，返回資料 

這個是最優的hash join，他的成本基本是兩張表的full table scan，在加微量的hash運算 

onepass 
如果程式的pga很小，或者驅動表結果集很大，超過了hash area的大小，會怎麼辦？當然會用到臨時表空間，此時oracle的處理方式稍微複雜點需奧注意上面提到的有個partition的概念，可以這麼理解，資料是經過兩次hash運算的，先確定你的partition，再確定你的bulket，假設hash area小於整個hash table，但至少大於一個partition的size，這個時候走的就是onepass 
當我們生成好hash表後，狀況是部分partition留在記憶體中，其他的partition留在磁碟臨時表空間中，當然也有可能某個partition一半在記憶體，一半在磁碟，剩下的步驟大致如下： 
1.掃描第二張表，對join鍵做hash運算，確定好對應的partition和bulket 
2.檢視bitmap，確定bulket是否有資料，沒有則直接丟棄 
3.如果有資料，並且這個partition是在記憶體中的，就進入對應的桶去精確匹配，能匹配上，就返回這行資料，否則丟棄 
4.如果partition是在磁碟上的，則將這行資料放入磁碟中暫存起來，儲存的形式也是partition，bulket的方式 
5.當第二張表被掃描完後，剩下的是驅動表和探測表生成的一大堆partition，保留在磁碟上 
6.由於兩邊的資料都按照相同的hash演算法做了partition和bulket，現在只要成對的比較兩邊partition資料即可，並且在比較的時候，oracle也做了優化處理，沒有嚴格的驅動與被驅動關係，他會在partition對中選較小的一個作為驅動來進行，直到磁碟上所有的partition對都join完 

可以發現，相比optimal，他多出的成本是對於無法放入記憶體的partition，重新讀取了一次，所以稱為onepass，只要你的記憶體保證能裝下一個partition，oracle都會騰挪空間，每個磁碟partition做到onepass 

multipass 
這是最複雜，最糟糕的hash join，此時hash area小到連一個partition也容納不下，當掃描好驅動表後，可能只有半個partition留在hash area中，另半個加其他的partition全在磁碟上,剩下的步驟和onepass比價類似，不同的是針對partition的處理 
由於驅動表只有半個partition在記憶體中，探測表對應的partition資料做探測時，如果匹配不上，這行還不能直接丟棄，需要繼續保留到磁碟，和驅動表剩下的半個partition再做join，這裡舉例的是記憶體可以裝下半個partition，如果裝的更少的話，反覆join的次數將更多，當發生multipass時，partition物理讀的次數會顯著增加

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28859270/viewspace-765698/，如需轉載，請註明出處，否則將追究法律責任。

相關文章