深入理解Oracle—三大表连接方式详解之Nested loop join和Sort merge join

        关系数据库技术的精髓就是通过关系表进行规范化的数据存储，并通过各种表连接技术和各种类型的索引技术来进行信息的检索和处理，下面大家一起来学习分享Oracle的三大表连接技术。

        在早期版本，Oracle提供的是nested-loop join，两表连接就相当于二重循环，假定两表分别有m行和n行，如果内循环是全表扫描，时间复杂度就是O(m*n)；如果内循环是索引扫描，时间复杂度就是O(m*㏒n)；而hash join的时间复杂度是O(m*n)。因此10g后，hash join成为缺省的连接方法。

        对于三种连接，我们都可以使用hint来强制让优化器走：use_hash,use_nl,use_merge

一.三种连接概述

1.nested loop

        从A表抽一条记录，遍历B表查找匹配记录，然后从a表抽下一条，遍历B表，就是一个二重循环。

2.hash join

        将A表按连接键计算出一个hash表，然后从B表一条条抽取记录，计算hash值，根据hash到A表的hash来匹配符合条件的记录。

3.sort merge join

        将A，B表都排好序，然后做merge，符合条件的选出。

 

二各种连接详解

1.Nested Loop Join

a.执行原理

        例如：

 

select t1.*,t2.* from t1,t2 where t1.col1=t2.col2;

 

        访问机制如下：

 

for i in (select * from t1) loop
  for j in (select * from t2 where col2=i.col1) loop
  display results;
  end loop;
  end loop;

 

        类似一个嵌套循环，嵌套循环执行时，先是外层循环进入内层循环，并在内层循环终止之后，接着执行外层循环再由外层循环进入内层循环中，当外层循环全部终止时，程序结束。

b.步骤如下

        ① 确定驱动表

        ② 把inner 表分配给驱动表

        ③ 针对驱动表的每一行，访问被驱动表的所有行

c.执行计划大致如下

NESTED LOOPS
outer_loop
inner_loop

        优化器模式为FIRST_ROWS时，我们经常会发现有大量的NESTED LOOP，这时，在返回数据给用户时，我们没有必要缓存任何数据，这是nested loop的一大亮点。

d.使用场景

        一般用在连接的表中有索引，并且索引选择性较好（也就是Selectivity接近1）的时候，也就是驱动表的记录集比较小（<10000）而且inner表需要有有效的访问方法（Index），需要注意的是：JOIN的顺序很重要，驱动表的记录集一定要小，返回结果集的响应时间是最快的。

e.和索引的关系

        嵌套循环和索引就像一对孪生兄弟，一般需要共同考量与设计，这从优化器的执行机制可以看出，比如，存在2张表，一个10条记录，一个1000万条记录，以小表为驱动表，则代价为：10*(通过索引在大表查询一条记录的代价)，如果1000万的大表没有索引的时候，那么COST的代价可想而知。

        因此，在多表连接时，注意被驱动表的连接字段是否需要创建索引，或者连接字段与该表的其他约束条件字段上是否需要创建复合索引。

 

2.Sort Merge Join

a.执行原理

        例如：

select t1.*,t2.* from t1,t2 where t1.id=t2.id;

        访问机制如下：

        访问t1，并order by t1_1.id，这里的id代表连接字段；

        访问t2，并order by t2_1.id join t1_1.id = t2_1.id，依次交替比对归并，但无所谓驱动。

b.使用场景

        虽说，hash join就是用来替代Sort Merge Join的，但如果你的服务器的CPU资源和MEM资源都很紧张的时候，建议用SORT MERGE JOIN，因为hash join比sort merge join需要的资源更多，特别是cpu。

        10g sql tuning 文档上写道：

        On the other hand, sort-merge joins can perform better than hash joins if both of the following conditions are met：

        The row sources are already sorted. 

        A sort operation does not have to be done.

        所以，Sort Merge Join大概就用在没有索引，并且数据已经排序的情况。

 

文章来源：http://www.2cto.com/database/201301/186885.html