Oracle 表的串連方式(2)-----HASH JOIN的基本機制2

標籤：style   io   ar   color   使用   sp   strong   on   資料   

Hash演算法原理

　　對於什麼是Hash演算法原理？這個問題有點難度，不是很好說清楚，來做一個比喻吧：我們有很多的小豬，每個的體重都不一樣，假設體重分布比較平均(我們考慮到公斤層級)，我們按照體重來分,劃分成100個小豬圈。 然後把每個小豬，按照體重趕進各自的豬圈裡，記錄檔案。 好了，如果我們要找某個小豬怎麼辦呢？我們需要每個豬圈，每個小豬的比對嗎？ 當然不需要了。 我們先看看要找的這個小豬的體重，然後就找到了對應的豬圈了。 在這個豬圈裡的小豬的數量就相對很少了。 我們在這個豬圈裡就可以相對快的找到我們要找到的那個小豬了。 對應於hash演算法。 就是按照hashcode分配不同的豬圈，將hashcode相同的豬放到一個豬圈裡。 尋找的時候，先找到hashcode對應的豬圈，然後在逐個比較裡面的小豬。 所以問題的關鍵就是建造多少個豬圈比較合適。 如果每個小豬的體重全部不同（考慮到毫克層級)，每個都建一個豬圈，那麼我們可以最快速度的找到這頭豬。缺點就是，建造那麼多豬圈的費用有點太高了。 如果我們按照10公斤層級進行劃分，那麼建造的豬圈只有幾個吧，那麼每個圈裡的小豬就很多了。我們雖然可以很快的找到豬圈，但從這個豬圈裡逐個確定那頭小豬也是很累的。 所以，好的hashcode，可以根據實際情況，根據具體的需求，在時間成本(更多的豬圈，更快的速度)和空間本(更少的豬圈，更低的空間需求)之間平衡。(摘自網路)

Hash JOIN基本過程（可對比《hash join的模式》）

　　如果兩個表（分別命名為表T1和表T2）在做表串連時使用的是雜湊串連，則Oracle在做雜湊串連時會依次順序執行如下步驟：

1、確定Hash Partition的數量：首先Oracle會根據參數HASH_AREA_SIZE、DB_BLOCK_SIZE和_HASH_MULTIBLOCK_IO_COUNT的值來決定Hash Partition的數量（Hash Partition 是一個邏輯上的概念，所有Hash Partition的集合就被稱之為Hash Table，即一個Hash Table是由多個Hash Partition所組成，而一個 Hash Partition又是由多個Hash Bucket所組成）；

2、確定驅動結果集：表T1和T2在施加了目標SQL中指定的謂詞條件（如果有的話）後得到的結果集中資料量較小的那個結果集會被Oracle選為雜湊串連的驅動結果集，這裡我們假設T1所對應的結果集的資料量相對較小，我們記為S；T2所對應的結果集的資料量相對較大，我們記為B；顯然 這裡S是驅動結果集，B是被驅動結果集；

3、計算雜湊值：接著Oracle會遍曆S，讀取S中的每一條記錄，並對S中的每一條記錄按照該記錄在表T1中的串連列做雜湊運算，這個雜湊運算會使用 兩個內建雜湊函數，這兩個雜湊函數會同時對該串連列計算雜湊值，我們把這兩個內建雜湊函數分別記為hash_func_1和hash_func_2， 它們所計算出來的雜湊值分別記為hash_value_1和hash_value_2；

4、分組：然後Oracle會按照hash_value_1的值把相應的S中的對應記錄儲存在不同Hash Partition的不同Hash Bucket裡，同時和該記錄儲存在 一起的還有該記錄用hash_func_2計算出來的hash_value_2的值。注意，儲存在Hash Bucket裡的記錄並不是目標表的完整行記錄，而是 只需要儲存位於目標SQL中的跟目標表相關的查詢列和串連列就足夠了；我們把S所對應的每一個Hash Partition記為Si；

5、構建位元影像：在構建Si的同時，Oracle會構建一個位元影像（BITMAP），這個位元影像用來標記Si所包含的每一個Hash Bucket是否有記錄（即記錄數是否大於0）

6、構建Hash Table過程：如果S的資料量很大，那麼在構建S所對應的Hash Table時，就可能會出現PGA的工作區（WORK AREA）被填滿的情況，這時候Oracle會 把工作區中現有的Hash Partition中包含記錄數最多的Hash Partition寫到磁碟上（TEMP資料表空間），接著Oracle會繼續構建S所對應的 Hash Table，在繼續構建的過程中，如果工作區又滿了，則Oracle會繼續重複上述挑選包含記錄數最多的Hash Partition並寫回到磁碟 上的動作；如果要構建的記錄所對應的Hash Partition已經事先被Oracle寫回到了磁碟上，則此時Oracle就會去磁碟上更新該Hash Partition， 即會把該條記錄和hash_value_2直接加到這個已經位於磁碟上的Hash Partition的相應Hash Bucket中；注意，極端情況下可能會出現只 有某個Hash Partition的部分記錄還在記憶體中，該Hash Partition的剩餘部分和餘下的所有Hash Partition都已經被寫回到磁碟上。（註：

7、上述構建S所對應的Hash Table的過程會一直持續下去，直到遍曆完S中的所有記錄為止；

8、排序：接著，Oracle會對所有的Si按照它們所包含的記錄數來排序，然後Oracle會把這些已經排好序的Hash Partition按順序依次、並且盡 可能的全部放到記憶體中（PGA的工作區），當然，如果實在放不下的話，放不下的那部分Hash Partition還是會位於磁碟上。我認為這個 按照Si的記錄數來排序的動作不是必須要做的，因為這個排序動作的根本目的就是為了儘可能多的把那些記錄數較小的Hash Partition 保留在記憶體中，而將那些已經被寫回到磁碟上、記錄數較大且現有記憶體已經放不下的Hash Partition保留在磁碟上，顯然，如果所有的 Si本來就都在記憶體中，也沒發生過將Si寫回到磁碟的操作，那這雷根本就不需要排序了。

9、至此Oracle已經處理完S，現在可以來開始處理B了；

10、Oracle會遍曆B，讀取B中的每一條記錄，並對B中的每一條記錄按照該記錄在表T2中的串連列做雜湊運算，這個雜湊運算和步驟3中的 雜湊運算是一模一樣的，即這個雜湊運算還是會用步驟3中的hash_func_1和hash_func_2，並且也會計算出兩個雜湊值hash_value_1和hash_value_2； 接著Oracle會按照該記錄所對應的雜湊值hash_value_1去Si裡找匹配的Hash Bucket；如果能找到匹配的Hash Bucket，則Oracle還會遍曆該 Hash Bucket中的每一條記錄，並會校正儲存於該Hash Bucket中的每一條記錄的串連列，看是否是真的匹配（即這裡要校正S和B中的匹配記 錄所對應的串連列是否真的相等，因為對於Hash運算而言，不同的值經過雜湊運算後的結果可能是一樣的），如果是真的匹配，則上述 hash_value_1所對應B中的記錄的位於目標SQL中的查詢列和該Hash Bucket中的匹配記錄便會組合起來，一起作為滿足目標SQL串連條件的記 錄返回；如果找不到匹配的Hash Bucket，則Oracle就會去訪問步驟5中構建的位元影像，如果位元影像顯示該Hash Bucket在Si中對應的記錄數大於0， 則說明該Hash Bucket雖然不在記憶體中，但它已經被寫回到了磁碟上，則此時Oracle就會按照上述hash_value_1的值把相應B中的對應記錄也 以Hash Partition的方式寫回到磁碟上，同時和該記錄儲存在一起的還有該記錄用hash_func_2計算出來的hash_value_2的值；如果位元影像顯示 該Hash Bucket在Si中對應的記錄數等於0，則Oracle就不用把上述hash_value_1所對應B中的記錄寫回到磁碟上了，因為這條記錄必然不滿足 目標SQL的串連條件；這個根據位元影像來決定是否將上述hash_value_1所對應B中的記錄寫回到磁碟的動作就是所謂的“位元影像過濾”；我們把B所對 應的每一個Hash Partition記為Bj；

11、上述去Si中尋找匹配Hash Bucket和構建Bj的過程會一直持續下去，直到遍曆完B中的所有記錄為止；

12、至此Oracle已經處理完所有位於記憶體中的Si和對應的Bj，現在只剩下位於磁碟上的Si和Bj還未處理；

13、因為在構建Si和Bj時用的是同樣的雜湊函數hash_func_1和hash_func_2，所以Oracle在處理位於磁碟上的Si和Bj的時候可以放心的配對處 理，即只有對應Hash Partition Number值相同的Si和Bj才可能會產生滿足串連條件的記錄；這裡我們用Sn和Bn來表示位於磁碟上且對應 Hash Partition Number值相同的Si和Bj；

14、對於每一對兒Sn和Bn，它們之中記錄數較少的會被當作驅動結果集，然後Oracle會用這個驅動結果集的Hash Bucket裡記錄的hash_value_2 來構建新的Hash Table，另外一個記錄數較大的會被當作被驅動結果集，然後Oracle會用這個被驅動結果集的Hash Bucket裡記錄的hash_value_2 去上述構建的新Hash Table中找匹配記錄；注意，對每一對兒Sn和Bn而言，Oracle始終會選擇它們中記錄數較少的來作為驅動結果集，所以每 一對兒Sn和Bn的驅動結果集都可能會發生變化，這就是所謂的“動態角色互換”；

15、步驟14中如果存在匹配記錄，則該匹配記錄也會作為滿足目標SQL串連條件的記錄返回；

16、上述處理Sn和Bn的過程會一直持續下去，直到遍曆完所有的Sn和Bn為止。

對於雜湊串連的優缺點及適用情境，有如下總結：

1.雜湊串連不一定會排序，或者說大多數情況下都不需要排序；

2.雜湊串連的驅動表所對應的串連列的可選擇性應儘可能的好，因為這個可選擇性會影響對應Hash Bucket中的記錄數，而Hash Bucket中的記錄數又會直接影響從該Hash Bucket中尋找匹配記錄的效率；如果一個Hash Bucket裡所包含的記錄數過多，則可能會嚴重降低所對應雜湊串連的執行效率，此時典型的表現就是該雜湊串連執行了很長時間都沒有結束，資料庫所在database server上的CPU佔用率很高，但目標SQL所消耗的邏輯讀卻很低，因為此時大部分時間都耗費在了遍曆上述Hash Bucket裡的所有記錄上，而遍曆Hash Bucket裡記錄這個動作是發生在PGA的工作區裡，所以不耗費邏輯讀；

3.雜湊串連只適用於CBO、它也只能用於等值串連條件（即使是雜湊反串連，Oracle實際上也是將其轉換成了等價的等值串連）；

4.雜湊串連很適合於一個小表（結果集）和大表之間的表串連，特別是在小表的串連列的可選擇性非常好的情況下，這時候雜湊串連的執行時間就可以近似看作是和全表掃描那個大表所耗費的時間相當；

5.當兩個表做雜湊串連時，如果這兩個表在施加了目標SQL中指定的謂詞條件（如果有的話）後得到的結果集中資料量較小的那個結果集所對應的Hash Table能夠完全被容納在記憶體中時（PGA的工作區），則此時的雜湊串連的執行效率會非常高。

---整理自網路

 

Oracle 表的串連方式(2)-----HASH JOIN的基本機制2