Oracle的表连接

发表于 | 分类于 ， |
| 字数统计: 4,566

表连接

当优化器解析含有表连接的目标SQL时，它会根据目标SQL的SQL文本的写法来决定表连接的类型外，还必须决定如下的3个部分才能最终决定执行计划。

  • 表连接顺序
    不管目标SQL中有多少个表做链接，oracle在执行该SQL时只能两两做连接，然后将中间结果与下一个表连接，直到目标SQL中的所有表连接完毕。这里有两层含义：一是指俩个表做连接时，优化器要决定这两个表谁是驱动表，谁是被驱动表。二是优化器要决定哪两张表要先做连接，哪个表要随后最连接，哪个表最后做连接。

  • 表连接的方法
    Oracle数据库中表连接的方法有：排序合并连接、嵌套循环连接、哈希连接和笛卡尔连接。所以优化器要选择两张表要以哪种方式做连接。

  • 访问单表的方法
    优化器在决定最终执行计划时，除了考虑表连接顺序以及表连接方法外还要决定以索引方式访问具体表数据还是以全表扫描方式访问表数据，即优化器还要决定访问单表的方法。

表连接类型

Oracle数据库中表连接分为内连接和外链接两种类型。

  1. 内连接（inner join）
    内连接是指表连接的连接结果只包含那些完全满足连接条件的记录。对于SQL而言，只要其where条件中没有定义那些外连接关键字（如left outer join、right outer join、full outer join）外的所有连接类型定义为内连接。

  2. 外连接（outer join）
    外连接是对内连接的一种扩展，它是指表连接的结果除了包括那些满足条件的连接结果外还包含驱动表中不满足连接条件的结果。标准SQL的外连接分为左连接（left outer join）、右连接（righr outer join）、全连接（full outer join）三种。全连接可以近似看成是先做左连接union再做右连接，这里所说的近似是因为oracle实际的处理并不是这样做的，因为union要对结果集进行排序，而全连接并不需要排序。

对于外连接而言，除了表连接的条件之外的额外条件在目标SQL的SQL文本中所处的位置可能会影响该SQL的执行结果，而内连接没有限制。

表连接方法

优化器在解析含有表连接的目标SQL时，当根据目标SQL的SQL文本决定了表的连接类型后，接下来还要决定表的连接方法。Oracle中两表的连接方法有“排序合并”，“嵌套循环”，“哈希连接”，“笛卡尔连接”四种。

1.嵌套循环连接（Nested Loops Join）

嵌套循环连接是两个表在做连接时采用两层嵌套循环（外层循环和内层循环）的方式来得到表连接结果的方法。

嵌套循环实现机制(伪代码)：

1
2
3
4
5
6
7
8
9
10
11
12
13
For r1 in (select rows from table_1 where colx={value})

	loop

        for r2 in (select rows from table_2 that match current row from table_1)

            loop

                output values from current row of table_1 and current row of table_2;

            end loop;

   End loop;

这段代码由两个循环构成。 嵌套循环中的这两个表通常称为外部表（outer table)和内部表(inner table)。 在嵌套循环连接中,外部表又称为驱动表(driver table)。伪代码中：table_1为驱动表，table_2为内表。

嵌套循环的执行顺序如下（假设连接表为t1和t2）：

  1. 首先，优化器会根据一定的规则来决定t1和t2谁是驱动表、谁是被驱动表，驱动表做外层循环，被驱动表做内层循环，这里假设t1是驱动表，t2是被驱动表。
  2. 接着以目标SQL中的谓语条件去访问驱动表t1，所得到的结果集记为结果集1.
  3. 然后遍历驱动结果集1及遍历被驱动表2，即取出结果集1中的第一条记录，然后按照连接条件去遍历t2，查看是否有匹配记录；接着取出结果集1的第二条记录，按照同样的方法去遍历表t2，查看匹配记录，直到结果集1中所有的记录全部遍历完成为止。

嵌套循环连接的适用场景总结入下：

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

  2. 只要驱动结果集的记录较少，那么就具备了做嵌套循环的前提条件，而驱动表所对应的驱动结果集是在对驱动表应用了目标SQL之后所得到的结果集，所以大表也会可以作为驱动表的。

  3. 嵌套循环可以实现快速响应。

嵌套循环在访问被驱动表时，如果被驱动表有索引，将会采用单块读的方式访问索引，同时，如果返回结果集列不在索引中取得，嵌套循环连接也要采用单块读的方式回表。Oracle 11g中，oracle引入了向量I/O（vector i/o），oracle就可以将原来一批单块读所消耗的I/O组合起来，然后用一个向量I/O去批量处理它们，使用这种方法达到单块读的数量不下降的情况下减少这些单块读所需要耗费的物理I/O数量，也就提高了嵌套循环的效率。体现在执行计划上，你会发现在oracle 11g中本来一次嵌套循环可以处理完毕的SQL，但在执行计划中嵌套循环连接的数量由原来的1个变为现在的2个。

2.hash连接（Hash Join）

Hash连接是指两表在做连接时主要依靠哈希算法来取得结果集的方法。Oracle 7.3中引入了hash连接，在oracle 10g版本中，优化器是否启用哈希连接取决于隐藏_HASH_JOIN_ENABLED，而在oracle 10g前，优化器是否引用hash连接取决于参数HASH_JOIN_ENABLED。Hash连接的执行顺序如下（假设连接表为t1和t2）：

  1. 首先，oracle会根据参数hash_area_size、db_block_size、_hash_multiblock_io_count的值来决定hash partition的数量（hash partition是一种逻辑上的概念，它实际上是一组hash bucket的集合，所有的hash partition的集合就成为hash table。）。
  2. 表t1和t2施加了目标SQL的谓语条件后，得到的结果中数量较少的那个结果集会被oracle选为哈希连接的驱动结果集，这里假设t1结果集为驱动结果集，记为s，t2结果集较多，记为b。
  3. 接着oracle会遍历s，读取s中的每一条记录，并对每一条记录按照t1中的连接列做哈希运算，这个哈希运算会使用2个hash内置函数，这两个hash内置函数同时对连接列计算哈希值，我们把2个函数分别记为hash_func_1和hash_func_2，他们所计算的值分别为hash_value_1和hash_value_2。
  4. 然后oracle会按照hash_value_1的值把相应的s中对应的记录存储在不同的hash partition的不同hash bucket里，同时跟该记录记录在一起的还有该hash_func_2计算出来的hash_value_2。注意，存储在hash bucket里的并不是完整的行记录，只需要存储于目标SQL相关的查询列和连接列足够，我们把s对应的hash partition记为si。
  5. 在构建si的同时，oracle会构建一个bit map索引，这个索引用来记录每个hash bucket是否有记录。
  6. 如果s的结果集很大，就有可能出现pga被填满的情况，此时oracle会将工作区中记录最多的hash partition写到磁盘上，接着oracle会继续构建hash table，在继续构建的过程中，如果工作区内存满了，oracle会重复上述工作。如果构建的hash partition已经被写会磁盘，则此时oracle回去更新磁盘上的hash partition，即把hash_value_2直接加到这个已经位于磁盘上的hash partition对应的hash bucket中。极端请况可能出现某个hash partition的部分记录还在内存中，该hash partition的剩余部分已经被写道磁盘上。
  7. 上述构建s所对应的hash table的过程会一直持续下去，知道遍历完s中的所有记录为止。
  8. oralce会对所有si按照他们所包含的记录数排序，然后依次将排序好的数据尽可能存放到内存中，如果内存实在放不下，那些数据量交大的hash partition还是会被写到磁盘上。
  9. oracle至此已经处理好s，接着开始处理b。
  10. oracle会遍历b，读取b中的每一条记录，并按照t2的连接列做哈希运算，这个过程和步骤3一模一样。
  11. 上述过程会一直持续下去，直到遍历完所有数据为止。
  12. 至此oracle处理完所有内存中数据，接着处理硬盘上的数据。
  13. 因为构建si和bj时用了同样的hash函数，所以oracle处理磁盘上的数据时可以放心的匹配。即只有对应的hash partition number值相同的si和bj才可能会产生满足条件的记录。
  14. 对于每一个sn和bn，他们中结果集较少的会被当做驱动结果集。继续匹配。
  15. 步骤14中存在匹配记录，则该匹配记录也会作为满足目标SQL的连接条件的记录返回。
  16. 上述过程会一直持续下去，直到遍历完所有sn和bn为止。

hash连接的适用场景总结入下：

  1. 哈希连接不一定需要排序，大部分情况下。
  2. 哈希连接的驱动表所对应的选择列可选择性应尽可能好，因为这个可选择性会影响到hash bucket的记录数，而hash bucket的记录数又会直接影响从该hash backet中查找匹配记录数的效率。不好的体现在于哈希连接执行了很长时间都没有结束，数据库所在的数据库服务器cpu占用率很高，但是目标SQL所消耗的逻辑读很低，因为此时大部分时间都浪费在了遍历上述hash bucket的记录上，而遍历hash bucket的记录发生在pga，所以不消耗逻辑读。
  3. 哈希连接只适用于CBO，同时哈希连接也只适用于等值连接。
  4. 哈希连接适合于小表和大表做表连接且连接结果集记录数较多的情形，特别是在小表的连接列可选择性特别好的情况下，这时hash连接的执行时间近似于对大表的执行时间。
  5. 当两表做hash连接时，如果在施加了目标SQL指定的谓语条件后所得到的数量较小的那个结果集所对应的hash table能够完全被容纳在内存中（pga），此时hash连接执行效率会非常高。

3.排序合并连接（Sort Merge Join）

排序合并连接是一种两表做表连接时用排序操作（sort）和合并操作（merge）来得到表连接结果的方法。排序合并的执行顺序如下（假设连接表为t1和t2）：

  1. 首先以目标SQL中的谓语条件去访问表t1，然后对结果按连接列进行排序，排好序的结果记为结果集1。

  2. 然后以目标SQL中的谓语条件去访问表t2，然后对结果按连接列进行排序，排好序的结果记为结果集2。

  3. 然后对结果集1和结果集2进行合并操作，从中取匹配记录作为排序合并的最终结果。

排序合并连接的适用场景总结入下：

  1. 通常情况下，由于排序成本高，排序合并连接的执行效率不如哈希连接，但是前者使用范围广，因为hash连接只适用于等值连接，而排序合并还适用于其他连接（比如<、>、<=、>=）。

  2. 如果行源已经被排过序，在执行sort merge join时不需要再排序，这时sort merge join的性能会优于hash join。

  3. 当全表扫描比“索引范围扫描后再通过rowid进行表访问”更可取的情况下，sort merge join会比nested loops性能更佳。

  4. 通常情况下，排序合并不适合OLTP类型连接，本质是排序对于OLTP类型来说成本是昂贵的。但是，如果能避免排序，也是可以适合于OLTP系统的。

4.笛卡尔连接（cross join）

笛卡尔连接又称笛卡尔积（cartesian product），它是一种两表在做连接时没有任何连接条件的表连接方法。笛卡尔积是一种特殊的合并连接，这里的合并连接和排序合并类似，只不过笛卡尔连接不需要排序，并且执行合并时并没有连接条件。的执行顺序如下（假设连接表为t1和t2）：

  1. 首先以目标SQL中制定的谓语条件访问表t1，此时所得到的结果集记为结果集1，结果集1的记录数为m。

  2. 其次以目标SQL中制定的谓语条件访问表t2，此时所得到的结果集记为结果集2，结果集1的记录数为n。

  3. 最后结果集1和结果集2执行合并操作。笛卡尔积连接结果记录数为m*n。

笛卡尔连接总结如下：

  1. 笛卡尔连接出现一般都是目标SQL中漏写了连接条件，所以笛卡尔连接一般都是不好的，除非刻意这样做（比如有时利用笛卡尔积来减少对目标SQL中大表的全表扫描次数）。

  2. 有时候笛卡尔连接的出现时因为目标SQL中使用了ordered hint，同时该SQL中文本委会相邻的两个表又没有直接的连接条件。

5.反连接（anti join）

oracle中没有专门的关键词定义反连接，形如“t1.x anti= t2.y”来表示反连接，其中t1为驱动表，t2是被驱动表，反连接条件为t1.x = t2.y。这里“t1.x anti= t2.y”的含义是只要表t2中由满足条件的“t1.x = t2.y”的记录存在，则t1表中满足条件的“t1.x = t2.y”的记录就会被丢弃，最后返回的记录就是t1表中那些不满足条件“t1.x = t2.y”的记录。目标SQL中那些外部where条件为not exists、not in或<>all的子查询在做子查询展开时常常会被转换为对应的反连接。（即返回在t2中满足条件但是不在t1中满足条件的记录。）

例：t1和t2在在各自的连接列col2上没有null值情况下：

select * from t1 where col2 not in (select col2 from t2);

或

select * from t1 where col2<>all(select col2 from t2);

或

select * from t1 where not exists (select 1 from t2 where col2=t1.col2);

对于连接表有null值得情况说明如下：

  1. t1和t2表在各自的连接列col2上一但有null值，以上例子的执行结果将不完全等价。

  2. not in和<>all对null值敏感，这意味着not in和<>all后面的子查询或常量一但出现null值，则整个sql的执行结果就是null。

  3. not exists对null值不敏感，这意味着not exists后面值出现null对结果不会有什么影响。

oracle的处理办法：为了解决not in和<>all对null值敏感的问题，oracle推出了改良的反连接，这种反连接能够处理null值，oracle称其为null-aware anti jion，对应执行计划“….. anti na”,其中na就是null aware的缩写。在oracle 11gR2中，oralce是否启用null-aware anti join受隐藏_optimizer_null_aware_antijoin控制，其默认值为true。

6.半连接（semi join）

oracle中没有专门的关键词定义半连接，形如“t1.x semi= t2.y”来表示半连接，其中t1为驱动表，t2是被驱动表，反连接条件为t1.x = t2.y。这里“t1.x anti= t2.y”的含义是只要表t2中找到一条满足的“t1.x = t2.y”的记录存在，则马上停止搜索t2，并直接返回t1表中满足条件的“t1.x = t2.y”的记录，最后返回的记录就是t1表中那些满足条件“t1.x = t2.y”的记录。也就是说，t2中满足条件t1.x = t2.y的记录有多条，t1表也只会返回一条记录。所以半连接和普通的内连接不同，半连接会去重。目标SQL中那些外部where条件为exists、in或<>any的子查询在做子查询展开时常常会被转换为对应的半连接。（即返回在t2中满足条件并且在t1中满足条件的记录。）

例：

select * from t1 where col2 in (select col2 from t2);

或

select * from t1 where col2<>any(select col2 from t2);

或

select * from t1 where exists (select 1 from t2 where col2=t1.col2);

7.星型连接（star join）

星型连接通常应用于数据仓库类型应用，他是一个单个事实表外键和多个维度表主键之间的连接，维度表通过连接条件和事实表连接，维度表之间一般没有直接连接关系。一般事实表的外键上还会存在bit map索引。

------ 本文完 ------