Oracle中的Hash Join祥解

chenfeng發表於2015-12-24

一、  hash join概念

     hash join(HJ)是一種用於equi-join(而anti-join就是使用NOT IN時的join)的技術。在Oracle中,它是從7.3開始引入的,
以代替sort-mergenested-loop join方式,提高效率。在CBOhash join只有在CBO才可能被使用到)模式下,最佳化器計算代價時,
首先會考慮hash join
     可以透過提示use_hash來強制使用hash join,也可以透過修改會話或資料庫引數HASH_JOIN_ENABLED=FALSE(預設為TRUE)強
制不使用hash join
     Hash join的主要資源消耗在於CPU(在記憶體中建立臨時的hash表,並進行hash計算),而merge join的資源消耗主要在於此盤IO
(掃描表或索引)。在並行系統中,hash joinCPU的消耗更加明顯。所以在CPU緊張時,最好限制使用hash join
     在絕大多數情況下,hash join效率比其他join方式效率更高:
     Sort-Merge Join(SMJ),兩張表的資料都需要先做排序,然後做merge。因此效率相對最差;
     Nested-Loop Join(NL)效率比SMJ更高。特別是當驅動表的資料量很大(集的勢高)時。這樣可以並行掃描內表。
     Hash join效率最高,因為只要對兩張表掃描一次。
 
     Hash join一般用於一張小表和一張大表進行join時。Hash join的過程大致如下(下面所說的記憶體就指sort area,關於過程,後
面會作詳細討論):
1.  一張小表被hash在記憶體中。因為資料量小,所以這張小表的大多數資料已經駐入在記憶體中,剩下的少量資料被放置在臨時表空間中;
2.  每讀取大表的一條記錄,就和小表中記憶體中的資料進行比較,如果符合,則立即輸出資料(也就是說沒有讀取臨時表空間中的小表的數
據)。而如果大表的資料與小表中臨時表空間的資料相符合,則不直接輸出,而是也被儲存臨時表空間中。
3.  當大表的所有資料都讀取完畢,將臨時表空間中的資料以其輸出。
 
     如果小表的資料量足夠小(小於hash area size),那所有資料就都在記憶體中了,可以避免對臨時表空間的讀寫。
 
     如果是並行環境下,前面中的第2步就變成如下了:
2.  每讀取一條大表的記錄,和記憶體中小表的資料比較,如果符合先做join,而不直接輸出,直到整張大表資料讀取完畢。如果記憶體足夠,
Join好的資料就儲存在記憶體中。否則,就儲存在臨時表空間中。

二、  Oracle中與hash join相關的引數

     首先,要注意的是,hash join只有在CBO方式下才會被啟用。在oracle中與hash join相關的引數主要有以下幾個:

1.             HASH_JOIN_ENABLED

     這個引數是控制查詢計劃是否採用hash join的“總開關”。它可以在會話級和例項級被修改。預設為TRUE,既可以(不是一定,要看優
化器計算出來的代價)使用。如果設為FALSE,則禁止使用hash join

2.             HASH_AREA_SIZE

     這個引數控制每個會話的hash記憶體空間有多大。它也可以在會話級和例項級被修改。預設(也是推薦)值是sort area空間大小的兩倍
2*SORT_AREA_SIZE)。要提高hash join的效率,就一定儘量保證sort area足夠大,能容納下整個小表的資料。但是因為每個會話都會
開闢一個這麼大的記憶體空間作為hash記憶體,所以不能過大(一般不建議超過2M)。
     Oracle9i及以後版本中,Oracle不推薦在dedicated server中使用這個引數來設定hash記憶體,而是推薦透過設定
PGA_AGGRATE_TARGET引數來自動管理PGA記憶體。保留HASH_AREA_SIZE只是為了向後相容。在dedicated server中,hash area是從
PGA中分配的,而在MTS(Multi-Threaded Server)中,hash area是從UGA中分配的。
     另外,還要注意的是,每個會話並不一定只開啟一個hash area,因為一個查詢中可能不止一個hash join,這是就會相應同時開啟多個
hash area

3.             HAHS_MULTIBLOCK_IO_COUNT

     這個引數決定每次讀入hash area的資料塊數量。因此它會對IO效能產生影響。他只能在init.oraspfile中修改。在8.0及之前版本,
它的預設值是1,在8i及以後版本,預設值是0。一般設定為1-(65536/DB_BLOCK_SIZE)
     9i中,這個引數是一個隱藏引數:_HASH_MULTIBLOCK_IO_COUNT,可以透過表x$ksppi查詢和修改。
     另外,在MTS中,這個引數將不起作用(只會使用1)。
     它的最大值受到OSIO頻寬和DB_BLOCK_SIZE的影響。既不能大於MAX_IO_SIZE/DB_BLOCK_SIZE
     8i及以後版本,如果這個值設定為0,則表示在每次查詢時,Oracle自己自動計算這個值。這個值對IO效能影響非常大,因此,建議不要
修改這個引數,使用預設值0,讓Oracle自己去計算這個值。
     如果一定要設定這個值,要保證以下不等式能成立:
     R/M < Po2(M/C)
     其中,R表示小表的大小;M=HASH_AREA_SIZE*0.9Po2(n)n2次方;C=HASH_MULTIBLOCK_IO_COUNT*DB_BLOCK_SIZE

三、  Hash join的過程

     一次完整的hash join如下:

1.             計算小表的分割槽(bucket)數

     決定hash join的一個重要因素是小表的分割槽(bucket)數。這個數字由hash_area_sizehash_multiblock_io_count
db_block_size引數共同決定。Oracle會保留hash area20%來儲存分割槽的頭資訊、hash點陣圖資訊和hash表。因此,這個數字的計算公式是:
     Bucket=0.8*hash_area_size/(hash_multiblock_io_count*db_block_size)

2.             Hash計算   

     讀取小表資料(簡稱為R),並對每一條資料根據hash演算法進行計算。Oracle採用兩種hash演算法進行計算,計算出能達到最快速度的hash
(第一hash值和第二hash值)。而關於這些分割槽的全部hash值(第一hash值)就成為hash表。

3.             存放資料到hash記憶體中

     將經過hash演算法計算的資料,根據各個buckethash值(第一hash值)分別放入相應的bucket中。第二hash值就存放在各條記錄中。

4.             建立hash點陣圖

     與此同時,也建立了一個關於這兩個hash值對映關係的hash點陣圖。

5.             超出記憶體大小部分被移到磁碟

     如果hash area被佔滿,那最大一個分割槽就會被寫到磁碟(臨時表空間)上去。任何需要寫入到磁碟分割槽上的記錄都會導致磁碟分割槽被更新。這
樣的話,就會嚴重影響效能,因此一定要儘量避免這種情況。
     2-5一直持續到整個表的資料讀取完畢。

6.             對分割槽排序

     為了能充分利用記憶體,儘量儲存更多的分割槽,Oracle會按照各個分割槽的大小將他們在記憶體中排序。

7.             讀取大表資料,進行hash匹配

     接下來就開始讀取大表(簡稱S)中的資料。按順序每讀取一條記錄,計算它的hash值,並檢查是否與記憶體中的分割槽的hash值一致。如果是,返
join資料。如果記憶體中的分割槽沒有符合的,就將S中的資料寫入到一個新的分割槽中,這個分割槽也採用與計算R一樣的演算法計算出hash值。也就是說這些
S中的資料產生的新的分割槽數應該和R的分割槽集的分割槽數一樣。這些新的分割槽被儲存在磁碟(臨時表空間)上。

8.             完全大表全部資料的讀取

     一直按照7進行,直到大表中的所有資料的讀取完畢。
 

9.             處理沒有join的資料

     這個時候就產生了一大堆join好的資料和從RS中計算儲存在磁碟上的分割槽。

10.       二次hash計算

     RS的分割槽集中抽取出最小的一個分割槽,使用第二種hash函式計算出並在記憶體中建立hash表。採用第二種hash函式的原因是為了使資料分佈
性更好。

11.       二次hash匹配

     在從另一個資料來源(與hash在記憶體的那個分割槽所屬資料來源不同的)中讀取分割槽資料,與記憶體中的新hash表進行匹配。返回join資料。

12.       完成全部hash join

     繼續按照9-11處理剩餘分割槽,直到全部處理完畢。
 
     整個hash join就完成了。
 

四、  關於唯一健值的hash點陣圖

     這個點陣圖包含了每個hash分割槽是否有有值的資訊。它記錄了有資料的分割槽的hash值。這個點陣圖的最大作用就是,如果S表中的資料沒有與記憶體中的
hash表匹配上,先檢視這個點陣圖,已決定是否將沒有匹配的資料寫入磁碟。那些不可能匹配到的資料(即點陣圖上對應的分割槽沒有資料)就不再寫入磁碟。

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

相關文章