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效率最高，因为只要对两张表扫描一次。