表与表间的连接方式 Nest loop, Hash join and Sort merge join

Nest loop / Hash join / Sort merge join区别和说明

NESTED LOOP

nested loop就是扫描一个表，每读到一条记录，就根据索引去另一个表里面查找，没有索引一般就不会是nested loops。 对于被连接的数据子集较小的情况，nested loop连接是个较好的选择。

一般在nested loop中， 驱动表满足条件结果集不大的小表，被驱动表的连接字段要有索引，这样就走nested loop。如果驱动表返回记录太多，就不适合nested loops了。如果连接字段没有索引，则适合走hash join，因为不需要索引。 

Note：返回的结果集较小的表适合做驱动表(即外部表，而并非数据记录count(*)量少的表)。我们在看执行计划的时候，离nested loops，hash join 近的表就是驱动表，也就是说执行计划中靠上的表是驱动表。

可用ordered提示来改变CBO默认的驱动表，可用USE_NL(table_name1 table_name2)提示来强制使用nested loop。

HASH JOIN

Hash join一般用于一张小表和一张大表进行连接，优化器扫描小表（或数据源），利用连接键（也就是根据连接字段计算hash 值）在内存中建立hash表，然后扫描大表，每读到一条记录就来探测hash表一次，找出与hash表匹配的行。

以下条件下hash join可能有优势： 两个巨大的表之间的连接；在一个巨大的表和一个小表之间的连接。

Hash join的过程大致如下（下面所说的内存就指sort area）：

1．一张小表被hash在内存中。因为数据量小，所以这张小表的大多数数据已经驻入在内存中，剩下的少量数据被放置在临时表空间中；

2．每读取大表的一条记录，就和小表中内存中的数据进行比较，如果符合，则立即输出数据（也就是说没有读取临时表空间中的小表的数据）。而如果大表的数据与小表中临时表空间的数据相符合，则不直接输出，而是也被存储临时表空间中。

3．当大表的所有数据都读取完毕，将临时表空间中的数据以其输出。      

如果小表的数据量足够小（小于hash area size），那所有数据就都在内存中了，可以避免对临时表空间的读写。      

如果是并行环境下，前面中的第2步就变成了：

2．每读取一条大表的记录，和内存中小表的数据比较，如果符合先做join，而不直接输出，直到整张大表数据读取完毕。如果内存足够，Join好的数据就保存在内存中。否则，就保存在临时表空间中。

使用hash join时，HASH_AREA_SIZE初始化参数必须足够的大，如果是9i，Oracle建议使用SQL工作区自动管理，设置WORKAREA_SIZE_POLICY 为AUTO，然后调整PGA_AGGREGATE_TARGET即可。

可用ordered提示来改变CBO默认的驱动表，可用USE_HASH(table_name1 table_name2)提示来强制使用hash join。也可以通过修改会话或数据库参数"_HASH_JOIN_ENABLED"=FALSE（默认为TRUE）强制禁用hash join。




/*+ leading(b) use_hash(a b) */   --强制b表为驱动表

SORT MERGE JOIN

Sort merge join 两张表的数据都需要先做排序，然后做合并，操作通常分三步：对连接的每个表做table access full；对table access full的结果进行排序；进行merge join对排序结果进行合并。

sort merge join性能开销几乎都在前两步。一般是在没有索引的情况下，9i开始已经很少出现了，因为其排序成本高，大多为hash join替代了。通常情况下hash join的效果都比sort merge join要好，然而如果行源已经被排过序，在执行sort merge join时不需要再排序了，这时sort merge join的性能会优于hash join。在全表扫描比索引范围扫描再通过rowid进行表访问更可取的情况下，sort merge join会比nested loops性能更佳。

在缺乏数据的选择性或者可用的索引时,或者两个源表都过于庞大(所选的数据超过表记录数的5%)时, 排序合并连接将比嵌套循环连接更加高效。排列合并连接需要比较大的临时内存块,以用于排序，这将导致在临时表空间占用更多的内存和磁盘I/O。

基于RBO的，可用USE_MERGE(table_name1 table_name2)提示强制使用sort merge join。

Nest loop / Hash join / Sort merge join使用比较：

NESTED LOOP：适合外表结果集较小，内表有强选择性的索引
HASH JOIN：无适当的索引可用，驱动表的结果集较大，内表结果集记录数远大于驱动表
SORT MERGE JOIN：适合无合适的索引可用，内外表结果集大小接近的情况