oracle连接的类型,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

内部连接过程：

首先生成 row source1 需要的数据，然后对这些数据按照连接操作关联列进行排序。

随后生成 row source2 需要的数据，然后对这些数据按照与 sort source1 对应的连接操作关联列进行排序。

最后两边已排序的行被放在一起执行合并操作，即将 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笔记，可以更及时回复你的讨论。