在上一篇文章DM7操作符优化系列（一）—— 单表操作符中，我们为大家介绍了单表操作符，但是在实际应用过程中，查询时涉及到的往往不止一张表，不同的表之间存在一定的关系，处理多张表时就会涉及到多表操作符，接下来讲解多表操作符。

NEST LOOP (INNER LEFT RIGHT SEMI) 嵌套循环连接
HASH JOIN(INNER LEFT RIGHT SEMI) 哈希连接
INDEX JOIN (INNER LEFT RIGHT SEMI) 索引连接
MERGE JOIN 排序归并连接

首先创建最简单的测试用表

CREATE TABLE T1(ID VARCHAR);
CREATE TABLE T2(ID VARCHAR);
INSERT INTO T1 VALUES('AMEE'),('AMSS'),('BURNING'),('ABED'),('CHALICE');
INSERT INTO T2 VALUES('AAAA'),('AAAA'),('BBBB'),('CCCC'),('DDDD'),('AAME'),('AMEE'),('EEEE');

• NEST LOOP INNER JOIN

最基础的一种连接方式，将一张表的每一个值分别与另一张表的所有值拼接，形成一个大结果集，再从大结果集中过滤出满足条件的行。

--SEL9
SQL> EXPLAIN SELECT /*+ENABLE_HASH_JOIN(0)*/* FROM T1,T2 WHERE T1.ID = T2.ID;

1   #NSET2: [7, 20, 96]
2     #PRJT2: [7, 20, 96]; exp_num(2), is_atom(FALSE)
3       #SLCT2: [7, 20, 96]; T1.ID = T2.ID
4         #NEST LOOP INNER JOIN2: [7, 20, 96];
5           #CSCN2: [0, 5, 48]; INDEX33555457(T1)
6           #CSCN2: [0, 8, 48]; INDEX33555458(T2)

##SEL9中/+ENABLE_HASH_JOIN(0)/ 为查询提示，暂时不用关注

这里T1中存在5行数据，T2中存在8行数据，NEST LOOP INNER JOIN 就是将这两个表无条件组成一张5 * 8 = 40 行的表
然后对这40行的表依次筛选出 T1.ID = T2.ID的数据（查询计划中的第3行 SLCT操作符，文章后面会讲到）
不难看出，这种方式是我们比较不希望看到的，如果T1、T2表较大，那么生成的基表会非常大，同样的，上层过滤条件需要执行的次数也非常多，这样效率非常低。

在输出上，这种连接方式的结果集按左表（T1）涉及的索引有序。

• HASH JOIN

没有索引的情况下，大多数连接的处理方式，是将一张表的连接列做成HASH表，另一张表的数据向这个HASH表匹配，满足条件的值返回，计划的形式一般如下

--SEL 10 
EXPLAIN SELECT * FROM T1,T2 WHERE T1.ID = T2.ID;

1   #NSET2: [0, 20, 96]
2     #PRJT2: [0, 20, 96]; exp_num(2), is_atom(FALSE)
3       #HASH2 INNER JOIN: [0, 20, 96];  KEY_NUM(1);
4         #CSCN2: [0, 5, 48]; INDEX33555457(T1)
5         #CSCN2: [0, 8, 48]; INDEX33555458(T2)

这里由于HASH表的生成不容易直观表现，我们用例子的方式呈现匹配过程：

T1 C1 AMEE AMSS BURNING ABED CHALICE （5行）
T2 C1 AAAA AAAA BBBB CCCC DDDD AAME AMEE EEEE （8行）

我们指定一种 HASH 算法（一种简化的算法，服务器上的 HASH 算法并不是这样），将字符串转换成首字符 + 字符长度，比如 AMEE 转换成 A4 ，BURNING 转换成B7，我们将转换后的值称为 FOLD 。
具体执行时，首先我们获取到T1的所有数据，将 T1的数据按照上述算法去处理，得到处理后的 T1 HASH 表数据：

A4(AMEE AMSS ABED)
B7(BURNING)
C7(CHALICE)

然后我们获取 T2 的所有数据，一样进行 HASH 算法转换，每一个转换的结果再与 T1 的 HASH 表中所有数据进行对比，对比分为两个阶段，FOLD 对比和内容对比，当 FOLD 不相等时直接跳过后一阶段。
对比过程：
AAAA -> A4 -> COMPARE A4 (AMEE AMSS ABED) -> 满足FOLD
-> 轮询检查内容(AAAA -> AMEE AAAA -> AMSS AAAA -> ABED)
-> 不满足任意条件 -> 返回空
AAAA -> A4 -> COMPARE A4 (AMEE AMSS ABED) -> 满足FOLD
-> 轮询检查内容(AAAA -> AMEE AAAA -> AMSS AAAA -> ABED)
-> 不满足任意条件 -> 返回空
BBBB -> B4 -> COMPARE A4 (AMEE AMSS ABED) -> 不满足FOLD
-> COMPARE B7(BURNING) -> 不满足FOLD
-> COMPARE C7 (CHALICE) -> 不满足FOLD
-> 不满足任意条件 -> 返回空
CCCC -> C4 -> COMPARE A4 (AMEE AMSS ABED) -> 不满足FOLD
-> COMPARE B7(BURNING) -> 不满足FOLD
-> COMPARE C7 (CHALICE) -> 不满足FOLD
-> 不满足任意条件 -> 返回空
DDDD -> D4 -> COMPARE A4 (AMEE AMSS ABED) -> 不满足FOLD
-> COMPARE B7(BURNING) -> 不满足FOLD
-> COMPARE C7 (CHALICE) -> 不满足FOLD
-> 不满足任意条件 -> 返回空
AAME -> A4 -> COMPARE A4 (AMEE AMSS ABED) -> 满足FOLD
-> 轮询检查内容(AAME -> AMEE AAME -> AMSS AAME -> ABED)
-> 不满足任意条件 -> 返回空
AMEE -> A4 -> COMPARE A4 (AMEE AMSS ABED) -> 满足FOLD
-> 轮询检查内容(AMEE -> AMEE)
-> 满足条件 -> 返回AMEE
EEEE -> E4 -> COMPARE A4 (AMEE AMSS ABED) -> 不满足FOLD
-> COMPARE B7(BURNING) -> 不满足FOLD
-> COMPARE C7 (CHALICE) -> 不满足FOLD
-> 不满足任意条件 -> 返回空
最终得到结果集 AMEE AMEE
可以看到，这样处理的计算量相比 NEST LOOP 会少很多，主要的计算量有三个部分：
1.对左右表的全表扫描（T1、T2）
2. HASH 表的计算（取决于HASH算法的计算复杂度）
3.与右表（T2）每行数据进行匹配
（取决于每一个 FOLD 中存在多少个值，满足 FOLD 的情况下还需要进行多少次匹配，比如 A4 中存在3个值，每一个值匹配时最多需要再匹配3次， INI 参数，JOIN_HASH_SIZE 控制的就是 HASH 表的 FOLD 个数，一般来说 FOLD 越多，每个 FOLD 中的值越少）

由于所有的输出都是在扫描右表时完成的，所有 HASH JOIN 的输出是按右表（T2）涉及的索引有序的。

• INDEX JOIN

将一张表（T1）的数据拿出，去另外一张表（T2）上进行范围扫描找出需要的数据行。索引连接需要右表的连接列上存在索引。

CREATE INDEX I_TEST2 ON T2(ID);

--SEL 11
EXPLAIN SELECT * FROM T1,T2 WHERE T1.ID = T2.ID;

1   #NSET2: [0, 17, 96]
2     #PRJT2: [0, 17, 96]; exp_num(2), is_atom(FALSE)
3       #NEST LOOP INDEX JOIN2: [0, 17, 96]
4         #CSCN2: [0, 5, 48]; INDEX33555457(T1)
5         #SSEK2: [0, 3, 0]; scan_type(ASC), I_TEST2(T2), scan_range[T1.ID,T1.ID]

这样的做法基本等价于：在右表（T2）上做N次（select * from t2 where id = ?）这样的语句，开销取决于（select * from t2 where id = ?）这样语句的结果集行数以及左表T1的行数，若两者都很小，那么这种方式是最理想的连接方式。

这种连接方式是按 T1 的基表操作符涉及的索引有序输出的。

SPL：某一张表输出一行结果后，带入到另一个表中进行执行，满足条件则输出

--SEL12
EXPLAIN SELECT /*+REFED_EXISTS_OPT_FLAG(0) ENABLE_RQ_TO_NONREF_SPL(2)*/* FROM T1 A WHERE EXISTS (SELECT * FROM T2 B WHERE A.ID = B.ID);

1   #NSET2: [0, 1, 56]
2     #PIPE2: [0, 1, 56]
3       #PRJT2: [0, 1, 56]; exp_num(2), is_atom(FALSE)
4         #SLCT2: [0, 1, 56]; NOREFED_EXISTS_SSS[sss3]
5           #SSCN: [0, 5, 56]; I_INDEX2(T1 as A)
6       #SPL2: [0, 1, 48]; key_num(1), spool_num(0)
7         #PRJT2: [0, 1, 48]; exp_num(1), is_atom(FALSE)
8           #SSEK2: [0, 1, 48]; scan_type(ASC), I_TEST2(T2 as B), scan_range[var1,var1]

在这里两张表的情况下，我们看到首先是对T1进行扫描获取到数据，然后每一行结果放到T2中进行过滤（SEEK I_TEST2 scan_range[var1,var1]），两张表的情况下，这样的处理方式和INDEX JOIN 基本类似，但在一些更复杂的情况中，不能使用 INDEX JOIN 的时候，这样的处理方式有助于提升处理效率。

• MERGE JOIN

两张表都扫描索引，按照索引顺序进行归并。

CREATE INDEX I_INDEX2 ON T1(ID);

--SEL13
EXPLAIN SELECT /*+enable_index_join(0) enable_hash_join(0)*/* FROM T1,T2 WHERE T1.ID = T2.ID;
1   #NSET2: [0, 14, 96]
2     #PRJT2: [0, 14, 96]; exp_num(2), is_atom(FALSE)
3       #MERGE INNER JOIN3: [0, 14, 96];
4         #SSCN: [0, 5, 48]; I_INDEX2(T1)
5         #SSCN: [0, 8, 48]; I_TEST2(T2)

这里需要同时 SSCN 两条有序索引，将其中满足条件的值输出到结果集，效率比 NEST LOOP 要高很多，不考虑其他条件，如果 T1 和 T2 都很大的情况下跟 HASH JOIN 的效率相当（HASH JOIN 是 CSCN 两张基表，MERGE JOIN 则 SSCN 相关索引）

这种连接的输出是按 T1 的索引严格有序的。

多表连接的操作符可以理解为复杂查询的基本单元，涉及到多表的复杂查询大多是由这些操作符组合而成，在此我们暂时只了解什么时候会出现这些不同的连接处理方式，具体的优化方式会在后面的文章中讲到。