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。就作業型態來看，HashJoin與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技术）