文章目录
表（结果集）与表（结果集）之间的连接方式非常重要，如果CBO选择了错误的连接方式，本来几秒就能出结果的SQL可能执行一天都执行不完。如果想要快速定位超大型SQL性能问题，我们就必须深入理解表连接方式。在多表关联的时候，一般情况下只能是两个表先关联，两表关联之后的结果再和其他表/结果集关联，如果执行计划中出现了Filter，这时可以一次性关联多个表。
1、嵌套循环（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性能极差，所以两表关联返回少量数据才能走嵌套循环。
我们在测试账号scott中运行如下SQL：
SQL> select / *+ gather_plan_statistics use_nl(e,d) leading(e) */
2 e.ename, e.job, d.dname
3 from emp e, dept d
4 where e.deptno = d.deptno;
我们运行下面命令获取带有A-TIME的执行计划：
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID g374au8y24mw5, child number 0
-------------------------------------
select / *+ gather_plan_statistics use_nl(e,d) leading(e) */ e.ename,
e.job, d.dname from emp e, dept d where e.deptno = d.deptno
Plan hash value: 3625962092
-------------------------------------------------------------------------------------
| Id | Operation |Name |Starts|E-Rows|A-Rows| A-Time |Buffers|
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1| | 14|00:00:00.01| 26 |
| 1 | NESTED LOOPS | | 1| | 14|00:00:00.01| 26 |
| 2 | NESTED LOOPS | | 1| 15| 14|00:00:00.01| 12 |
| 3 | TABLE ACCESS FULL |EMP | 1| 15| 14|00:00:00.01| 8 |
|* 4 | INDEX UNIQUE SCAN |PK_DEPT| 14| 1| 14|00:00:00.01| 4 |
| 5 | TABLE ACCESS BY INDEX ROWID|DEPT | 14| 1| 14|00:00:00.01| 14 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."DEPTNO"="D"."DEPTNO")
在执行计划中，离NESTED LOOPS关键字最近的表就是驱动表。这里EMP就是驱动表，DEPT就是被驱动表。
驱动表EMP扫描了一次（Id=3，Starts=1），返回了14行数据（Id=3，A-Row），传值14次给被驱动表（Id=4），被驱动表扫描了14次（Id=4，Id=5，Starts=14）。
下面是嵌套循环的PLSQL代码实现：
declare
cursor cur_emp is
select ename, job, deptno from emp;
v_dname dept.dname%type;
begin
for x in cur_emp loop
select dname into v_dname from dept where deptno = x.deptno;
dbms_output.put_line(x.ename || ' ' || x.job || ' ' || v_dname);
end loop;
end;
游标cur_emp就相当于驱动表EMP，扫描了一次，一共返回了14条记录。该游标循环了14次，每次循环的时候传值给dept，dept被扫描了14次。为什么嵌套循环被驱动表的连接列要创建索引呢？我们注意观察加粗部分的PLSQL代码。
declare
cursor cur_emp is
select ename, job, deptno from emp;
v_dname dept.dname%type;
begin
for x in cur_emp loop
select dname into v_dname from dept where deptno = x.deptno;
dbms_output.put_line(x.ename || ' ' || x.job || ' ' || v_dname);
end loop;
end;
因为扫描被驱动表dept次数为14次，每次需要通过deptno列传值，所以嵌套循环被驱动表的连接列需要创建索引。
提醒大家，在编写PLSQL的时候，尽量避免游标循环里面套用SQL，因为那是纯天然的嵌套循环。假如游标返回100万行数据，游标里面的SQL会被执行100万次。同样的道理，游标里面尽量不要再套游标，如果外层游标循环1万次，内层游标循环1万次，那么最里面的SQL将被执行一亿次。
当两表使用外连接进行关联，如果执行计划是走嵌套循环，那么这时无法更改驱动表，驱动表会被固定为主表，例如下面SQL：
SQL> explain plan for select / *+ use_nl(d,e) leading(e) */
2 *
3 from dept d
4 left join emp e on d.deptno = e.deptno;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 2022884187
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 812 | 8 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER| | 14 | 812 | 8 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 4 | 152 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
3 - filter("D"."DEPTNO"="E"."DEPTNO"(+))
15 rows selected.
use_nl(d,e)表示让两表走嵌套循环，在书写HINT的时候，如果表有别名，HINT中一定要使用别名，否则HINT不生效；如果表没有别名，HINT中就直接使用表名。
leading(e)表示让EMP表作为驱动表。从执行计划中我们可以看到，DEPT与EMP是采用嵌套循环进行连接的，这说明use_nl(d,e)生效了。执行计划中驱动表为DEPT，虽然设置了leading(e)，但是没有生效。
为什么leading(e)没有生效呢？因为DEPT与EMP是外连接，DEPT是主表，EMP是从表，外连接走嵌套循环的时候驱动表只能是主表。
为什么两表关联是外连接的时候，走嵌套循环无法更改驱动表呢？因为嵌套循环需要传值，主表传值给从表之后，如果发现从表没有关联上，直接显示为NULL即可；但是如果是从表传值给主表，没关联上的数据不能传值给主表，不可能传NULL给主表，所以两表关联是外连接的时候，走嵌套循环驱动表只能固定为主表。
需要注意的是，如果外连接中从表有过滤条件，那么此时外连接会变为内连接，例如下面SQL:
SQL> select / *+ leading(e) use_nl(d,e) */ *
2 from dept d
3 left join emp e on d.deptno = e.deptno
4 where e.sal < 3000;
11 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 351108634
-------------------------------------------------------------------------------------
| Id |Operation | Name | Rows | Bytes | Cost(%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 |SELECT STATEMENT | | 12 | 696 | 15 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 12 | 696 | 15 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | EMP | 12 | 456 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("E"."SAL"
HINT指定了让从表EMP作为嵌套循环驱动表，从执行计划中我们看到，EMP确实是作为嵌套循环的驱动表，而且执行计划中没有OUTER关键字，这说明SQL已经变为内连接。
为什么外连接的从表有过滤条件会变成内连接呢？因为外连接的从表有过滤条件已经排除了从表与主表没有关联上显示为NULL的情况。
提问：两表关联走不走NL是看两个表关联之后返回的数据量多少?还是看驱动表返回的数据量多少？
回答：如果两个表是1∶N关系，驱动表为1，被驱动表为N并且N很大，这时即使驱动表返回数据量很少，也不能走嵌套循环，因为两表关联之后返回的数据量会很多。所以判断两表关联是否应该走NL应该直接查看两表关联之后返回的数据量，如果两表关联之后返回的数据量少，可以走NL；返回的数据量多，应该走HASH连接。
提问：大表是否可以当嵌套循环（NL）驱动表？
回答：可以，如果大表过滤之后返回的数据量很少就可以当NL驱动表。
提问： select * from a,b where a.id=b.id; 如果a有100条数据，b有100万行数据，a与b是1∶N关系，N很低，应该怎么优化SQL?
回答： 因为a与b是1∶N关系，N很低，我们可以在b的连接列（id）上创建索引，让a与b走嵌套循环（a nl b），这样b表会被扫描100次，但是每次扫描表的时候走的是id列的索引（范围扫描）。如果让a和b进行HASH连接，b表会被全表扫描（因为没有过滤条件），需要查询表中100万行数据，而如果让a和b进行嵌套循环，b表只需要查询出表中最多几百行数据（100*N）。一般情况下，一个小表与一个大表关联，我们可以考虑让小表NL大表，大表走连接列索引（如果大表有过滤条件，需要将过滤条件与连接列组合起来创建组合索引），从而避免大表被全表扫描。
2、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，找到数据就关联上，没找到数据就没关联上。哈希连接只支持等值连接。
我们在测试账号scott中运行如下SQL：
SQL> select / *+ gather_plan_statistics use_hash(e,d) */
2 e.ename, e.job, d.dname
3 from emp e, dept d
4 where e.deptno = d.deptno;
我们运行如下命令获取执行计划：
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL_ID 2dj5zrbcps5yu, child number 0
-------------------------------------
select / *+ gather_plan_statistics use_hash(e,d) */ e.ename, e.job,
d.dname from emp e, dept d where e.deptno = d.deptno
Plan hash value: 615168685
-------------------------------------------------------------------------------------
| Id |Operation |Name|Starts|E-Rows|A-Rows| A-Time |Buffers|OMem|1Mem|Used-Mem|
-------------------------------------------------------------------------------------
| 0 |SELECT STATEMENT | | 1| | 14|00:00:00.01| 15| | | |
|* 1 | HASH JOIN | | 1| 15| 14|00:00:00.01| 15|888K|888K| 714K(0)|
| 2 | TABLE ACCESS FULL|DEPT| 1| 4| 4|00:00:00.01| 7| | | |
| 3 | TABLE ACCESS FULL|EMP | 1| 15| 14|00:00:00.01| 8| | | |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPTNO"="D"."DEPTNO")
执行计划中离HASH连接关键字最近的表就是驱动表。这里DEPT就是驱动表，EMP就是被驱动表。驱动表DEPT只扫描了一次（Id=2，Starts=1），被驱动表EMP也只扫描了一次（Id=3，Starts=1）。再次强调，嵌套循环被驱动表需要扫描多次，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设置较大。
当两表使用外连接进行关联，如果执行计划走的是HASH连接，想要更改驱动表，我们需要使用swap_join_inputs，而不是leading，例如下面SQL：
SQL> explain plan for select / *+ use_hash(d,e) leading(e) */
2 *
3 from dept d
4 left join emp e on d.deptno = e.deptno;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 3713469723
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 812 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 14 | 812 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 532 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("D"."DEPTNO"="E"."DEPTNO"(+))
15 rows selected.
从执行计划中我们可以看到，DEPT与EMP是采用HASH连接，这说明use_hash(d,e)生效了。执行计划中，驱动表为DEPT，虽然设置了leading(e)，但是没有生效。现在我们使用swap_join_inputs来更改外连接中HASH连接的驱动表。
SQL> explain plan for select / *+ use_hash(d,e) swap_join_inputs(e) */
2 *
3 from dept d
4 left join emp e on d.deptno = e.deptno;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
Plan hash value: 3590956717
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 812 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN RIGHT OUTER| | 14 | 812 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | DEPT | 4 | 80 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("D"."DEPTNO"="E"."DEPTNO"(+))
15 rows selected.
从执行计划中我们可以看到，DEPT与EMP是采用HASH连接，这说明use_hash(d,e)生效了。执行计划中，驱动表为DEPT，虽然设置了leading(e)，但是没有生效。现在我们使用swap_join_inputs来更改外连接中HASH连接的驱动表：
SQL> explain plan for select / *+ use_hash(d,e) swap_join_inputs(e) */
2 *
3 from dept d
4 left join emp e on d.deptno = e.deptno;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
Plan hash value: 3590956717
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 812 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN RIGHT OUTER| | 14 | 812 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | DEPT | 4 | 80 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("D"."DEPTNO"="E"."DEPTNO"(+))
15 rows selected.
从执行计划中我们可以看到，使用swap_join_inputs更改了外连接中HASH连接的驱动表。
**思考：**怎么优化HASH连接？
**回答：**因为HASH连接需要将驱动表的select列和join列放入PGA中，所以，我们应该尽量避免书写select * from…语句，将需要的列放在select list中，这样可以减少驱动表对PGA的占用，避免驱动表被溢出到临时表空间，从而提升查询性能。如果无法避免驱动表被溢出到临时表空间，我们可以将临时表空间创建在SSD上或者RAID 0上，加快临时数据的交换速度。
当PGA采用自动管理，单个进程的work area被限制在1G以内，如果是PGA采用手动管理，单个进程的work area不能超过2GB。如果驱动表比较大，比如驱动表有4GB，可以开启并行查询至少parallel(4)，将表拆分为至少4份，这样每个并行进程中的work area能够容纳1GB数据，从而避免驱动表被溢出到临时表空间。如果驱动表非常大，比如有几十GB，这时开启并行HASH也无能为力，这时，应该考虑对表进行拆分。
3、排序合并连接（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列传值超过10 000的时候，B表通过ID列的索引返回数据每次都会超过10 000条，这个时候会造成B表大量回表。所以该SQL不能走嵌套循环，只能走排序合并连接。
排序合并连接的算法：两表关联，先对两个表根据连接列进行排序，将较小的表作为驱动表（Oracle官方认为排序合并连接没有驱动表，笔者认为是有的），然后从驱动表中取出连接列的值，到已经排好序的被驱动表中匹配数据，如果匹配上数据，就关联成功。驱动表返回多少行，被驱动表就要被匹配多少次，这个匹配的过程类似嵌套循环，但是嵌套循环是从被驱动表的索引中匹配数据，而排序合并连接是在内存中（PGA中的work area）匹配数据。
我们在测试账号scott中运行如下SQL：
SQL> select / *+ gather_plan_statistics */ e.ename, e.job,
2 d.dname from emp e, dept d where e.deptno >= d.deptno;
......省略输出结果......
我们获取执行计划：
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL_ID f673my5x7tkkg, child number 0
-------------------------------------
select / *+ gather_plan_statistics */ e.ename, e.job, d.dname from
emp e, dept d where e.deptno >= d.deptno
Plan hash value: 844388907
-------------------------------------------------------------------------------------
| Id |Operation |Name |Starts|E-Rows|A-Rows| A-Time |Buffers|
-------------------------------------------------------------------------------------
| 0 |SELECT STATEMENT | | 1| | 31|00:00:00.01| 15|
| 1 | MERGE JOIN | | 1| 3| 31|00:00:00.01| 15|
| 2 | TABLE ACCESS BY INDEX ROWID|DEPT | 1| 4| 4|00:00:00.01| 8|
| 3 | INDEX FULL SCAN |PK_DEPT| 1| 4| 4|00:00:00.01| 4|
|* 4 | SORT JOIN | | 4| 14| 31|00:00:00.01| 7|
| 5 | TABLE ACCESS FULL |EMP | 1| 14| 14|00:00:00.01| 7|
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."DEPTNO">="D"."DEPTNO")
filter("E"."DEPTNO">="D"."DEPTNO")
执行计划中离MERGE JOIN关键字最近的表就是驱动表。这里DEPT就是驱动表，EMP就是被驱动表。驱动表DEPT只扫描了一次（Id=2，Starts=1），被驱动表EMP也只扫描了一次（Id=5,Starts=1）。
因为DEPT走的是INDEX FULL SCAN，INDEX FULL SCAN返回的数据是有序的，所以DEPT表就不需要排序了。EMP走的是全表扫描，返回的数据是无序的，所以EMP表在PGA中进行了排序。在实际工作中，我们一定要注意INDEX FULL SCAN返回了多少行数据，如果INDEX FULL SCAN返回的行数太多，应该强制走全表扫描。
现在我们强制DEPT表走全表扫描，查看执行计划：
SQL> select / *+ full(d) */
2 e.ename, e.job, d.dname
3 from emp e, dept d
4 where e.deptno >= d.deptno;
31 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1407029907
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 90 | 8 (25)| 00:00:01 |
| 1 | MERGE JOIN | | 3 | 90 | 8 (25)| 00:00:01 |
| 2 | SORT JOIN | | 4 | 52 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 4 | 52 | 3 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 238 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL| EMP | 14 | 238 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."DEPTNO">="D"."DEPTNO")
filter("E"."DEPTNO">="D"."DEPTNO")
从执行计划中我们看到，DEPT走的是全表扫描，因为全表扫描返回的数据是无序的，所以DEPT在PGA中进行了排序。
如果两表是等值关联，一般不建议走排序合并连接。因为排序合并连接需要将两个表放入PGA中，而HASH连接只需要将驱动表放入PGA中，排序合并连接与HASH连接相比，需要耗费更多的PGA。即使排序合并连接中有一个表走的是INDEX FULL SCAN，另外一个表也需要放入PGA中，而这个表往往是大表，如果走HASH连接，大表会作为被驱动表，是不会被放入PGA中的。因此，两表等值关联，要么走NL（返回数据量少），要么走HASH（返回数据量多），一般情况下不要走SMJ。
思考：怎么优化排序合并连接？
**回答：**如果两表关联是等值关联，走的是排序合并连接，我们可以将表连接方式改为HASH连接。如果两表关联是非等值关联，比如>，>=，<，<=，<>，这时我们应该先从业务上入手，尝试将非等值关联改写为等值关联，因为非等值关联返回的结果集“类似”于笛卡儿积，当两个表都比较大的时候，非等值关联返回的数据量相当“恐怖”。如果没有办法将非等值关联改写为等值关联，我们可以考虑增加两表的限制条件，将两个表数据量缩小，最后可以考虑开启并行查询加快SQL执行速度。
下表列举出了3种表连接方式的主要区别：
4、笛卡儿连接（CARTESIAN JOIN）
两个表关联没有连接条件的时候会产生笛卡儿积，这种表连接方式就叫笛卡儿连接。
我们在测试账号scott中运行如下SQL：
SQL> set autot trace
SQL> select * from emp, dept;
56 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2034389985
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 56 | 3248 | 8 (0)| 00:00:01 |
| 1 | MERGE JOIN CARTESIAN| | 56 | 3248 | 8 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | DEPT | 4 | 80 | 3 (0)| 00:00:01 |
| 3 | BUFFER SORT | | 14 | 532 | 5 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | EMP | 14 | 532 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------
执行计划中MERGE JOIN CARTESIAN就表示笛卡儿连接。笛卡儿连接会返回两个表的乘积。DEPT有4行数据，EMP有14行数据，两个表进行笛卡儿连接之后会返回56行数据。笛卡儿连接会对两表中其中一个表进行排序，执行计划中的BUFFER SORT就表示排序。
在多表关联的时候，两个表没有直接关联条件，但是优化器错误地把某个表返回的Rows算为1行（注意必须是1行），这个时候也可能发生笛卡儿连接。例子如下：
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
Plan hash value: 710264295
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
| 1 | WINDOW SORT | | 1 |
|* 2 | TABLE ACCESS BY INDEX ROWID | F_AGT_GUARANTY_INFO_H | 1 |
| 3 | NESTED LOOPS | | 1 |
| 4 | NESTED LOOPS | | 1 |
| 5 | MERGE JOIN CARTESIAN | | 1 |
| 6 | TABLE ACCESS FULL | B_M_BUSINESS_CONTRACT | 1 |
| 7 | BUFFER SORT | | 61507 |
|* 8 | TABLE ACCESS FULL | F_AGT_GUARANTY_RELATIVE_H | 61507 |
| 9 | TABLE ACCESS BY INDEX ROWID| F_CONTRACT_RELATIVE | 1 |
|* 10 | INDEX UNIQUE SCAN | SYS_C0019578 | 1 |
|* 11 | INDEX RANGE SCAN | SYS_C005707 | 1 |
-----------------------------------------------------------------------------
执行计划中Id=6的表和Id=8的表就是进行笛卡儿连接的。在这个执行计划中，为什么优化器会选择笛卡儿积连接呢？
因为Id=6这个表返回的Rows被优化器错误地估算为1行，优化器认为1行的表与任意大小的表进行笛卡儿关联，数据也不会翻番，这是安全的。所以这里优化器选择了笛卡儿连接。
Id=6这步是全表扫描，而且没过滤条件（因为没有*），优化器认为它只返回1行。大家请思考，全表扫描返回1行并且无过滤条件，这个可能吗？难道表里面真的就只有1行数据？这不符合常识。那么显然是Id=6的表没有收集统计信息，导致优化器默认地把该表算为1行（当时数据库没开启动态采样）。下面是上述执行计划的SQL语句：
SELECT b.agmt_id,
b.corp_org,
b.cur_cd,
b.businesstype,
c.object_no,
c.guaranty_crsum,
row_number() over(PARTITION BY b.agmt_id, b.corp_org, c.object_no ORDER BY b.agmt_id, b.corp_org, c.object_no) row_no
FROM b_m_business_contract b, --合同表
dwf.f_contract_relative c, --合同关联表
dwf.f_agt_guaranty_relative_h r, --业务合同、担保合同与担保物关联表
dwf.f_agt_guaranty_info_h g --担保物信息表
WHERE b.corp_org = c.corp_org
AND b.agmt_id = c.contract_seqno --业务合同号
AND c.object_type = 'GuarantyContract'
AND r.start_dt <= DATE '2012-09-17' / *当天日期*/
AND r.end_dt > DATE '2012-09-17' / *当天日期*/
AND c.contract_seqno = r.object_no --业务合同号
AND c.object_no = r.guaranty_no --担保合同编号
AND c.corp_org = r.corp_org --企业法人编码
AND r.object_type = 'BusinessContract'
AND r.agmt_id = g.agmt_id --担保物编号
AND r.corp_org = g.corp_org --企业法人编码
AND g.start_dt <= DATE '2012-09-17' / *当天日期*/
AND g.end_dt > DATE '2012-09-17' / *当天日期*/
AND g.guarantytype = '020010' --质押存款
执行计划中进行笛卡儿关联的表就是b和r，在SQL语句中b和r没有直接关联条件。
如果两个表有直接关联条件，无法控制两个表进行笛卡儿连接。
如果两个表没有直接关联条件，我们在编写SQL的时候将两个表依次放在from后面并且添加HINT：ordered，就可以使两个表进行笛卡儿积关联：
SQL> select / *+ ordered */
2 a.ename, a.sal, a.deptno, b.dname, c.grade
3 from dept b, salgrade c, emp a
4 where a.deptno = b.deptno
5 and a.sal between c.losal and c.hisal;
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2197699399
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 65 | 12 (9)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 65 | 12 (9)| 00:00:01 |
| 2 | MERGE JOIN CARTESIAN| | 20 | 1040 | 8 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | DEPT | 4 | 52 | 3 (0)| 00:00:01 |
| 4 | BUFFER SORT | | 5 | 195 | 5 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | SALGRADE | 5 | 195 | 1 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | EMP | 14 | 182 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."DEPTNO"="B"."DEPTNO")
filter("A"."SAL">="C"."LOSAL" AND "A"."SAL"<="C"."HISAL")
在SQL语句中，DEPT与SALGRADE没有直接关联条件，HINT：ordered表示根据SQL语句中from后面表的顺序依次关联。因为DEPT与SALGRADE没有直接关联条件，而且SQL语句中添加了HINT：ordered，再有SQL语句中两个表是依次放在from后面的，所以DEPT与SALGRADE只能进行笛卡儿连接。
**思考：**当执行计划中有笛卡儿连接应该怎么优化呢？
首先应该检查表是否有关联条件，如果表没有关联条件，那么应该询问开发与业务人员为何表没有关联条件，是否为满足业务需求而故意不写关联条件。
其次应该检查离笛卡儿连接最近的表是否真的返回1行数据，如果返回行数真的只有1行，那么走笛卡儿连接是没有问题的，如果返回行数超过1行，那就需要检查为什么Rows会估算错误，同时要纠正错误的Rows。纠正错误的Rows之后，优化器就不会走笛卡儿连接了。
我们可以使用HINT /+opt_param(‘_optimizer_mjc_enabled’, ‘false’) /禁止笛卡儿连接。
5、标量子查询（SCALAR SUBQUERY）
当一个子查询介于select与from之间，这种子查询就叫标量子查询，例子如下：
select e.ename,
e.sal,
(select d.dname from dept d where d.deptno = e.deptno) dname
from emp e;
我们在测试账号scott中运行如下SQL：
SQL> select / *+ gather_plan_statistics */ e.ename,
2 e.sal,
3 (select d.dname from dept d where d.deptno = e.deptno) dname
4 from emp e;
......省略输出结果......
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
SQL_ID ggmw3tv6xypx1, child number 0
-------------------------------------
select / *+ gather_plan_statistics */ e.ename, e.sal,
(select d.dname from dept d where d.deptno = e.deptno) dname from emp e
Plan hash value: 2981343222
-------------------------------------------------------------------------------------
| Id |Operation |Name |Starts|E-Rows|A-Rows| A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 |SELECT STATEMENT | | 1| | 14|00:00:00.01| 8 |
| 1 | TABLE ACCESS BY INDEX ROWID|DEPT | 3| 1| 3|00:00:00.01| 5 |
|* 2 | INDEX UNIQUE SCAN |PK_DEPT| 3| 1| 3|00:00:00.01| 2 |
| 3 | TABLE ACCESS FULL |EMP | 1| 14| 14|00:00:00.01| 8 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("D"."DEPTNO"=:B1)
标量子查询类似一个天然的嵌套循环，而且驱动表固定为主表。大家是否还记得：嵌套循环被驱动表的连接列必须包含在索引中。同理，标量子查询中子查询的表连接列也必须包含在索引中。主表EMP通过连接列（DEPTNO）传值给子查询中的表（DEPT），执行计划中:B1就表示传值，这个传值过程一共进行了3次，因为主表（EMP）的连接列（DEPTNO）基数等于3。
SQL> select count(distinct deptno) from emp;
COUNT(DISTINCTDEPTNO)
---------------------
3
我们建议在工作中，尽量避免使用标量子查询，假如主表返回大量数据，主表的连接列基数很高，那么子查询中的表会被多次扫描，从而严重影响SQL性能。如果主表数据量小，或者主表的连接列基数很低，那么这个时候我们也可以使用标量子查询，但是记得要给子查询中表的连接列建立索引。
当SQL里面有标量子查询，我们可以将标量子查询等价改写为外连接，从而使它们可以进行HASH连接。为什么要将标量子查询改写为外连接而不是内连接呢？因为标量子查询是一个传值的过程，如果主表传值给子查询，子查询没有查询到数据，这个时候会显示NULL。如果将标量子查询改写为内连接，会丢失没有关联上的数据。
现有如下标量子查询：
SQL> select d.dname,
2 d.loc,
3 (select max(e.sal) from emp e where e.deptno = d.deptno) max_sal
4 from dept d;
DNAME LOC MAX_SAL
-------------- ------------- ----------
ACCOUNTING NEW YORK 5000
RESEARCH DALLAS 3000
SALES CHICAGO 2850
OPERATIONS BOSTON ---NULL
我们可以将其等价改写为外连接：
SQL> select d.dname, d.loc, e.max_sal
2 from dept d
3 left join (select max(sal) max_sal,
4 deptno
5 from emp
6 group by deptno)e
7 on d.deptno = e.deptno;
DNAME LOC MAX_SAL
-------------- ------------- ----------
ACCOUNTING NEW YORK 5000
RESEARCH DALLAS 3000
SALES CHICAGO 2850
OPERATIONS BOSTON ---NULL
当然了，如果主表的连接列是外键，而子查询的连接列是主键，我们就没必要改写为外连接了，因为外键不可能存NULL值，可以直接改写为内连接。
在Oracle12c中，简单的标量子查询会被优化器等价改写为外连接。
6、半连接（SEMI JOIN）
两表关联只返回一个表的数据就叫半连接。半连接一般就是指的in和exists。在SQL优化实战中，半连接的优化是最为复杂的。
6.1、半连接等价改写
in和exists一般情况下都可以进行等价改写。
半连接in的写法如下：
SQL> select * from dept where deptno in (select deptno from emp);
DEPTNO DNAME LOC
---------- ---------- ---------------------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
半连接exists的写法如下：
SQL> select * from dept where exists (select null from emp where dept.deptno=emp.deptno);
DEPTNO DNAME LOC
---------- ---------- ---------------------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
in和exists有时候也可以等价地改写为内连接，例如，上面查询语句可以改写为如下写法：
SQL> select d.*
2 from dept d, (select deptno from emp group by deptno) e
3 where d.deptno = e.deptno;
DEPTNO DNAME LOC
---------- --------------- -----------------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
注意：上面内连接的写法性能没有半连接写法性能高，因为多了GROUP BY去重操作。
在将半连接改写为内连接的时候，我们要注意主表与子表（子查询中的表）的关系。这里DEPT与EMP是1∶n关系。在半连接的写法中，返回的是DEPT表的数据，也就是说返回的数据是属于1的关系。然而在使用内连接的写法中，由于DEPT与EMP是1∶n关系，两表关联之后会返回n（有重复数据），所以我们需要加上GROUP BY去掉重复数据。
如果半连接中主表属于1的关系，子表（子查询中的表）属于n的关系，我们在改写为内连接的时候，需要加上GROUP BY去重。注意：这个时候半连接性能高于内连接。
如果半连接中主表属于n的关系，子表（子查询中的表）属于1的关系，我们在改写为内连接的时候，就不需要去重了。注意：这个时候半连接与内连接性能一样。
如果半连接中主表属于n的关系，子表（子查询中的表）也属于n的关系，这时我们可以先对子查询去重，将子表转换为1的关系，然后再关联，千万不能先关联再去重。
7、反连接（ANTI JOIN）
两表关联只返回主表的数据，而且只返回主表与子表没关联上的数据，这种连接就叫反连接。反连接一般就是指的not in和not exists。
7.1、反连接等价改写
not in与not exists一般情况下也可以进行等价改写。
not in的写法如下：
SQL> select * from dept where deptno not in (select deptno from emp);
DEPTNO DNAME LOC
---------- --------------- ---------------------------------------
40 OPERATIONS BOSTON
not exists的写法如下：
SQL> select *
2 from dept
3 where not exists (select null from emp where dept.deptno = emp.deptno);
DEPTNO DNAME LOC
---------- --------------- ---------------------------------------
40 OPERATIONS BOSTON
需要注意的是，not in里面如果有null，整个查询会返回空，而in里面有null，查询不受null影响，例子如下：
SQL> select * from dept where deptno not in (10,null);
no rows selected
SQL> select * from dept where deptno in (10,null);
DEPTNO DNAME LOC
---------- --------------- -----------------------
10 ACCOUNTING NEW YORK
所以在将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);
not in与not exists除了可以相互等价改写以外，还可以等价地改写为外连接，例如，上面查询可以等价改写为如下写法：
SQL> select d.*
2 from dept d
3 left join emp e on d.deptno = e.deptno
4 where e.deptno is null;
DEPTNO DNAME LOC
---------- --------------- ------------------
40 OPERATIONS BOSTON
为什么反连接可以改写为“外连接+子表连接条件is null”？我们再来回顾一下反连接定义：两表关联只返回主表的数据，而且只返回主表与子表没有关联上的数据。根据反连接定义，翻译为标准SQL写法就是“外连接+子表连接条件is null”。与半连接改写为内连接不同的是，反连接改写为外连接不需要考虑两表之间的关系。
8、FILTER
如果子查询（in/exists/not in/not exists）没能展开（unnest），在执行计划中就会产生FILTER，FILTER类似嵌套循环，FILTER的算法与标量子查询一模一样。
现有如下SQL以及其执行计划：
SQL> select ename, deptno
2 from emp
3 where exists (select deptno
4 from dept
5 where emp.deptno = dept.deptno
6 and dname = 'RESEARCH'
7 and rownum = 1);
Execution Plan
----------------------------------------------------------
Plan hash value: 3414630506
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost(%CPU)|Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 45 | 6 (0)|00:00:01|
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | EMP | 14 | 126 | 3 (0)|00:00:01|
|* 3 | COUNT STOPKEY | | | | | |
|* 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)|00:00:01|
|* 5 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)|00:00:01|
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT 0 FROM "DEPT" "DEPT" WHERE ROWNUM=1 AND
"DEPT"."DEPTNO"=:B1 AND "DNAME"='RESEARCH'))
3 - filter(ROWNUM=1)
4 - filter("DNAME"='RESEARCH')
5 - access("DEPT"."DEPTNO"=:B1)
执行计划中，Id=1就是FILTER。注意观察FILTER所对应的谓词信息，FILTER对应的谓词中包含有EXISTS（子查询:B1）。运用光标移动大法我们可以知道FILTER下面有两个儿子（Id=2，Id=3）。
我们在做SQL优化的时候，一般只需要关注FILTER下面有两个或者两个以上儿子这种FILTER。
9、IN与EXISTS谁快谁慢
我相信很多人都受到过in与exists谁快谁慢的困扰。如果执行计划中没有产生FILTER，那么我们可以参考以下思路：in与exists是半连接，半连接也属于表连接，那么既然是表连接，我们需要关心两表的大小以及两表之间究竟走什么连接方式，还要控制两表的连接方式，才能随心所欲优化SQL，而不是去记什么时候in跑得快，什么时候exists跑得快。
10、SQL语句的本质
标量子查询可以改写为外连接（需要注意表与表之间关系，去重），半连接可以改写为内连接（需要注意表与表之间关系，去重），反连接可以改写为外连接（不需要注意表与表之间关系，也不需要去重）。SQL语句中几乎所有的子查询都能改写为表连接的方式，所以我们提出这个观点：SQL语句其本质就是表连接（内连接与外连接），以及表与表之间是几比几关系再加上GROPU BY。
862
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
