【Oracle】看懂执行计划系列之表连接

简介

  表连接，顾名思义多个表之间用用连接条件连接在一起，使用表连接的目标SQL其目的就是从多个表获取存储在这些表中不同维度的数据。

解析表连接SQL的要素

1. 目标SQL文本的写法
   SQL文本的写法决定表连接的类型：内连接、外连接、自连接

自连接：同一张表内的字段做连接

3. 表连接顺序
   不管目标SQL中有多少个表做连接，Oracle在实际执行该SQL时都只能先两两 连接，再依次同剩下的表做连接，直到目标SQL中所有的表都已连接完毕。
4. 表连接方法
   排序合并连接、嵌套循环连接、哈希连接和笛卡尔积连接

表连接类型

• 内连接（Join）
  连接结果只包含那些完全满足连接条件的记录
• 外连接
  1. 左外连接（Left Join）
     表连接结果包含左边表的全部行，右边表满足连接条件的查询列正常显示，不满足展示Null
  2. 右外连接（Right Join）
     表连接结果包含右边表的全部行，左边表满足连接条件的查询列正常显示，不满足展示Null
  3. 全外连接（Full Join）
     表连接结果包含两张表的全部记录，满足连接条件的查询列正常显示，不满足连接条件的记录对应另外一张表展示Null

如果使用（+）操作符指定外连接,不满足连接条件时靠近操作符表的连接列以Null值填充,必须使用Where子句指定连接条件,否则会产生笛卡尔积

示例

A表：1，2，3，4，5
B表：2，4，6，8，10

表连接方法

排序合并连接(Sort Merge Join)

两个表在做表连接时用排序操作(Sort)和合并操作(Merge)来得到结果集的表连接方法

执行步骤简析

1. 用目标SQL中指定的谓词分别去访问两张表，对谓词过滤后的表数据分别按照连接列排序生成结果集1和结果集2。

2. 遍历结果集1，按照连接列的顺序依次匹配结果集2中的数据，最后结果集1和结果集2中所有的匹配结果就是上述排序合并连接的最终结果。

适用场景

• 通常情况下，排序合并连接的执行效率不如哈希连接，但前者的适用范围广，因为哈希连接通常只用于等值连接，而排序连接还能用于其他连接(例：>,<,>=,<=)。

• 通常情况下，排序合并连接不适合OLTP(On-Line Transaction Processing)联机事务处理过程，因为排序相对而言成本较高。

嵌套循环连接(Nested Loops Join)

两张表在做表连接时依靠两层嵌套循环(外层循环和内层循环)来得到连接结果集的表连接方法。

执行步骤

1. 首先Oracle优化器会按照一定的规则来决定驱动表和被驱动表。驱动表用于外层循环，被驱动表用于内层循环。

2. 然后分别用目标SQL中指定的谓词去筛选数据。

3. 接着遍历驱动表按照连接条件去匹配被驱动表中的数据，直到遍历完驱动表中的所有数据。显然驱动表有多少数据，内层循环就要做多少次。

适用场景

• 如果驱动表所对应的驱动结果集的记录数较少，同时在被驱动表的连接列上又存在唯一索引(或者在被驱动表的连接列上存在选择性比较好的非唯一性索引)，那么此时使用嵌套循环连接的执行效率就会非常高；但如果驱动表所对应的驱动结果集的记录数很多，即便被驱动表的连接列上存在索引，此时使用嵌套循环连接的执行效率也不会太高。

• 大表也可以作为嵌套循环连接的驱动表，关键看目标SQL中指定谓词条件能否将驱动结果集的数据量降下来。

• 嵌套循环连接有一个其他连接方法没有的优点：嵌套循环连接可以实现快速响应。

在Oracle11g中，Oracle引入了向量I/O(Vector I/O)。在引入向量I/O后，Oracle就可以将原先一批单块读所需要消耗的物理I/O组合起来，然后用一个向量I/O去批量处理他们，这样就实现了在单块读的数据量不降低的情况下减少这些单块读所需要消耗的物理I/O数量，也就提高了嵌套循环连接的执行效率。

哈希连接(Hash Join)

两张表在做表连接时主要依靠哈希运算来得到连接结果集的表连接方法。

执行步骤

1. 两张表在施加了目标SQL指定谓词条件后，结果集数量较少的将被Oracle选为哈希连接的驱动表，反之结果集数据量较多的选做被驱动结果集。
2. 哈希运算会在hash area生成hash bulket，并将若干bulket分成一组，成为一个partition，还会生成一个bitmap的列表，每个bulket在上面占一位
3. 对驱动表的连接列做hash运算，将数据分散到相应partition的bulket中，当运算完成后，如果键值唯一性较高的话，bulket里的数据会比较均匀，也有可能有的桶里面数据会是空的，这样bitmap上对应的标志位就是0，有数据的桶，标志位会是1
4. 再对被驱动表的连接列做hash运算，确定应该到某个partition的某个bulket去探测，探测之前，会看这个bulket的bitmap是否会1，如果为0，表示没数据，这行就直接丢弃掉，如果bitmap为1，则在桶内做精确匹配，判断OK后，返回数据
5. 如果驱动表或被驱动表数据量很大，在构建对应的Hash Table时，会出现PGA的工作区(Work Area)被填满的情况，这时Oracle会把工作区中包含记录数最多的Hash Partition写到磁盘上(TEMP表空间)。
6. 两张表都扫描完毕后，由于两边的数据都按照相同的hash算法做的partition和bulket，现在只要成对的比较两边partition数据即可，并且在比较的时候，oracle也做了优化处理，没有严格的驱动与被驱动关系，他会在partition对中选较小的一个作为驱动来进行，直到磁盘上所有的partition对都比较完成，哈希连接处理结束

试用场景

• 哈希连接只适用于CBO，只能用于等值连接条件(即使是哈希反连接，Oracle实际上也是将其转换成了等价的等值连接)。
• 当两个表作哈希连接时，如果在施加了目标SQL中指定谓词条件后得到的数据量较小的那个结果集所对应的Hash Table能够完全被容纳在内存中(PGA的工作区)，则此时的哈希连接的执行效率会非常高。

笛卡尔积连接(Cross Join)

两个表在做表连接时没有任何连接条件的表连接方法。

执行步骤

1. 首先以目标SQL中指定谓词条件访问表1，得到结果集1，记录数m；

2. 接着以目标SQL中指定谓词条件访问表2，得到结果集2，记录数n；

3. 最后对结果集1和结果集2执行合并操作，因为没有表连接条件，结果集1和结果集2连接时，结果集1中的任意一条记录匹配结果集2中的每一条记录，最终得到记录数m*n的结果集。

笛卡尔积的出现通常是由于目标SQL中漏写了表连接条件，笛卡尔积一般是不好的，有些情况下也可以利用笛卡尔积来减少对目标SQL中大表的全表扫描次数。

---