Oracle SQL优化③——表的连接方式

前言

表（结果集）与表（结果集）之间的连接方式非常重要，如果CBO选择了错误的连接方式，本来几秒就能出结果的SQL可能执行一天都执行不完。如果想要快速定位超大型SQL性能问题，就必须深入理解表连接方式。在多表关联的时候，一般情况下只能是两个表先关联，两表关联之后的结果再和其他表/结果集关联，如果执行计划中出现了Filter,这时可以一次性关联多个表。

一.嵌套循环（NESTED LOOPS）

嵌套循环的算法：驱动表返回一行数据，通过连接列传值给被驱动表，驱动表返回多少行，被驱动表就要被扫描多少次。
嵌套循环可以快速返回两表关联的前几条数据，如果SQL中添加了HINT:FIRST_ROWS,在两表关联的时候，优化器更倾向于嵌套循环。
嵌套循环驱动表应该返回少量数据。如果驱动表返回了100万行，那么被驱动表就会被扫描100万次。这个时候SQL会执行很久，被驱动表会被误认为热点表，被驱动表连接列的索引也会被误认为热点索引。
嵌套循环被驱动表必须走索引。如果嵌套循环被驱动表的连接列没包含在索引中，那么被驱动表就只能走全表扫描，而且是反复多次全表扫描。当被驱动表很大的时候，SQL就执行不出结果。
嵌套循环被驱动表走索引只能走INDEX UNIQUE SCAN或者INDEX RANGE SCAN。
嵌套循环被驱动表不能走TABLE ACCESS FULL，不能走INDEX FULL SCAN，不能走INDEX SKIP SCAN，也不能走INDEX FAST FULL SCAN。
嵌套循环被驱动表的连接列基数应该很高。如果被驱动表连接列的基数很低，那么被驱动表就不应该走索引，这样一来被驱动表就只能进行全表扫描了，但是被驱动表也不能走全表扫描。
两表关联返回少量数据才能走嵌套循环。前面提到，嵌套循环被驱动表必须走索引，如果两表关联，返回100万行数据，那么被驱动表走索引就会产生100万次回表。回表一般是单块读，这个时候SQL性能极差，所以两表关联返回少量数据才能走嵌套循环。

二.HASH连接（HASH JOIN）

上面提到，两表关联返回少量数据应该走嵌套循环，两表关联返回大量数据应该走HASH连接。
HASH连接的算法：两表等值关联，返回大量数据，将较小的表选为驱动表，将驱动表的“select列和join列”读入PGA中的work area,然后对驱动表的连接列进行hash运算生成hash table,当驱动表的所有数据完全读入PGA中的work area之后，再读取被驱动表（被驱动表不需要读入PGA中的work area),对被驱动表的连接列也进行hash运算，然后到PGA中的work area去探测hash table，找到数据就关联上，没找到数据就没关联上。哈希连接只支持等值连接。

再次强调，嵌套循环被驱动表需要扫描多次，HASH连接的被驱动表只需要扫描一次。
HASH连接，执行计划中的Used-Mem表示HASH连接消耗了多少PGA,当驱动表太大、PGA不能完全容纳驱动表时，驱动表就会溢出到临时表空间，进而产生磁盘HASH连接，这时候HASH连接性能会严重下降。嵌套循环不需要消耗PGA。
嵌套循环每循环一次，会将驱动表连接列传值给被驱动表的连接列，也就是说嵌套循环会进行传值。HASH连接没有传值的过程。在进行HASH连接的时候，被驱动表的连接列会生成HASH值，到PGA中去探测驱动表所生成的hash table。HASH连接的驱动表与被驱动表的连接列都不需要创建索引。
OLTP环境一般是高并发小事物居多，此类SQL返回结果很少，SQL执行计划多以嵌套循环为主，因此OLTP环境SGA设置较大，PGA设置较小（因为嵌套循环不消耗 PGA)。而OLAP环境多数SQL都是大规模的ETL,此类SQL返回结果集很多，SQL执行计划通常以HASH连接为主，往往要消耗大量PGA，所以OLAP系统PGA设置较大。

三.排序合并连接（SORT MERGE JOIN）

上文提到HASH连接主要用于处理两表等值关联返回大量数据。
排序合并连接主要用于处理两表非等值关联，比如>，>=，<， <=,< ，但是不能用于instr、substr、like、regexp_like关联，instr、substr、like、regexp_like关联只能走嵌套循环。现有如下SQL。

select * from a,b where a.id>=b.id;

A表有10万条数据，B表有20万条数据，A表与B表的ID列都是从1开始每次加1。该SQL是非等值连接，因此不能进行HASH连接。
假如该SQL走的是嵌套循环，A作为驱动表，B作为被驱动表，那么B表会被扫描10万次。前文提到，嵌套循环被驱动表连接列要包含在索引中，那么B表的ID列需要创建一个索引，嵌套循环会进行传值，当A表通过ID列传值超过10000的时候，B表通过ID列的索引返回数据每次都会超过10000条，这个时候会造成B表大量回表。所以该SQL不能走嵌套循环，只能走排序合并连接。
排序合并连接的算法：两表关联，先对两个表根据连接列进行排序，将较小的表作为驱动表(Oracle官方认为排序合并连接没有驱动表，笔者认为是有的)，然后从驱动表中取出连接列的值，到已经排好序的被驱动表中匹配数据，如果匹配上数据，就关联成功。驱动表返回多少行，被驱动表就要被匹配多少次，这个匹配的过程类似嵌套循环，但是嵌套循环是从被驱动表的索引中匹配数据，而排序合并连接是在内存中(PGA中的work area)匹配数据。

如果两表是等值关联，一般不建议走排序合并连接。因为排序合并连接需要将两个表放入PGA中，而HASH连接只需要将驱动表放入PGA中，排序合并连接与HASH连接相比，需要耗费更多的PGA。即使排序合并连接中有一个表走的是INDEX FULL SCAN,另外一个表也需要放入PGA中，而这个表往往是大表，如果走HASH连接，大表会作为被驱动表，是不会被放入PGA中的。因此，两表等值关联，要么走NL(返回数据量少)，要么走HASH(返回数据量多)，一般情况下不要走SMJ。

四.笛卡尔连接（CARTESIAN JOIN）

两个表关联没有连接条件的时候会产生笛卡尔积，这种表连接方式就叫笛卡尔连接。
笛卡尔连接会返回两个表的乘积，比如a表有4条数据，b表有20条数据，两个表进行笛卡尔连接之后就回返回80条数据。
在多表关联的时候，两个表没有直接关联条件，但是优化器错误的把某个表返回的Rows算为一行（必须是一行），这时候也可能发生笛卡尔连接。

五.标量子查询（SCALAR SUBQUERY）

当一个子查询介于select与from之间，这种子查询就叫标量子查询。
例子：

select e.ename,
		  e.sal,
		  (select d.dname from dept d where d.deptno=e.deptno) dname
  from emp e;

标量子查询类似一个天然的嵌套循环，而且驱动表固定为主表。大家是否记得，嵌套循环被驱动表的连接列必须包含在索引中。同理，标量子查询中子查询的表连接列也必须包含在索引中。
建议在工作中，尽量避免使用标量子查询，假如主表返回大量数据，主表的连接列基数很高，那么子查询中的表会被多次扫描，从而严重影响SQL性能。如果主表数据量小，或者主表的连接列基数很低，那么这个时候我们也可以使用标量子查询，但是记得要给子查询中表的连接列建立索引。
当SQL里面有标量子查询，我们可以将标量子查询等价改写为外连接，从而使它们可以进行HASH连接。为什么要将标量子查询改写为外连接而不是内连接呢？因为标量子查询是一个传值的过程，如果主表传值给子查询，子查询没有查询到数据，这个时候会显示NULL。如果将标量子查询改写为内连接，会丢失没有关联上的数据。

在12c中，简单的标量子查询会被优化器等价改写为外连接

六.半连接（SEMI JOIN）

两表关联只返回一个表的数据就叫半连接。半连接一般就是指的in和exists。在SQL优化中，半连接的优化是最为复杂的。

1.半连接等价改写

in和exists一般情况下都可以进行等价改写
半连接in的写法如下：

select * from dept where deptno in (select deptno from emp);

半连接exists的写法如下

select * from dept where exists (select null from emp where dept.deptno=emp.deptno);

如果半连接中主表属于1的关系，子表（子查询中的表）属于n的关系，我们在改写为内连接的时候，需要加上GROUPBY去重。注意：这个时候半连接性能高于内连接。
如果半连接中主表属于n的关系，子表（子查询中的表）属于1的关系，我们在改写为内连接的时候，就不需要去重了。注意：这个时候半连接与内连接性能一样。
如果半连接中主表属于n的关系，子表（子查询中的表）也属于n的关系，这时我们可以先对子查询去重，将子表转换为1的关系，然后再关联，千万不能先关联再去重。

2.控制半连接执行计划

示例SQL

explain plan for select * from dept where deptno in (select deptno from emp);

执行计划中DEPT和EMP是采用排序合并连接进行关联的。
现在加HINT，让DEPT和EMP进行嵌套循环连接，同时让DEPT当驱动表

select /*+ use_nl(emp@a,dept) leading(dept) */
 *
  from dept
 where deptno in (select /*+ qb_name(a) */ deptno from emp);

如果不想使用qb_name这个hint 也可以参考以下操作

explain plan for select * from dept where deptno in (select deptno from emp);
select * from table(dbms_xplan.display(null,null,'advanced -projection -outline -predicate'));

现在我们让DEPT与EMP进行HASH连接，同时让EMP作为驱动表
select /*+ use_hash(dept,emp@sel$2) leading(emp@sel$2) */
*
from dept
where deptno in (select deptno from emp);

让EMP表作为驱动表之后，CBO先对EMP进行了去重(SORT UNIQUE)操作，这里CBO其实对该SQL进行了等价改写，将半连接等价改写为内连接（因为执行计划中没有SEMI关键字)，在改写的过程中，因为EMP属于N的关系，所以对EMP进行了去重。

七.反连接

两表关联只返回主表数据，而且只返回主表与子表没关联上的数据，这种连接就叫反连接。反连接一般就是指not in和not exists
反连接等价改写
not in的写法如下

select * from dept where deptno not in (select deptno from emp);

not exists的写法如下

select * from dept where not exists (select null from emp where dept.deptno = emp.deptno);

需要注意的是，not in里面如果有null，整个查询会返回空，而in里面有null，查询不受null影响，例子如下

select * from dept where deptno not in (10,null);

所以在将not exists等价改写为not in的时候，要注意null。一般情况下，如果反连接采用not in写法，我们需要在where条件中剔除null

select * from dept where deptno not in (select deptno from emp where deptno is not null);

八.FILTER

如果子查询（in/exists/not in/not exists）没能展开（unnest），在执行计划中就会产生FILTER，FILTER类似嵌套循环，FILTER的算法与标量子查询一模一样