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

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/