表（结果集）与表（结果集）之间的连接方式非常重要，如果 CBO（Cost-Based Optimization） 选择了错误的连接方式，本来几秒就能出结果的 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 性能极差，所以两表关联返回少量数据才能走嵌套循环。

我们在测试账号 scott 中运行如下 SQL。

  1. 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;

Screen Shot 2021-11-04 at 9.40.34 AM.png

我们运行下面命令获取带有 A-TIME 的执行计划。

  1. select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
  1. PLAN_TABLE_OUTPUT
  2. -------------------------------------------------------------------------------------------------
  3. SQL_ID  g374au8y24mw5, child number 0
  4. -------------------------------------
  5. select /*+ gather_plan_statistics use_nl(e,d) leading(e) */  e.ename,
  6. e.job, d.dname   from emp e, dept d  where e.deptno = d.deptno
  8. Plan hash value: 3625962092
  10. -------------------------------------------------------------------------------------
  11. | Id | Operation                    |Name   |Starts|E-Rows|A-Rows|  A-Time   |Buffers|
  12. -------------------------------------------------------------------------------------
  13. |  0 | SELECT STATEMENT             |       |     1|      |    14|00:00:00.01|   26 |
  14. |  1 |  NESTED LOOPS                |       |     1|      |    14|00:00:00.01|   26 |
  15. |  2 |   NESTED LOOPS               |       |     1|    15|    14|00:00:00.01|   12 |
  16. |  3 |    TABLE ACCESS FULL         |EMP    |     1|    15|    14|00:00:00.01|    8 |
  17. |* 4 |    INDEX UNIQUE SCAN         |PK_DEPT|    14|     1|    14|00:00:00.01|    4 |
  18. |  5 |   TABLE ACCESS BY INDEX ROWID|DEPT   |    14|     1|    14|00:00:00.01|   14 |
  19. -------------------------------------------------------------------------------------
  21. Predicate Information (identified by operation id):
  22. ---------------------------------------------------
  24. 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 代码实现。

  1. declare
  2.   cursor cur_emp is
  3.     select ename, job, deptno from emp;
  4.   v_dname dept.dname%type;
  5. begin
  6.   for x in cur_emp loop
  7.     select dname into v_dname from dept where deptno = x.deptno;
  8.     dbms_output.put_line(x.ename || ' ' || x.job || ' ' || v_dname);
  9.   end loop;
  10. end;

游标 cur_emp 就相当于驱动表 EMP，扫描了一次，一共返回了 14 条记录。该游标循环了 14 次，每次循环的时候传值给 dept，dept 被扫描了 14 次。

为什么嵌套循环被驱动表的连接列要创建索引呢？我们注意观察加粗部分的 PLSQL 代码。

  1. declare
  2.   cursor cur_emp is
  3.     select ename, job, deptno from emp;
  4.   v_dname dept.dname%type;
  5. begin
  6.   for x in cur_emp loop
  7.     select dname into v_dname from dept where deptno = x.deptno;
  8.     dbms_output.put_line(x.ename || ' ' || x.job || ' ' || v_dname);
  9.   end loop;
  10. end;

因为扫描被驱动表 dept 次数为 14 次，每次需要通过 deptno 列传值，所以嵌套循环被驱动表的连接列需要创建索引。

虽然本书不讲 PLSQL 优化，但是笔者见过太多的 PLSQL 垃圾代码，因此，提醒大家，在编写 PLSQL 的时候，尽量避免游标循环里面套用 SQL，因为那是纯天然的嵌套循环。假如游标返回 100 万行数据，游标里面的 SQL 会被执行 100 万次。同样的道理，游标里面尽量不要再套游标，如果外层游标循环 1 万次，内层游标循环 1 万次，那么最里面的 SQL 将被执行一亿次。

当两表使用外连接进行关联，如果执行计划是走嵌套循环，那么这时无法更改驱动表，驱动表会被固定为主表，例如下面 SQL。

  1. 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;
  1. Explained.
  1. select * from table(dbms_xplan.display);
  1. PLAN_TABLE_OUTPUT
  2. ---------------------------------------------------------------------------
  3. Plan hash value: 2022884187
  5. ---------------------------------------------------------------------------
  6. | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
  7. ---------------------------------------------------------------------------
  8. |   0 | SELECT STATEMENT   |      |    14 |   812 |     8   (0)| 00:00:01 |
  9. |   1 |  NESTED LOOPS OUTER|      |    14 |   812 |     8   (0)| 00:00:01 |
  10. |   2 |   TABLE ACCESS FULL| DEPT |     4 |    80 |     3   (0)| 00:00:01 |
  11. |*  3 |   TABLE ACCESS FULL| EMP  |     4 |   152 |     1   (0)| 00:00:01 |
  12. ---------------------------------------------------------------------------
  14.    3 - filter("D"."DEPTNO"="E"."DEPTNO"(+))
  16. 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。

  1. 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;
  1. 11 rows selected.
  3. Execution Plan
  4. ----------------------------------------------------------
  5. Plan hash value: 351108634
  7. -------------------------------------------------------------------------------------
  8. | Id |Operation                    | Name    | Rows  | Bytes | Cost(%CPU)| Time     |
  9. -------------------------------------------------------------------------------------
  10. |  0 |SELECT STATEMENT             |         |    12 |   696 |   15   (0)| 00:00:01 |
  11. |  1 | NESTED LOOPS                |         |    12 |   696 |   15   (0)| 00:00:01 |
  12. |* 2 |  TABLE ACCESS FULL          | EMP     |    12 |   456 |    3   (0)| 00:00:01 |
  13. |  3 |  TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |    1   (0)| 00:00:01 |
  14. |* 4 |   INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |    0   (0)| 00:00:01 |
  15. -------------------------------------------------------------------------------------
  17. Predicate Information (identified by operation id):
  18. ---------------------------------------------------
  20.    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 表只需要查询出表中最多几百行数据（嵌套循环（NESTED LOOPS） - 图2）。一般情况下，一个小表与一个大表关联，我们可以考虑让小表 NL 大表，大表走连接列索引（如果大表有过滤条件，需要将过滤条件与连接列组合起来创建组合索引），从而避免大表被全表扫描。

最后，为了加深对嵌套循环的理解，大家可以在 SQLPLUS 中依次运行以下脚本，观察 SQL 执行速度，思考 SQL 为什么会执行缓慢：

  1. create table a as select * from dba_objects;
  2. create table b as select * from dba_objects;
  3. set timi on
  4. set lines 200 pages 100
  5. set autot trace
  6. select /*+ use_nl(a,b) */ * from a,b where a.object_id=b.object_id;