oracle表连接深入浅出

男娘i 2022-07-12 15:23 174阅读 0赞

author:skate

time:2010-08-20

--------------------

**表的连接**

表的连接是指在一个SQL语句中通过表与表之间的关联，从一个或多个表检索出相关的数据。如果一个SQL语句的关联表超过两个，  
那么连接的顺序如何呢？ORACLE首先连接其中的两个表，产生一个结果集；然后将产生的结果集与下一个表再进行关联；继续这  
个过程，直到所有的表都连接完成；最后产生所需的数据。

ORACLE 从6的版本开始，优化器使用4种不同的表的连接方式：

1 嵌套循环连接（NESTED LOOP JOIN）  
2 群集连接 (CLUSTER JOIN)  
3 排序合并连接（SORT MERGE JOIN）  
4 笛卡尔连接 (CARTESIAN JOIN)

ORACLE 7.3中，新增加了

5 哈希连接(HASH JOIN)。

在ORACLE 8中，新增加了

6 索引连接(INDEX JOIN)。

这六种连接方式都有其各自技术特点，在一定的条件下，可以充分发挥高效的性能。但是也都有其局限性，如果使用不当，不仅不  
能提高效率，反而会严重影响系统的性能。因此，深入地探讨连接方式的内部 运行机制对于性能优化是必要的。

1 嵌套循环连接

嵌套循环连接的内部处理的流程：

1） Oracle 优化器根据基于规则RBO或基于成本CBO的原则，选择两个表中的一个作为驱动表，并指定其为外部表。  
2） Oracle 优化器再将另外一个表指定为内部表。  
3） Oracle从外部表中读取第一行,然后和内部表中的数据逐一进行对比，所有匹配的记录放在结果集中。  
4） Oracle读取外部表中的第二行，再和内部表中的数据逐一进行对比，所有匹配的记录添加到结果集中。  
5） 重复上述步骤，直到外部表中的所有纪录全部处理完。  
6） 最后产生满足要求的结果集。

使用嵌套循环连接是一种从结果集中提取第一批记录最快速的方法。在驱动行源表(就是正在查找的记录，外部表)较小、或者内部行  
源表已连接的列有惟一的索引或高度可选的非惟一索引时, 嵌套循环连接效果是比较理想的。嵌套循环连接比其他连接方法有优势,它  
可以快速地从结果集中提取第一批记录,而不用等待整个结果集完全确定下来。这样,在理想情况下,终端用户就可以通过查询屏幕查看  
第一批记录,而在同时读取其他记录。不管如何定义连接的条件或者模式,任何两个行记录源都可以使用嵌套循环连接,所以嵌套循环连  
接是非常灵活的。

然而,

如果内部行源表(读取的第二张表)已连接的列上不包含索引,或者索引不是高度可选时,嵌套循环连接效率是很低的。  
如果驱动表的记录非常庞大时,其他的连接方法可能更加有效。

可以通过在SQL语句中添加HINTS（use\_nl）,强制ORACLE优化器产生嵌套循环连接的执行计划。

2 群集连接（CLUSTER JOIN）

群集连接实际上是嵌套循环连接的一种特例。如果所连接的两张源表是群集中的表，即两张表属于同一个段（SEGMENT）,  
那么ORACLE能够使用群集连接。处理的过程是：ORACLE从第一张行源表中读取第一行，然后在第二张行源表中使用CLUSTER索  
引查找能够匹配到的纪录；继续上面的步骤处理行源表中的第二行，直到所有的记录全部处理完。

群集连接的效率极高，因为两个参加连接的行源表实际上处于同一个物理块上。但是，群集连接也有其限制，没有群集的两  
个表不可能用群集连接。所以，群集连接实际上很少使用。

3 排序合并连接（SORT MERGE JOIN）

排序合并连接内部处理的流程：  
1) 优化器判断第一个行源表是否已经排序，如果已经排序，则到第3步，否则到第2步。  
2) 第一个源表排序  
3) 优化器判断第二个行源表是否已经排序，如果已经排序，则到第5步，否则到第4步。  
4) 第二个源表排序  
5) 已经排过序的两个源表进行合并操作，并生成最终的结果集。

在缺乏数据的选择性或者可用的索引时,或者两个行源表都过于庞大(所选的数据超过表记录数的5%)时,排序合并连接将比嵌套  
循环连更加高效。排列合并连接需要比较大的临时内存块,以用于排序，这将导致在临时表空间占用更多的内存和磁盘I/O。

可以通过在SQL语句中添加HINTS（use\_merge）,强制ORACLE优化器产生排序合并连接的执行计划。

4 笛卡尔连接 (CARTESIAN JOIN)

笛卡尔连接是指在sql语句中没有写出表连接的条件，优化器把第一个表的每一条记录和第二个表的所有纪录相连接。如果第  
一个表的纪录数为m, 第二个表的纪录数为m,则会产生m\*n条纪录数。

由于笛卡尔连接会导致性能很差的SQL,因此一般也很少用到。

5 哈希连接(Hash Join)

**Hash Join 概述**  
Hash join 算法的一个基本思想就是根据小的row sources(称作build input我们记较小的表为S ，较大的表为B)建立一个可以  
存在于 hash area内存中的hash table ，然后用大的 row sources(称作probe input) 来探测前面所建的hash table。如果  
hash area 内存不够大， hash table就无法完全存放在hash area内存中。针对这种情况，Oracle在连接键利用一个hash函数将  
build input 和 probe input 分割成多个不相连的分区（分别记作 Si 和 Bi ),这个阶段叫做分区阶段；然后各自相应的分区，  
即 Si 和 Bi 再做 Hash join ，这个阶段叫做 join 阶段。

如果在分区后，针对某个分区所建的hash table还是太大的话，oracle就采用nested-loops hash join. 所谓的nested-loops hash join就是对部分 Si 建立 hash table ，然后读取所有的 Bi 与所建的 hash table 做连接，然后再对剩余的 Si 建立 hash table ，再  
将所有的 Bi 与所建的 hash table 做连接，直至所有的 Si 都连接完了。

Hash Join 算法有一个限制，就是它是在假设两张表在连接键上是均匀的，也就是说每个分区拥有差不多的数据。但是实际当中数据都是不  
均匀的，为了很好地解决这个问题， oracle 引进了几种技术，位图向量过滤、角色互换、柱状图，这些术语的具体意义会在后面详细介绍。

**Hash Join 原理**  
我们用一个例子来解释 Hash Join 算法的原理，以及上述所提到的术语。

考虑以下两个数据集。

S=\{1,1,1,3,3,4,4,4,4,5,8,8,8,8,10\}  
B=\{0,0,1,1,1,1,2,2,2,2,2,2,3,8,9,9,9,10,10,11\}

oracle优化器根据统计信息,Hash Join 的第一步就是判定小表（即 build input ）是否能完全存放在 hash area 内存中。如果能完全存放在内存中，则在内存中建立 hash table ，这是最简单的 hash join 。

如果不能全部存放在内存中，则 build input 必须分区。分区的个数叫做 fan-out 。 Fan-out 是由 hash\_area\_size 和 cluster size 来决定的。其中 cluster size 等于 db\_block\_size \* hash\_multiblock\_io\_count ， hash\_multiblock\_io\_count 在 oracle9i 中是隐含参数。这里需要注意的是 fan-out 并不是 build input 的大小 /hash\_ara\_size ，也就是说 oracle 决定的分区大小有可能还是不能完全存放在 hash area 内存中。大的 fan-out 导致许多小的分区，影响性能，而小的 fan-out 导致少数的大的分区，以至于每个分区不能全部存放在内存中，这也影响 hash join 的性能。

Oracle 采用内部一个 hash 函数作用于连接键上，将 S 和 B 分割成多个分区，在这里我们假设这个 hash 函数为求余函数，即 Mod(join\_column\_value,10) 。这样产生十个分区，如下表。

经过这样的分区之后，只需要相应的分区之间做 join 即可（也就是所谓的 partition pairs ），如果有一个分区为 NULL 的话，则相应的分区 join 即可忽略。

在将 S 表读入内存分区时，oracle将记录连接键的唯一值，构建成所谓的位图向量，它需要占 hash area 内存的 5% 左右。在这里即为 \{1,3,4,5,8,10\} 。

当对 B 表进行分区时，将每一个连接键上的值与位图向量相比较，如果不在其中，则将其记录丢弃。在我们这个例子中， B 表中以下数据将被丢弃 \{0,0,2,2,2,2,2,2,9,9,9,9,9\} 。这个过程就是位图向量过滤。

当 S1,B1 做完连接后，接着对 Si,Bi 进行连接，这里 oracle 将比较两个分区，选取小的那个做 build input ，就是动态角色互换，这个动态角色互换发生在除第一对分区以外的分区上面。

**Hash Join 算法**

第 1 步：判定小表是否能够全部存放在 hash area 内存中，如果可以，则做内存 hash join 。如果不行，转第二步。 
第 2 步：决定 fan-out 数。 
   (Number of Partitions) \* C<= Favm \*M 
    其中 C 为 Cluster size ， 
其值为 DB\_BLOCK\_SIZE\*HASH\_MULTIBLOCK\_IO\_COUNT ； Favm 为 hash area 内存可以使用的百分比，一般为 0.8 左右； M 为 Hash\_area\_size 的大小。

第 3 步：读取部分小表 S ，采用内部 hash 函数 ( 这里称为 hash\_fun\_1) ，将连接键值映射至某个分区，同时采用 hash\_fun\_2 函数对连接键值产生另外一个 hash 值，这个 hash 值用于创建 hash table 用，并且与连接键值存放在一起。

第 4 步：对 build input 建立位图向量。  
第 5 步：如果内存中没有空间了，则将分区写至磁盘上。  
第 6 步：读取小表 S 的剩余部分，重复第三步，直至小表 S 全部读完。  
第 7 步：将分区按大小排序，选取几个分区建立 hash table( 这里选取分区的原则是使选取的数据最多 ) 。  
第 8 步：根据前面用 hash\_fun\_2 函数计算好的 hash 值，建立 hash table 。  
第 9 步：读取表 B ，采用位图向量进行位图向量过滤。  
第 10 步：对通过过滤的数据采用 hash\_fun\_1 函数将数据映射到相应的分区中去，并计算 hash\_fun\_2 的 hash 值。  
第 11 步：如果所落的分区在内存中，则将前面通过 hash\_fun\_2 函数计算所得的 hash 值与内存中已存在的 hash table 做连接， 将结果写致磁盘上。 如果所落的分区不在内存中，则将相应的值与表 S 相应的分区放在一起。

第 12 步：继续读取表 B ，重复第 9 步，直至表 B 读取完毕。  
第 13 步：读取相应的 (Si,Bi) 做 hash 连接。在这里会发生动态角色互换。  
第 14 步：如果分区过后，最小的分区也比内存大，则发生 nested- loop hash join 。

**Hash Join 的成本**

1. In-Memory Hash Join  
   
  Cost(HJ)=Read(S)+ build hash table in memory(CPU)+Read(B) + Perform In memory Join(CPU)  
   忽略 cpu 的时间，则  
  Cost(HJ)=Read(S)+Read(B)

2. On-Disk Hash Join  
根据上述的步骤描述，我们可以看出

Cost(HJ)=Cost(HJ1)+Cost(HJ2)

其中 Cost(HJ1) 的成本就是扫描 S,B 表，并将无法放在内存上的部分写回磁盘，对应前面第 2 步至第 12 步。  
Cost(HJ2) 即为做 nested-loop hash join 的成本，对应前面的第 13 步至第 14 步。

其中 Cost(HJ1) 近似等于 Read(S)+Read(B)+Write((S-M)+(B-B\*M/S)) 。

因为在做 nested-loop hash join 时，对每一 chunk 的 build input ，都需要读取整个 probe input ，因此  
Cost(HJ2) 近似等于 Read((S-M)+n\*(B-B\*M/S))

其中 n 是 nested-loop hash join 需要循环的次数。 n=(S/F)/M

一般情况下，如果 n 大于 10 的话， hash join 的性能将大大下降。从 n 的计算公式可以看出， n 与 Fan-out 成反比例，  
提高 fan-out ，可以降低 n 。当 hash\_area\_size 是固定时，可以降低 cluster size 来提高 fan-out 。

从这里我们可以看出，提高 hash\_multiblock\_io\_count 参数的值并不一定提高 hash join 的性能。

当连接的两个表是用等值连接并且表的数据量比较大时，优化器才可能采用哈希连接。哈希连接是基于CBO的。只有在数据库  
初始化参数HASH\_JOIN\_ENABLED设为True,并且为参数PGA\_AGGREGATE\_TARGET设置了一个足够大的值的时候,Oracle才会使用哈  
希边连接。HASH\_AREA\_SIZE是向下兼容的参数,但在Oracle9i之前的版本中应当使用HASH\_AREA\_SIZE。当使用ORDERED提示时,  
FROM子句中的第一张表将用于建立哈希表。

当缺少有用的索引时，哈希连接比嵌套循环连接更加有效。哈希连接也可能比嵌套循环连接更快，因为处理内存中的哈希表  
比检索B\_树索引更加迅速。 Hash Join 适合于小表与大表连接、返回大型结果集的连接。

深入地理解和掌握oracle的表连接对于优化数据库的性能至关重要。由于优化器选择方式的不同，以及统计信息的缺失或统  
计信息的不准确，ORACLE自动选择的表连接方式不一定是最优的。当SQL语句的执行效率很低时，可通过auto trace对执行计  
划进行跟踪和分析。当出现多表连接时，需要仔细分析是否有更佳的连接条件。根据系统的特点，必要时可以在SQL中添加  
HINTS,从而改变SQL的执行计划，从而达到性能优化的目的。

6 索引连接

如果一组已存在的索引包含了查询所需要的所有信息，那么优化器将在索引中有选择地生成一组哈希表。可通过范围或者快  
速全局扫描访问到每一个索引，而选择何种扫描方式取决于WHERE子句中的可有条件。在一张表有大量的列，而您只想访问有  
限的列时，这种方法非常有效。WHERE子句约束条件越多，执行速度越快。因为优化器在评估执行查询的优化路径时，将把约  
束条件作为选项看待。您必须在合适的列（那些满足整个查询的列）上建立索引，这样可以确保优化器将索引连接作为可选  
项之一。这个任务通常牵涉到在没有索引，或者以前没有建立联合索引的列上增加索引。相对于快速全局扫描，连接索引的

优势在于：快速全局扫描只有一个单一索引满足整个查询;索引连接可以有多个索引满足整个查询。

\------end------