深入了解Oracle表(3)：三大表连接方式详解之Nested loop join和 Sort merge join
日期：2014-05-16 浏览次数：20452 次
深入理解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的代价可想而知
关系数据库技术的精髓就是通过关系表进行规范化的数据存储
并通过各种表连接技术和各种类型的索引技术来进行信息的检索和处理
这里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的代价可想而知
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
