ORACLE 表连接方式

一、多表连接的三种方式

在执行计划中，多表连接查询，能看到三种连接方式

HASH JOIN

SORT  MERGE JOIN

NESTED LOOPS

二、连接说明：

1.Oracle一次只能连接两个表。不管查询中有多少个表，Oracle 在连接中一次仅能操作两张表。

2.当执行多个表的连接时，优化器从一个表开始，将它与另一个表连接；然后将中间结果与下一个表连接，以此类推，直到处理完所有表为止

下面是三种连接在执行计划中的体现

1． HASH JOIN :

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。

 在绝大多数情况下，hash join效率比其他join方式效率更高：

     在Sort-Merge Join(SMJ)，两张表的数据都需要先做排序，然后做merge。因此效率相对最差；

     Nested-Loop Join(NL)效率比SMJ更高。特别是当驱动表的数据量很大（集的势高）时。这样可以并行扫描内表。

     Hash join效率最高，因为只要对两张表扫描一次。


     Hash join一般用于一张小表和一张大表进行join时。Hash join的过程大致如下

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

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

3．  当大表的所有数据都读取完毕，将临时表空间中的数据以其输出。如果小表的数据量足够小（小于hash area size），那所有数据就都在内存中了，可以避免对临时表空间的读写。


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

2．  每读取一条大表的记录，和内存中小表的数据比较，如果符合先做join，而不直接输出，直到整张大表数据读取完毕。如果内存足够，

Join好的数据就保存在内存中。否则，就保存在临时表空间中。

 

步骤：将两个表中较小的一个在内存中构造一个HASH表（对JOIN KEY），扫描另一个表，同样对JOIN KEY进行HASH后探测是否可以JOIN。适用于记录集比较大的情况。需要注意的是：如果HASH表太大，无法一次构造在内存中，则分成若干个partition，写入磁盘的temporary segment，则会多一个写的代价，会降低效率。

cost = (outer access cost * # of hash partitions) + inner access cost

3．SORT MERGE JOIN

通常情况下散列连接的效果都比排序合并连接要好，然而如果行源已经被排过序，在执行排序合并连接时不需要再排序了，这时排序合并连接的性能会优于散列连接。可以使用USE_MERGE(table_name1 table_name2)来强制使用排序合并连接.

        

Sort Merge join 用在没有索引，并且数据已经排序的情况.

排序合并链接内部处理的流程如下。

（1）优化器判断第一个源表是否已经排序，如果已经排序，则到第3步，否则到第2步。

（2）第一个源表排序。

（3）优化器判断第二个源表是否已经排序，如果已经排序，则到第5步，否则到第4步。

（4）第二个源表排序。

（5）已经排过序的两个源表进行合并操作，并生成最终的结果集。

在缺乏数据的选择性或者可用的索引时，或者两个源表都过于庞大（所选的数据超过表记录数的5%）时，排序合并链接将比嵌套循环链接更加高效。

排列合并链接需要比较大的临时内存块，以用于排序，这将导致在临时表空间占用更多的内存和磁盘I/O。

 

步骤：将两个表排序，然后将两个表合并。通常情况下，只有在以下情况发生时，才会使用此种JOIN方式：

1.RBO模式

2.不等价关联(>,<,>=,<=,<>)

3.HASH_JOIN_ENABLED=false

4.数据源已排序

 

3． NESTED LOOP:

嵌套循环中的这两个表通常称为外部表（outer table)和内部表(inner table)。

在嵌套循环连接中,外部表又称为驱动表(driver table)

NEST LOOP JOIN COST = 从第一个表取得数据的成本 + 从第一个表得到结果的基数 Х 对第二个表访问一次的成本

nest loop join  cost = outer access cost + (outer cardinality  *  inner access one time cost  )

所以嵌套循环一般适合于驱动表记录集比较少（<10000）且内表有高效索引访问方式。
使用USE_NL(table_1 table_2)可强制CBO 执行嵌套循环连接。table_1为驱动表，table_2为内表
驱动表确定：驱动表【select rows from table_1 where colx={value} 】一般为根据where条件能得到较小结果集的表，而不一定是整个表记录比较小的表。

 

4.  三种连接工作方式比较： 

         Hash join的工作方式是将一个表（通常是小一点的那个表）做hash运算，将列数据存储到hash列表中，从另一个表中抽取记录，做hash运算，到hash 列表中找到相应的值，做匹配。

        

         Nested loops 工作方式是从一张表中读取数据，访问另一张表（通常是索引）来做匹配，nested loops适用的场合是当一个关联表比较小的时候，效率会更高。

 

         Merge Join 是先将关联表的关联列各自做排序，然后从各自的排序表中抽取数据，到另一个排序表中做匹配，因为merge join需要做更多的排序，所以消耗的资源更多。 通常来讲，能够使用merge join的地方，hash join都可以发挥更好的性能。