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

朱雀 2021-04-06 13:17 513阅读 0赞

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