Hash Join 与 Nested-Loop Join

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/bbliutao/article/details/7727316

       总所周知,Oracle数据库常用的两种优化器:RBO(rule-based-optimizer)和CBO(cost-based-optimizer)。目前更多地采用CBO(cost-based-optimizer)基于开销的优化器。在CBO方式下,Oracle会根据表及索引的状态信息来选择计划;在RBO方式下,Oracle会根据自己内部设置的一些规则来决定选择计划。
   
   Oracle在表格联集(Table Join)技術上大致可以分為Nested LoopJoin、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 join和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-LoopJoin的效能分析比较,但实际应用场合面对各种不同的情况却并非如此。

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

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

    长时间参与java开发、数据库开发和软件测试,发现在开发中测试的 sql语句运行效率非常好,但是一放到平台上,数据量达到几千万的时候就出现无法响应的情况,那是因为垄断式的Hash Join在作怪,CPU占用率飙升。

    综上:面对绝大部分交互式的系统,建议在从事开发的时候,穿插于代码中的SQL语句用Nested-Loop Join。从事数据库开发、数据处理则建议用Hash Join。(方法:通过在select 后面追加提示即可强制使用指定的join技术)


阅读更多
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页