多表连接操作符

在做多表连接查询时，我们可能会碰到的 SQL 操作符有以下几种类别：

• NEST LOOP 嵌套循环连接
• HASH JOIN 哈希连接
• INDEX JOIN 索引连接
• 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：最基础的连接方式，将一张表的一个值与另一张表的所有值拼接，形成一个大结果集，再从大结果集中过滤出满足条件的行。

--SEL 9
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)/ 为优化器提示，此处是对 ini 进行语句级动态提示，意为不启用 hash 连接。

这里 T1 中存在 5 行数据，T2 中存在 8 行数据，NEST LOOP JOIN 就是将这两个表无条件组成一张 5*8=40 行的表，然后对这 40 行的表依次筛选出 T1.ID=T2.ID 的数据（SLCT 操作符）。

不难看出，这种方式是我们比较不希望看到的，如果 T1，T2 表非常大，那么生成的表会非常大，同样上层过滤条件需要执行的次数也非常多。输出上，结果集按左表 (T1) 涉及的索引有序

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

--SEL 10
SQL>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 JOIN。以一张表的连接列为 Hash 键，构造 HASH 表，另一张表的连接列进行 HASH 探测，找到满足条件的记录。由于 HASH 命中率高，因此，在大数据量情况下，HASH JOIN 的效率较NEST LOOP 会高很多，主要的计算量有三个部分

• 对左右表的全表扫描 (T1，T2)
• HASH 表的计算（取决于 HASH 算法的计算复杂度）
• 右表 (T2) 每行数据进行匹配

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

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

SQL>create index i_test2 on t2(id);
--SEL 11
SQL>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 的基表操作符涉及的索引有序输出的。

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

SQL>create index i_test1 on t1(id);
--SEL 12
SQL>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     # CSCN:[0,5,48];I_TEST1(T1)
5     # CSCN:[0,8,48];I_TEST2(T2)

需要同时 SSCN 两条有序索引，将其中满足条件的值输出到结果集，效率比 NEST LOOP 要高。这里的输出是按 T1 的索引有序的。

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

--SEL 13
SQL>explain select /*+REFED_EXISTS_OPT_FLAG(10) 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_TEST1(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 的时候，这样的处理方式有助于提升处理效率。