日期:2014-05-16  浏览次数:20422 次

深入理解Oracle表(3):三大表连接方式详解之Nested loop join和 Sort merge join
       系数据库技术的精髓就是通过关系表进行规范化的数据存储
       并通过各种表连接技术和各种类型的索引技术来进行信息的检索和处理
       这里Think愿意和大家一起来学习分享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
       
       三大连接方法的大纲先列如下:
       
       nested loop
       从A表抽一条记录,遍历B表查找匹配记录,然后从a表抽下一条,遍历B表。。。
       就是一个二重循环
       
       hash join
       将A表按连接键计算出一个hash表,然后从B表一条条抽取记录,计算hash值,根据hash到A表的hash来匹配符合条件的记录
       
       sort merge join
       将A,B表都排好序,然后做merge,符合条件的选出
       
       接下来分别谈谈各种连接
       
       ㈠ Nested Loop Join
       
       ⑴ 执行原理
       例如:
       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;
       类似一个嵌套循环
       嵌套循环执行时,先是外层循环进入内层循环,并在内层循环终止之后
       接着执行外层循环再由外层循环进入内层循环中,当外层循环全部终止时,程序结束
       
       ⑵ 步骤如下:
       
       ① 确定驱动表
       ② 把inner 表分配给驱动表
       ③ 针对驱动表的每一行,访问被驱动表的所有行
       
       ⑶执行计划大致如下:
       
       NESTED LOOPS
       outer_loop
       inner_loop
       
       优化器模式为FIRST_ROWS时,我们经常会发现有大量的NESTED LOOP
       这时,在返回数据给用户时,我们没有必要缓存任何数据,这是nested loop的一大亮点
       
       
       ⑷ 使用场景
          一般用在连接的表中有索引,并且索引选择性较好(也就是Selectivity接近1)的时候
          也就是驱动表的记录集比较小(<10000)而且inner表需要有有效的访问方法(Index)
          需要注意的是:JOIN的顺序很重要,驱动表的记录集一定要小,返回结果集的响应时间是最快的
          
       ⑸ 和索引的关系
       
          嵌套循环和索引就像一对孪生兄弟,一般需要共同考量与设计
          这从优化器的执行机制可以看出,比如,存在2张表,一个10条记录,一个1000万条记录
          以小表为驱动表,则代价为:10*(通过索引在大表查询一条记录的代价)
          如果1000万的大表没有索引的时候,那么COST的代价可想而知