Oracle中的Hash Join祥解
一、 hash join概念
Hashjoin(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。
三、 Hash join的過程
一次完整的hash join如下：
1．計算小表的分區（bucket）數決定hash join的一個重要因素是小表的分區（bucket）數。這個數字由hash_area_size、hash_multiblock_io_count和db_block_size參數共同決定。Oracle會保留hash area的20%來存儲分區的頭資訊、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算法計算的資料，根據各個bucket的hash值（第一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好的資料和從R和S中計算存儲在磁盤上的分區。
10．二次hash計算
從R和S的分區集中抽取出最小的一個分區，使用第二種hash函數計算出并在記憶體中建立hash表。采用第二種hash函數的原因是為了使資料分布性更好。
11．二次hash比對
在從另一個資料源（與hash在記憶體的那個分區所屬資料源不同的）中讀取分區資料，與記憶體中的新hash表進行比對。傳回join資料。
12．完成全部hash join
繼續按照9-11處理剩餘分區，直到全部處理完畢。整個hash join就完成了。
四、關于唯一健值的hash位圖
這個位圖包含了每個hash分區是否有有值的資訊。它記錄了有資料的分區的hash值。這個位圖的最大作用就是，如果S表中的資料沒有與記憶體中的hash表比對上，先檢視這個位圖，已決定是否将沒有比對的資料寫入磁盤。那些不可能比對到的資料（即位圖上對應的分區沒有資料）就不再寫入磁盤。
以上轉自:http://www.hellodba.com/Doc/Oracle_Hash_Join.htm