oracle 表连接类型 join type

Join 是一种试图将两个表结合在一起的谓词,一次只能连接 2 个表,表连接也可以被称为表关联。Join 过程的各个步骤经常是串行操作,即使相关的 row source 可以被并行访问,但是在将表中符合限制条件的数据读入到内存形成 row source 后,join 的其它步骤一般是串行的。

row source (表)之间的连接顺序对于查询的效率有非常大的影响。通过首先存取特定的表,即将该表作为驱动表,这样可以先应用某些限制条件,从而得到一个较小的 row source,使连接的效率较高,这也就是我们常说的要先执行限制条件的原因。一般是在将表读入内存时,应用 where 子句中对该表的限制条件。

根据 2 个 row source 的连接条件的中操作符的不同,可以将连接分为等值连接(如 WHERE A.COL1 = B.COL2)、非等值连接(WHERE A.COL1> B.COL2)、外连接(WHERE A.COL1= B.COL2+))。

NESTED LOOP JOIN

这个连接方法有驱动表(外部表)的概念。其实,该连接过程就是一个 2 层嵌套循环,所以外层循环的次数越少越好,这也就是我们为什么将小表或返回较小 row source 的表作为驱动表(用于外层循环)的理论依据。

在 NESTED LOOPS 连接中,Oracle 读取 row source1 中的每一行,然后在 row source2 中检查是否有匹配的行,所有被匹配的行都被放到结果集中,然后处理 row source1 中的下一行。这个过程一直继续,直到 row source1 中的所有行都被处理。这是从连接操作中可以得到第一个匹配行的最快的方法之一,这种类型的连接可以用在需要快速响应的语句中,以响应速度为主要目标。

如果 driving row source (外部表)比较小,并且在 inner row source (内部表)上有唯一索引,或有高选择性非唯一索引时,使用这种方法可以得到较好的效率。NESTED LOOPS 有其它连接方法没有的的一个优点是:可以先返回已经连接的行,而不必等待所有的连接操作处理完才返回数据,这可以实现快速的响应时间。

HASH JOIN

较小的 row source 被用来构建 hash table 与 bitmap ,第 2 个 row source 被用来被 hashed,并与第一个 row source 生成的 hash table 进行匹配,以便进行进一步的连接。Bitmap 被用来作为一种比较快的查找方法,来检查在 hash table 中是否有匹配的行。特别的,当 hash table 比较大而不能全部容纳在内存中时,这种查找方法更为有用。这种连接方法也有NL连接中所谓的驱动表的概念,被构建为 hash table 与 bitmap 的表为驱动表,当被构建的 hash table 与 bitmap 能被容纳在内存中时,这种连接方式的效率极高。

SORT MERGE JOIN

内部连接过程:

  1. 首先生成 row source1 需要的数据,然后对这些数据按照连接操作关联列进行排序。
  2. 随后生成 row source2 需要的数据,然后对这些数据按照与 sort source1 对应的连接操作关联列进行排序。
  3. 最后两边已排序的行被放在一起执行合并操作,即将 2 个 row source 按照连接条件连接起来。

排序是一个费时、费资源的操作,特别对于大表。基于这个原因,SMJ 经常不是一个特别有效的连接方法,但是如果 2 个 row source 都已经预先排序,则这种连接方法的效率也是蛮高的。

怎么选择连接方法

排序 – – 合并连接(Sort Merge Join, SMJ):

如果在关联的列上都有索引,那么效率会比较好,因为省却了排序的步骤。

嵌套循环(Nested Loops, NL):

  • 如果 driving row source (外部表)比较小,并且在 inner row source (内部表)上有唯一索引,或有高选择性非唯一索引时,使用这种方法可以得到较好的效率。

  • NESTED LOOPS 有其它连接方法没有的的一个优点是:可以先返回已经连接的行,而不必等待所有的连接操作处理完才返回数据,这可以实现快速的响应时间。

哈希连接(Hash Join, HJ):

  • 这种方法是在oracle7后来引入的,使用了比较先进的连接理论,一般来说,其效率应该好于其它2种连接,但是这种连接只能用在 CBO 优化器中。

  • 在 2 个较大的 row source 之间连接时会取得相对较好的效率,在一个 row source 较小时则能取得更好的效率。

  • 只能用于等值连接中。


欢迎关注我的微信公众号: coderbee笔记,可以更及时回复你的讨论。

发表回复

您的电子邮箱地址不会被公开。 必填项已用 * 标注

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据