Oracle中的Hash Join詳解

一、  hash join概念

hash join(HJ)是一種用於equi-join（而anti-join就是使用NOT IN時的join）的技術。在Oracle中，它是從7.3開始引入的，

以代替sort-merge和nested-loop join方式，提高效率。在CBO（hash join只有在CBO才可能被使用到）模式下，最佳化器計算代價時，

首先會考慮hash join。

可以通過提示use_hash來強制使用hash join，也可以通過修改會話或資料庫參數HASH_JOIN_ENABLED=FALSE（預設為TRUE）強

制不使用hash join。

Hash join的主要資源消耗在於CPU（在記憶體中建立臨時的hash表，並進行hash計算），而merge join的資源消耗主要在於此盤IO

（掃描表或索引）。在並行系統中，hash join對CPU的消耗更加明顯。所以在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.ora或spfile中修改。在8.0及之前版本，

它的預設值是1，在8i及以後版本，預設值是0。一般設定為1-(65536/DB_BLOCK_SIZE)。

在9i中，這個參數是一個隱藏參數：_HASH_MULTIBLOCK_IO_COUNT，可以通過表x$ksppi查詢和修改。

另外，在MTS中，這個參數將不起作用（只會使用1）。

它的最大值受到OS的IO頻寬和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.9；Po2(n)為n的2次方；C=HASH_MULTIBLOCK_IO_COUNT*DB_BLOCK_SIZE。

1 2 下一頁