Hash Join, Nested Loop, Sort-Merge Join

總所周知，Oracle資料庫常用的兩種優化器：RBO（rule-based-optimizer）和CBO(cost-based-optimizer)。目前更多地采用CBO(cost-based-optimizer)基于開銷的優化器。在CBO方式下，Oracle會根據表及索引的狀态資訊來選擇計劃；在RBO方式下，Oracle會根據自己内部設定的一些規則來決定選擇計劃。

    Oracle在表格聯集(Table Join)技術上大緻可以分為Nested Loop Join、Hash Join、 Merge Join、Semi Join和Anti Join等5種。其中Semi和Anti Join是被運用在子查詢(subquery)，另外三種聯集方式則被廣泛運用在一般的表格聯集上。

    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。

    下面将對這3種JOIN進行比較分析，在一般情況下，hash join效率比其他join方式效率更高：

——在Sort-Merge Join(SMJ)，兩張表的資料都需要先做排序，然後做merge。是以效率相對最差

    （在TOAD的效能分析器中，倘若語句存在ORDER BY，就會看見他了）。

——Nested-Loop Join(NL)效率比SMJ更高。特别是當驅動表的資料量很大（集的勢高）時。這樣可以并行掃描内表。

——Hash join效率最高，因為隻要對兩張表掃描一次。

    上面就一般情況做出的Hash Join與 Nested-Loop Join的效能分析比較，但實際應用場合面對各種不同的情況卻并非如此。

    縱觀時下許多 OLTP(OnLine Transaction Process) 特性的應用系統 ( 例如 ERP 、 MES 、 PLM... 等 ) ，經常存在有回應時間不如預期的現象。這些程式在經過 SQL 指令追蹤 (trace) 下，發現一個共同的特性，就是 Oracle 優化器多數是以 Hash Join 的方式來做表格聯集，但是在透過 SQL 訓示 (Hint) 強迫將存取方式改變為 Nested Loop 後，速度上馬上有著明顯的改善。如此一來，不免讓大家懷疑難道是 Oracle 成本優化器做出了誤判，還是優化器本身的 bug 所造成的結果？其實不然，導緻這種現象的發生最主要的原因是 Oracle 成本優化器根本無從得知 AP 的特性與資料實際的儲存位置所緻。

    一般以執行效能的高低來做比較， Hash Join 普遍是優於 Merge Join 。但是在符合查詢結果筆數 (cardinality) 較少的前提下， Nested Loop Joint 又往往是優於 Hash Join 。就作業型態來看， Hash Join與 Merge Join 是屬於壟斷式作業 (blocking operation) 模式， Nested Loop 則歸屬非壟斷式作業 (non-blocking operation) 模式。是以對於強調回應時間 (response time) 的系統而言，非壟斷式的作業模式比較符合其作業需求。相對於注重產能或單位時間產量 (throughput) 的系統來說，壟斷式的作業模式則會比較適合。

      長時間參與java開發、資料庫開發和軟體測試，發現在開發中測試的 sql語句運作效率非常好，但是一放到平台上，資料量達到幾千萬的時候就出現無法響應的情況，那是因為壟斷式的Hash Join在作怪，CPU占用率飙升。

      綜上：面對絕大部分互動式的系統，建議在從事開發的時候，穿插于代碼中的SQL語句用 Nested-Loop Join。 從事資料庫開發、資料處理則建議用Hash Join。（方法：通過在select 後面追加提示即可強制使用指定的join技術）