表连接三剑客(嵌套循环连接,哈希连接,排序合并连接)

1.表连接的定义:

例子1:有一个特别的舞会,男孩子集中在一个房间,女孩子集中在另外一个房间,舞池设置在两个房间中间.

开始跳舞时,从男孩子中选出一个à然后进入女孩子所在房间à选择出高度合适的女孩!这种方式成为à NESTED LOOPS JOIN(嵌套循环连接)

 

例子2:男孩子在房间里面先按身高进行排序à女孩子也在房间按照身高进行排序à男女双方按照排列好的顺序依次出来到舞池中间跳舞

这种方式称为àHASH连接(由PGA中的HASH_AREA_SIZE参数来控制)或者MERGE SORT JOIN(排序合并连接)(由PGA中的SORT_AREA_SIZE参数控制)

 

2.表连接的特点

(1)嵌套循环连接:驱动表返回多少条记录,被驱动表就反问多少次!

其中例子:

Select/*+ leading(t1) use_nl(t2)*/ from t1,t2 where t1.id = t2.id;

Use_nl表示强制使用嵌套循环连接的方式,leading(t1)表示强制先访问t1表,也就是t1表作为驱动表。

 

不会产生排序操作。支持所有的连接条件,没有任何限制。

 

嵌套循环连接适用场景：

A.      两表关联返回的记录不多,最佳情况是驱动表结果集仅返回1条或者少量几条记录,而被驱动表仅匹配到1条或少量几条记录,这种情况即便T1表和T2表的记录奇大无比,也是非常迅速  ？？？？？（不明白，不应该是遍历两个表吗？？怎么会很快？？）

B.      遇到一些不等值查询导致哈希连接和排序合并连接被限制使用。

 

优化思路：

驱动表的限制条件所在列有索引,被驱动表的连接条件的列有索引。

例如：select * from t1,t2 where t1.id = t2.t1_id and t1.n = 19;

此时如果t1表在n字段(限制条件)有索引,t2表在t1_id字段(连接条件)有索引的话，会高效。

解释：驱动表的限制条件建了索引,会快速的返回1条或几条记录,然后再根据连接条件传递给被驱动表,而被驱动表此时在该字段建有索引就会快速的返回记录。

 

(2)HASH连接：在HASH连接中,驱动表和被驱动表都只会访问0次或者1次。

Select /*+ leading(t1) use_hash(t2)*/* from t1,t2 where t1.id=t2.id;

 

哈希连接并不排序，但是需要消耗内存用于建立HASH表。在获取字段中根据业务需求尽量少获取字段。

 

哈希连接不支持不等值连接<>,不支持>和不支持<的连接方式,也不支持like的连接方式。

 

优化思路：

A.      在限制条件列如有适当的索引可以提升性能

B.      增大HASH_AREA_SIZE,一般在内存自动管理中，增大PGA的大小即可。

 

(3)排序合并连接：和HASH连接一样，只访问0次或者1次。但是没有驱动和被驱动表的概念。

Select /*+ ordered use_merge(t2)*/* from t1,t2 where t1.id= t2.id;

 

排序合并连接需要排序，会消耗内存。在获取字段中根据业务需求尽量少获取字段。

 

排序合并连接不支持<>,like的连接条件。

 

嵌套循环连接和哈希连接有驱动顺序,驱动表的顺序不同将影响表连接的性能,而排序合并连接没有驱动的概念,无论哪张表在前都无妨。

 

优化思路：

在连接条件列建立索引,以消除一张表的排序,提升效率。(但是2张表同时建立索引也只会消除一个表的排序)

转载自：http://blog.itpub.net/25269462/viewspace-764243/