Oracle 的 hash join連線方式

hash join是oracle裡面一個非常強悍的功能，當做hash join時，oracle會選擇一個表作為驅動表，先根據過濾條件排除不必要的資料，然後將結果集做成hash表，放入程式的hash area，接著掃描第二張表，將行的鍵值做hash運算，到記憶體的hash表裡面去探測，如果探測成功，就返回資料，否則這行就丟棄掉。這個是最基本的解釋，實際情況中，考慮到單個程式PGA的大小，oracle不會讓程式任意的消耗OS記憶體，hash area是有一定限制的，所以在oracle中，hash也有三種模式：

optimal，onepass，multipass

optimal：當驅動結果集生成的hash表全部可以放入PGA的hash area時，稱為optimal，大致過程如下：
1、先根據驅動表，得到驅動結果集；
2、在hash area生成hash bulket，並將若干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物理讀的次數會顯著增加。