看懂执行计划之表连接

简介

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

解析表连接 SQL 的要素

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

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

表连接类型

内连接（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)来得到结果集的表连接方法

执行步骤简析

用目标 SQL 中指定的谓词分别去访问两张表，对谓词过滤后的表数据分别按照连接列排序生成结果集 1 和结果集 2。
遍历结果集 1，按照连接列的顺序依次匹配结果集 2 中的数据，最后结果集 1 和结果集 2 中所有的匹配结果就是上述排序合并连接的最终结果。

适用场景

通常情况下，排序合并连接的执行效率不如哈希连接，但前者的适用范围广，因为哈希连接通常只用于等值连接，而排序连接还能用于其他连接(例：>,<,>=,<=)。
通常情况下，排序合并连接不适合 OLTP(On-Line Transaction Processing)联机事务处理过程，因为排序相对而言成本较高。

嵌套循环连接(Nested Loops Join)

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

执行步骤

首先 Oracle 优化器会按照一定的规则来决定驱动表和被驱动表。驱动表用于外层循环，被驱动表用于内层循环。
然后分别用目标 SQL 中指定的谓词去筛选数据。
接着遍历驱动表按照连接条件去匹配被驱动表中的数据，直到遍历完驱动表中的所有数据。显然驱动表有多少数据，内层循环就要做多少次。

适用场景

如果驱动表所对应的驱动结果集的记录数较少，同时在被驱动表的连接列上又存在唯一索引(或者在被驱动表的连接列上存在选择性比较好的非唯一性索引)，那么此时使用嵌套循环连接的执行效率就会非常高；但如果驱动表所对应的驱动结果集的记录数很多，即便被驱动表的连接列上存在索引，此时使用嵌套循环连接的执行效率也不会太高。
大表也可以作为嵌套循环连接的驱动表，关键看目标 SQL 中指定谓词条件能否将驱动结果集的数据量降下来。
嵌套循环连接有一个其他连接方法没有的优点：嵌套循环连接可以实现快速响应。

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

哈希连接(Hash Join)

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

执行步骤

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

试用场景

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

笛卡尔积连接(Cross Join)

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

执行步骤

首先以目标 SQL 中指定谓词条件访问表 1，得到结果集 1，记录数 m；
接着以目标 SQL 中指定谓词条件访问表 2，得到结果集 2，记录数 n；
最后对结果集 1 和结果集 2 执行合并操作，因为没有表连接条件，结果集 1 和结果集 2 连接时，结果集 1 中的任意一条记录匹配结果集 2 中的每一条记录，最终得到记录数 m*n 的结果集。