Nested loops、Hash join、Sort merge join(三种连接类型原理、使用要点)

  1. 云栖社区>
  2. 博客>
  3. 正文

Nested loops、Hash join、Sort merge join(三种连接类型原理、使用要点)

技术小美 2017-11-12 15:15:00 浏览524
展开阅读全文

nested loop 嵌套循环(原理):
oracle从较小结果集(驱动表、也可以被称为outer)中读取一行,然后和较大结果集(被侦查表,也可以叫做inner)中的所有数据逐条进行比较(也是等值连接,也可以是非等值连接),如果符合规则,就放在结果集中,然后取驱动表的下一条数据继续循环,直到结束。
简单的理解就是:从A表抽一条记录,遍历B表查找匹配记录,然后从a表抽下一条,遍历B表,就是一个二重循环。


使用要点:
1.驱动表的记录集比较小,一般来说小于驱动表结果集的10% 
2.inner表需要有有效的访问方法(Index),选择性要高。


hash join 哈希连接(原理): 
表T1和T2在施加了目标SQL中指定的谓词条件(如果有的话)后得到的结果集中数据量较小的那个结果集(S)会被Oracle选为哈希连接的驱动结果集,T2所对应的结果集的数据量相对较大,我们记为B;S是驱动结果集,B是被驱动结果集;接着Oracle会遍历S,读取S中的每一条记录,并对S中的每一条记录按照该记录在表T1中的连接列做哈希运算,直到遍历完S中的所有记录为止;Oracle会遍历B,读取B中的每一条记录,并对B中的每一条记录按照该记录在表T2中的连接列做哈希运算,最后hash值匹配hash值,返回结果。
简单的理解:将A表按连接键计算出一个hash表,然后从B表一条条抽取记录,计算hash值,根据hash到A表的hash来匹配符合条件的记录。


使用要点:
1. hash join在 oltp 环境下一般没什么优化的地方,在 olap环境中可以并行优化 hash join 
2. 返回大量结果集(几w+)适合走hash join
3. hash join选择小表作为驱动表,注意这里的小表不是指表的行数,而是指的 行数*列的宽度,可以简单的理解为 segment size。
4. hash join会使用到pga中的WORK AREA , 如果等待事件中有on-disk hash join( direct path read/write temp ),可以加大pga size。
5.hash join不适合驱动表表关联字段分布不均匀的情况。

(可以通过10046跟踪,然后查看 Maximum number of rows in a bucket 是否成千上万< 假象执行计划没有问题,但一个超大bucket,将hash join效率极度拉低,消耗大量cpu time   >,一般个数在个位数性能最好,如果一个hash bucket的rows过多,可以改写sql,改变hash table 的列值  )      10046跟踪显示的hash buckets 信息>

### Hash table overall statistics ###
Total buckets: 16384 Empty buckets: 9306 Non-empty buckets: 7078
Total number of rows: 9232
Maximum number of rows in a bucket: 5
Average number of rows in non-empty buckets: 1.304323


sort merge join 排序合并连接(原理):
先排序操作(Sort),再合并操作(Merge)。
简单的理解:将A,B表都排好序,然后做merge,符合条件的选出。

使用要点:
1. 排序合并连接的表无驱动顺序。
2. 排序合并连接不适用于的连接条件是:不等于<>,like,其中大于>,小于<,大于等于>=,小于等于<=,是可以适用于排序合并连接
3. 排序合并连接的表需要排序,用到SORT_AREA_SIZE。
4. 驱动表和被驱动表都是最多只被访问一次。

 

HINT:

操作           Nest Loop       Hash Join             Sort Mereg

Join           USE_NL         USE_HASH            USE_MERGE

Anti Join    NL_AJ            HASH_AJ               MERGE_AJ

Semi          NL_SJ            HASH_SJ              MERGE_SJ


本文转自 张冲andy 博客园博客,原文链接:  http://www.cnblogs.com/andy6/p/6780574.html ,如需转载请自行联系原作者


网友评论

登录后评论
0/500
评论
技术小美
+ 关注