Oracle数据库Nested Loop Join详解：原理、适用场景与11g向量I/O优化

嵌套循环连接（Nested Loop Join）是一种两个表在做表连接时依靠两层嵌套循环（分为外层循环和内存循环）来得到连接结果集的表连接方法。

如果两个表（这里将它们分别命名为表 T1 和表 T2）在做表连接时使用的是嵌套循环连接，则 Oracle 会依次顺序执行如下步骤。

1. 首先，优化器会按照一定的规则来决定表 T1 和 T2 中谁是驱动表、谁是被驱动表。驱动表用于外层循环，被驱动表用于内层循环。这里假设驱动表是 T1，被驱动表是 T2。
2. 接着以目标 SQL 中指定的谓词条件（如果有的话）去访问驱动表 T1，访问驱动表 T1 后得到的结果集我们记为驱动结果集 1。
3. 然后遍历驱动结果集 1 并同时遍历被驱动表 T2，即先取出驱动结果集 1 中的第 1 条记录，接着遍历被驱动表 T2 并按照连接条件去判断 T2 中是否存在匹配的记录，然后再取出驱动结果集 1 中的第 2 条记录，按照同样的连接条件再去遍历被驱动表 T2 并判断 T2 中是否还存在匹配的记录。直到遍历完驱动结果集 1 中所有的记录为止。这里的外层循环是指遍历驱动结果集 1 所对应的循环，内层循环是指遍历被驱动表 T2 所对应的循环。显然，外层循环所对应的驱动结果集 1 有多少条记录，遍历被驱动表 T2 的内层循环就要有多少次，这就是所谓的“嵌套循环”的含义。

对于嵌套循环的优缺点及适用场景，我们有如下总结。

从上述嵌套循环连接的具体执行过程可以看出：如果驱动表所对应的驱动结果集的记录数较少，同时被驱动表的连接列上又存在唯一性索引（或者在被驱动表的连接列上存在选择性很好的非唯一性索引），那么此时使用嵌套循环连接的执行效率就会非常高；但如果驱动表所对应的驱动结果集的记录数很多，即便在被驱动表的连接列上存在索引，此时使用嵌套循环连接的执行效率也不会很高。

只要驱动结果集的记录数较少，那就具备了做嵌套循环连接的前提条件，而驱动结果集是在对驱动表应用了目标 SQL 中指定的谓词条件（如果有的话）后得到的结果集，所以大表也可以作为嵌套循环连接的驱动表，关键看目标 SQL 中指定的谓词条件（如果有的话）能否将驱动结果集的数量降下来。

嵌套循环连接还有其它连接方法所没有的一个优点：嵌套循环连接可以实现快速响应，即它可以第一时间先返回已经连接过且满足连接条件的记录，而不必等待所有的连接操作全部做完后才返回连接结果。虽然排序合并连接和哈希连接也可以先返回已经连接过且满足连接条件的记录，而不必等待所有的连接操作都做完，但它们并不是第一时间返回，因为排序合并连接要等到排完序后做合并操作时才能开始返回数据，而哈希连接则要等到驱动结果集所对应的 Hash Table 全部建完后才能开始返回数据。

如果 Oracle 使用的是嵌套循环连接，且在被驱动表上的连接列上存在索引，那么 Oracle 在访问该索引时通常会使用单块读，这意味着嵌套循环连接的驱动结果集有多少条记录，Oracle 就需要访问该索引多少次。另外，如果目标 SQL 中的查询列并不能全部从被驱动表的相关索引中获得，那么 Oracle 在做完嵌套循环连接后，就还需要对被驱动表执行回表操作（即用连接结果集中每一条记录所包含的 ROWID 去回表获取被驱动表中的相关查询列）。这个回表操作通常也会使用单块读，这意味着做完嵌套循环连接后的连接结果集有多少条记录，Oracle 就需要回表多少次。

对应这种单块读而言，如果待访问的索引块或数据块不在 Buffer Cache 中，Oracle 就需要耗费物理 I/O 去相应的数据文件中获取。显然，在单块读的数量不降低的情况下，如果能减少这种单块读所需要耗费的物理 I/O 数量，那么嵌套循环连接的执行效率也会随之提高。

为了提高嵌套循环连接的执行效率，在 Oracle 11g 中，Oracle 引入了向量 I/O(Vector I/O)。在引入向量 I/O 后，Oracle 就可以将原先一批单块读所需要耗费的物理 I/O 组合起来，然后用一个向量 I/O 去批量处理它们，这样就实现了在单块读的数量不降低的情况下减少这些单块读所需要耗费的物理 I/O 数量，也就提高了嵌套循环连接的执行效率。

向量 I/O 的引入也反映在嵌套循环连接所对应的执行计划上。在 Oracle 11g 中，你会发现明明一次嵌套循环就可以处理完毕的 SQL，但其执行计划的显示内容中嵌套循环的数量却由之前的一个变为了现在的两个。

我们以测试表 T1 和 T2 为例来说明。两个表的内容如下：

SQL> select * from t1;

COL1 COL2
---------- ----------
1 A
2 B
3 C

SQL> select * from t2;

COL2      COL3
---------- ----------
A          A2
B          B2
D          D2

我们在表 T2 的 COL2 上建一个名为 IDX_T2 的索引：

SQL> create index idx_t2 on t2(col2);

Index created.

表 T1、T2 所在 Oracle 数据块的版本为 Oracle 11.2.0.4：

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

范例 SQL 1 为如下所示：

select t1.col1,t1.col2,t2.col3 from t1,t2 where t1.col2=t2.col2;

为了测试的准确，先收集一下两个表的统计信息：

SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T1',estimate_percent=>100,cascade=>true,method_opt=>'for all columns size 1');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T2',estimate_percent=>100,cascade=>true,method_opt=>'for all columns size 1');

PL/SQL procedure successfully completed.

现在在范例 SQL 1 中加入让其走嵌套循环的 Hint（即 /*+ orderd use_nl(T2) */），并实际执行一次：

SQL> set autotrace traceonly
SQL> select /*+ ordered use_nl(t2) */t1.col1,t1.col2,t2.col3 from t1,t2 where t1.col2=t2.col2;

Execution Plan
----------------------------------------------------------
Plan hash value: 1054738919

---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |     3 |    30 |     6   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |        |     3 |    30 |     6   (0)| 00:00:01 |
|   2 |   NESTED LOOPS               |        |     3 |    30 |     6   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL         | T1     |     3 |    15 |     3   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | IDX_T2 |     1 |       |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| T2     |     1 |     5 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T1"."COL2"="T2"."COL2")

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         13  consistent gets
          0  physical reads
          0  redo size
        718  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

注意，上述执行计划的显示内容中关键字“NESTED LOOPS”出现了两次，这说明在 Oracle 11gR2 中执行上述 SQL 时确实用了两次嵌套循环连接。这里第二次嵌套循环连接的被驱动表部分所对应的执行步骤是“TABLE ACCESS BY INDEX ROWID| T2”，这可能是因为 Oracle 想表达此时在通过 ROWID 回表访问表 T2 时是把一批 ROWID 组合起来后通过一个向量 I/O 批量回表，而不是每拿到一个 ROWID 就用一次单块读回表。

我们现在在范例 SQL 1 中加入 OPTIMIZER_FEATURES_ENABLE Hint（即 optimizer_features_enable('9.2.0')）后再次执行：

SQL> select /*+ optimizer_features_enable('9.2.0') ordered use_nl(t2) */t1.col1,t1.col2,t2.col3 from t1,t2 where t1.col2=t2.col2;

Execution Plan
----------------------------------------------------------
Plan hash value: 2253255382

----------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost  |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     3 |    30 |     5 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2     |     1 |     5 |     1 |
|   2 |   NESTED LOOPS              |        |     3 |    30 |     5 |
|   3 |    TABLE ACCESS FULL        | T1     |     3 |    15 |     2 |
|*  4 |    INDEX RANGE SCAN         | IDX_T2 |     1 |       |       |
----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T1"."COL2"="T2"."COL2")

Note
-----
   - cpu costing is off (consider enabling it)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         12  consistent gets
          0  physical reads
          0  redo size
        718  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

上述 OPTIMIZER_FEATURES_ENABLE Hint 的作用是让解析 SQL 1 的优化器的版本回退到 Oracle 9iR2，这样我们就可以观察到同一个 SQL 在 Oracle 11g 之前执行嵌套循环连接时的情况。

注意，上述执行计划的显示内容中关键字“NESTED LOOPS”只出现来一次，这说明在 Oracle 9iR2 中执行上述 SQL 时确实只用了一次嵌套循环连接。

通过以上对比，可以清晰地看到从 Oracle 11g 开始，为了提升 数据库性能（特别是嵌套循环连接中涉及大量单块读回表时的效率），Oracle 引入了向量 I/O 机制，并在执行计划上有了直观的体现。理解这一机制对于进行深层次的 SQL 优化 非常有帮助。如果您想了解更多数据库内核原理或性能调优技巧，欢迎在云栈社区与更多技术爱好者交流探讨。