oracle hash join演算法原理

Hash join演算法原理

 

自從oracke 7.3以來，oracle提供了一種新的join技術，就是hash join。Hash Join只能用於相等連線，且只能在CBO優化器模式下。相對於nested loop join，hash join更適合處理大型結果集。Hash join不需要在驅動表上存在索引。

 

一．       Hash Join概述

Hash join演算法的一個基本思想就是根據小的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分割成多個不相連的分割槽（分別記作S_i和B_i），這個階段叫做分割槽階段；然後各自相應的分割槽，即S_i和B_i再做Hash join，這個階段叫做join階段。

如果在分割槽後，針對某個分割槽所建的hash table還是太大的話，oracle就採用nested-loops hash join。所謂的nested-loops hash join就是對部分S_i建立hash table，然後讀取所有的B_i與所建的hash table做連線，然後再對剩餘的S_i建立hash table，再將所有的B_i與所建的hash table做連線，直至所有的S_i都連線完了。

Hash Join演算法有一個限制，就是它是在假設兩張表在連線鍵上是均勻的，也就是說每個分割槽擁有差不多的資料。但是實際當中資料都是不均勻的，為了很好地解決這個問題，oracle引進了幾種技術，點陣圖向量過濾、角色互換、柱狀圖，這些術語的具體意義會在後面詳細介紹。

 

二．       Hash Join原理

我們用一個例子來解釋Hash Join演算法的原理，以及上述所提到的術語。

考慮以下兩個資料集。

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 Join的第一步就是判定小表（即build input）是否能完全存放在hash area記憶體中。如果能完全存放在記憶體中，則在記憶體中建立hash table，這是最簡單的hash join。

如果不能全部存放在記憶體中，則build input必須分割槽。分割槽的個數叫做fan-out。Fan-out是由hash_area_size和cluster size來決定的。其中cluster size等於db_block_size * hash_multiblock_io_count，hash_multiblock_io_count在oracle9i中是隱含引數。這裡需要注意的是fan-out並不是build input的大小/hash_ara_size，也就是說oracle決定的分割槽大小有可能還是不能完全存放在hash area記憶體中。大的fan-out導致許多小的分割槽，影響效能，而小的fan-out導致少數的大的分割槽，以至於每個分割槽不能全部存放在記憶體中，這也影響hash join的效能。

Oracle採用內部一個hash函式作用於連線鍵上，將S和B分割成多個分割槽，在這裡我們假設這個hash函式為求餘函式，即Mod(join_column_value,10)。這樣產生十個分割槽，如下表。

 

 

 

分割槽		B0	B1	B2	B3	B4	B5	B6	B7	B8	B9
	值	0,0,10,10	1,1,1,1,11	2,2,2,2,2,2	3	NULL	NULL	NULL	NULL	8	9,9,9
S0	10	√
S1	1,1,1		√
S2	Null
S3	3,3				√
S4	4,4,4,4
S5	5
S6	NULL
S7	NULL
S8	8,8,8,8									√
S9	NULL

經過這樣的分割槽之後，只需要相應的分割槽之間做join即可（也就是所謂的partition pairs），如果有一個分割槽為NULL的話，則相應的分割槽join即可忽略。

在將S表讀入記憶體分割槽時，oracle即記錄連線鍵的唯一值，構建成所謂的點陣圖向量，它需要佔hash area記憶體的5%左右。在這裡即為{1,3,4,5,8,10}。

當對B表進行分割槽時，將每一個連線鍵上的值與點陣圖向量相比較，如果不在其中，則將其記錄丟棄。在我們這個例子中，B表中以下資料將被丟棄

{0,0,2,2,2,2,2,2,9,9,9,9,9}。這個過程就是點陣圖向量過濾。

當S1,B1做完連線後，接著對Si,Bi進行連線，這裡oracle將比較兩個分割槽，選取小的那個做build input，就是動態角色互換，這個動態角色互換髮生在除第一對分割槽以外的分割槽上面。

 

三．       Hash Join演算法

第1步：判定小表是否能夠全部存放在hash area記憶體中，如果可以，則做記憶體hash join。如果不行，轉第二步。

第2步：決定fan-out數。

       (Number of Partitions) * C<= F_avm *M

        其中C為Cluster size，

其值為DB_BLOCK_SIZE*HASH_MULTIBLOCK_IO_COUNT；F_avm為hash area記憶體可以使用的百分比，一般為0.8左右；M為Hash_area_size的大小。

 

第3步：讀取部分小表S，採用內部hash函式(這裡稱為hash_fun_1)，將連線鍵值對映至某個分割槽，同時採用hash_fun_2函式對連線鍵值產生另外一個hash值，這個hash值用於建立hash table用，並且與連線鍵值存放在一起。

第4步：對build input建立點陣圖向量。

第5步：如果記憶體中沒有空間了，則將分割槽寫至磁碟上。

第6步：讀取小表S的剩餘部分，重複第三步，直至小表S全部讀完。

 

第7步：將分割槽按大小排序，選取幾個分割槽建立hash table(這裡選取分割槽的原則是使選取的數量最多)。

 

第8步：根據前面用hash_fun_2函式計算好的hash值，建立hash table。

第9步：讀取表B，採用點陣圖向量進行點陣圖向量過濾。

第10步：對通過過濾的資料採用hash_fun_1函式將資料對映到相應的分割槽中去，並計算hash_fun_2的hash值。

第11步：如果所落的分割槽在記憶體中，則將前面通過hash_fun_2函式計算所得的hash值與記憶體中已存在的hash table做連線， 將結果寫致磁碟上。如果所落的分割槽不在記憶體中，則將相應的值與表S相應的分割槽放在一起。

第12步：繼續讀取表B，重複第9步，直至表B讀取完畢。

 

第13步：讀取相應的(Si,Bi)做hash連線。在這裡會發生動態角色互換。

第14步：如果分割槽過後，最小的分割槽也比記憶體大，則發生nested- loop hash join。

四．       Hash Join的成本

1.      In-Memory Hash Join

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.      On-Disk Hash Join

根據上述的步驟描述，我們可以看出

Cost(HJ)=Cost(HJ1)+Cost(HJ2)

其中Cost(HJ1)的成本就是掃描S,B表，並將無法放在記憶體上的部分寫回磁碟，對應前面第2步至第12步。Cost(HJ2)即為做nested-loop hash join的成本，對應前面的第13步至第14步。

 

其中Cost(HJ1)近似等於Read(S)+Read(B)+Write((S-M)+(B-B*M/S))。

 

因為在做nested-loop hash join時，對每一chunk的build input，都需要讀取整個probe input，因此

Cost(HJ2)近似等於Read((S-M)+n*(B-B*M/S))

其中n是nested-loop hash join需要迴圈的次數。

n=(S/F)/M

一般情況下，如果n在於10的話，hash join的效能將大大下降。從n的計算公式可以看出，n與Fan-out成反比例，提高fan-out，可以降低n。當hash_area_size是固定時，可以降低cluster size來提高fan-out。

 

從這裡我們可以看出，提高hash_multiblock_io_count引數的值並不一定提高hash join的效能。

五．       其它

1．確認小表是驅動表

2．確認涉及到的表和連線鍵分析過了。

3．如果在連線鍵上資料不均勻的話，建議做柱狀圖。

4．如果可以，調大hash_area_size的大小或pga_aggregate_target的值。

5．Hash Join適合於小表與大表連線、返回大型結果集的連線。

 

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