HASH JOIN ,MERGE JOIN ,NESTED LOOP(R2)

  1 ，排序 - - 合併連線（ Sort Merge Join ， SMJ ）

　　內部連線過程：

　　 1 ） 首先生成 row source1 需要的資料，然後對這些資料按照連線操作關聯列（如 A.col3 ）進行排序。

　　 2 ） 隨後生成 row source2 需要的資料，然後對這些資料按照與 sort source1 對應的連線操作關聯列（如 B.col4 ）進行排序。

　　 3 ） 最後兩邊已排序的行被放在一起執行合併操作，即將 2 個 row source 按照連線條件連線起來

　　下面是連線步驟的圖形表示：

　　               MERGE

　　              /           /

　　       SORT           SORT

　　          |                   |

　　 Row Source 1  Row Source 2

　　如果 row source 已經在連線關聯列上被排序，則該連線操作就不需要再進行 sort 操作，這樣可以大大提高這種連線操作的連線速度，因為排序是個極其費資源的操 作，特別是對於較大的表。預先排序的 row source 包括已經被索引的列（如 a.col3 或 b.col4 上有索引）或 row source 已經在前面的步驟中被排序了。儘管合併兩個 row source 的過程是序列的，但是可以並行訪問這兩個 row source （如並行讀入資料，並行排序）。

　　 SMJ 連線的例子：

　　 SQL> explain plan for

　　 select /*+ ordered */ e.deptno ， d.deptno

　　 from emp e ， dept d

　　 where e.deptno = d.deptno

　　 order by e.deptno ， d.deptno ；

　　 Query Plan

　　 -------------------------------------

　　 SELECT STATEMENT [CHOOSE] Cost=17

　　 MERGE JOIN

　　 SORT JOIN

　　 TABLE ACCESS FULL EMP [ANALYZED]

　　 SORT JOIN

　　 TABLE ACCESS FULL DEPT [ANALYZED]

　　排序是一個費時、費資源的操作，特別對於大表。基於這個原因， SMJ 經常不是一個特別有效的連線方法，但是如果 2 個 row source 都已經預先排序，則這種連線方法的效率也是蠻高的。

　　 2 ，巢狀迴圈（ Nested Loops ， NL ）

　　這個連線方法有驅動表（外部表）的概念。其實，該連線過程就是一個 2 層巢狀迴圈，所以外層迴圈的次數越少越好，這也就是我們為什麼將小表或返回較小 row source 的表作為驅動表（用於外層迴圈）的理論依據。但是這個理論只是一般指導原則，因為遵循這個理論並不能總保證使語句產生的 I/O 次數最少。有時 不遵守這個理論依據，反而會獲得更好的效率。如果使用這種方法，決定使用哪個表作為驅動表很重要。有時如果驅動表選擇不正確，將會導致語句的效能很差、很差。

　　內部連線過程：

　　 Row source1 的 Row 1 —— Probe ->Row source 2

　　 Row source1 的 Row 2 —— Probe ->Row source 2

　　 Row source1 的 Row 3 —— Probe ->Row source 2

　　……。

　　 Row source1 的 Row n —— Probe ->Row source 2

　　從內部連線過程來看，需要用 row source1 中的每一行，去匹配 row source2 中的所有行，所以此時保持 row source1 儘可能的小與高效的訪問 row source2 （一般透過索引實現）是影響這個連線效率的關鍵問題。這只是理論指導原則，目的是使整個連線操作產生最少的物理 I/O 次數，而且如果遵守這 個原則，一般也會使總的物理 I/O 數最少。但是如果不遵從這個指導原則，反而能用更少的物理 I/O 實現連線操作，那儘管違反指導原則吧！因為最少的物理 I/O 次數才是我們應該遵從的真正的指導原則，在後面的具體案例分析中就給出這樣的例子。

　　在上面的連線過程中，我們稱 Row source1 為驅動表或外部表。 Row Source2 被稱為被探查表或內部表。

　　在 NESTED LOOPS 連線中， Oracle 讀取 row source1 中的每一行，然後在 row sourc2 中檢查是否有匹配的行，所有被匹配的行都被放到結果集中，然後處理 row source1 中的下一行。這個過程一直繼續，直到 row source1 中的所有行都被處理。這是從連線操作中可以得到第一個匹配行的最快的方法之一，這種型別的連線可以用在需要快速響應的語句中，以響應速度為 主要目標。

　　如果 driving row source （外部表）比較小，並且在 inner row source （內部表）上有唯一索引，或有高選擇性非唯一索引時，使用這種方法可以得到較好的效率。 NESTED LOOPS 有其它連線方法沒有的的一個優點是：可以先返回已經連線的行，而不必等待所有的連線操作處理完才返回資料，這可以實現快速的響應時間。

　　如果不使用並行操作，最好的驅動表是那些應用了 where 限制條件後，可以返回較少行資料的的表，所以大表也可能稱為驅動表，關鍵看限制條件。對於並行查詢，我們經常選擇大表作為驅動表，因為大表可以充分利用並 行功能。當然，有時對查詢使用並行操作並不一定會比查詢不使用並行操作效率高，因為最後可能每個表只有很少的行符合限制條件，而且還要看你的硬體配置是否 可以支援並行（如是否有多個 CPU ，多個硬碟控制器），所以要具體問題具體對待。

　　 NL 連線的例子：

　　 SQL> explain plan for

　　 select a.dname ， b.sql

　　 from dept a ， emp b

　　 where a.deptno = b.deptno ；

　　 Query Plan

　　 -------------------------

　　 SELECT STATEMENT [CHOOSE] Cost=5

　　 NESTED LOOPS

　　 TABLE ACCESS FULL DEPT [ANALYZED]

　　 TABLE ACCESS FULL EMP [ANALYZED]

　　 3 ，雜湊連線（ Hash Join ， HJ ）

　　這種連線是在 oracle 7.3 以後引入的，從理論上來說比 NL 與 SMJ 更高效，而且只用在 CBO 最佳化器中。

　　較小的 row source 被用來構建 hash table 與 bitmap ，第 2 個 row source 被用來被 hansed ，並與第一個 row source 生成的 hash table 進行匹配，以便進行進一步的連線。 Bitmap 被用來作為一種比較快的查詢方法，來檢查在 hash table 中是否有匹配的行。特別的，當 hash table 比較大而不能全部容納在記憶體中時，這種查詢方法更為有用。這種連線方法也有 NL 連線中所謂的驅動表的概念，被構建為 hash table 與 bitmap 的表為驅動表，當被構建的 hash table 與 bitmap 能被容納在記憶體中時，這種連線方式的效率極高。

　　 HASH 連線的例子：

　　 SQL> explain plan for

　　 select /*+ use_hash （ emp ） */ empno

　　 from emp ， dept

　　 where emp.deptno = dept.deptno ；

　　 Query Plan

　　 ----------------------------

　　 SELECT STATEMENT[CHOOSE] Cost=3

　　 HASH JOIN

　　 TABLE ACCESS FULL DEPT

　　 TABLE ACCESS FULL EMP

　　要使雜湊連線有效，需要設定 HASH_JOIN_ENABLED=TRUE ，預設情況下該引數為 TRUE ，另外，不要忘了還要設定 hash_area_size 引數，以使雜湊連線高效執行，因為雜湊連線會在該引數指定大小的記憶體中執行，過小的引數會使雜湊連線的效能比其他連線方式還 要低。

 

最後，總結一下，在哪種情況下用哪種連線方法比較好：

　　排序 - - 合併連線（ Sort Merge Join ， SMJ ）：

　　 a ） 對於非等值連線，這種連線方式的效率是比較高的。

　　 b ） 如果在關聯的列上都有索引，效果更好。

　　 c ） 對於將 2 個較大的 row source 做連線，該連線方法比 NL 連線要好一些。

　　 d ） 但是如果 sort merge 返回的 row source 過大，則又會導致使用過多的 rowid 在表中查詢資料時，資料庫效能下降，因為過多的 I/O.

　　巢狀迴圈（ Nested Loops ， NL ）：

　　 a ） 如果 driving row source （外部表）比較小，並且在 inner row source （內部表）上有唯一索引，或有高選擇性非唯一索引時，使用這種方法可以得到較好的效率。

　　 b ） NESTED LOOPS 有其它連線方法沒有的的一個優點是：可以先返回已經連線的行，而不必等待所有的連線操作處理完才返回資料，這可以實現快速的響應時間。

　　雜湊連線（ Hash Join ， HJ ）：

　　 a ） 這種方法是在 oracle7 後來引入的，使用了比較先進的連線理論，一般來說，其效率應該好於其它 2 種連線，但是這種連線只能用在 CBO 最佳化器中，而且需要設定合適的 hash_area_size 引數，才能取得較好的效能。

　　 b ） 在 2 個較大的 row source 之間連線時會取得相對較好的效率，在一個 row source 較小時則能取得更好的效率。

　　 c ） 只能用於等值連線中

本文源地址：http://blogold.chinaunix.net/u3/114549/showart_2536704.html



-------------------------------------------------------------------------------------------------------------------
來源：

一、  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。
三、  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表匹配上，先檢視這個點陣圖，已決定是否將沒有匹配的資料寫入磁碟。那些不可能匹配到的資料（即點陣圖上對應的分割槽沒有資料）就不再寫入磁碟。