Oracle 19C 嵌套循环联接

• 当优化器考虑嵌套循环联接时，当数据库联接小的数据子集，数据库将优化器模式设置为的大型数据集联接时FIRST_ROWS，或者联接条件是访问内部表的有效方法时，嵌套循环联接非常有用。
• 嵌套循环如何连接 ：从概念上讲，嵌套循环等效于两个嵌套for循环。
• 嵌套的嵌套循环嵌套循环 ：外部循环本身可以是由不同的嵌套循环生成的行源。
• 嵌套循环的最新实现加入 ：Oracle数据库11g引入了嵌套循环的新实现，从而减少了物理I / O的总体延迟。
• 嵌套循环联接的原始实现 :  在当前版本中，嵌套循环的新实现和原始实现都是可能的。
• 嵌套循环控件  :  对于某些SQL语句，数据足够小，以使优化程序更喜欢全表扫描和哈希联接。但是，您可以添加USE_NL提示，以指示优化器将指定的表用作内部表，从而将每个指定的表连接到具有嵌套循环连接的另一个行源。

FOR erow IN (select * from employees where X=Y) LOOP
  FOR drow IN (select * from departments where erow is matched) LOOP
    output values from erow and drow
  END LOOP
END LOOP

内部循环对外部循环的每一行执行。该employees表是“外部”数据集，因为它在外部for循环中。外部桌子有时被称为驾驶台。该departments表是“内部”数据集，因为它位于内部for循环中。

嵌套循环联接涉及以下基本步骤：

1. 优化器确定驱动行源并将其指定为外部循环。
   

   外循环产生一组行来驱动连接条件。行源可以是使用索引扫描，全表扫描或任何其他生成行的操作访问的表。

   内部循环的迭代次数取决于在外部循环中检索到的行数。例如，如果从外部表中检索了10行，则数据库必须在内部表中执行10个查找。如果从外部表中检索了10,000,000行，则数据库必须在内部表中执行10,000,000查找。

2. 优化器将另一行源指定为内部循环。
   

   在执行计划中，外循环出现在内循环之前，如下所示：

   NESTED LOOPS 
     outer_loop
     inner_loop 
   

3. 对于客户端的每个获取请求，基本过程如下：
   
   1. 从外部行源获取一行
   2. 探查内部行源以查找与谓词条件匹配的行
   3. 重复前面的步骤，直到通过获取请求获得所有行

   有时，数据库会对rowid进行排序以获得更有效的缓冲区访问模式。

SELECT STATEMENT
  NESTED LOOPS 3
    NESTED LOOPS 2          - Row source becomes OUTER LOOP 3.1
      NESTED LOOPS 1        - Row source becomes OUTER LOOP 2.1
        OUTER LOOP 1.1
        INNER LOOP 1.2  
      INNER LOOP 2.2
    INNER LOOP 3.2

数据库按以下顺序对循环进行排序：

1. 数据库通过NESTED LOOPS 1以下方式进行迭代：
   

   NESTED LOOPS 1 
     OUTER LOOP 1.1
     INNER LOOP 1.2
   

   的输出NESTED LOOP 1是行源。

2. 数据库NESTED LOOPS 2使用生成的行源NESTED LOOPS 1作为其外部循环，通过进行迭代：
   

   NESTED LOOPS 2       
     OUTER LOOP 2.1         - Row source generated by NESTED LOOPS 1
     INNER LOOP 2.2 
   

   的输出NESTED LOOPS 2是另一个行源。

3. 数据库NESTED LOOPS 3使用生成的行源NESTED LOOPS 2作为其外部循环，通过进行迭代：
   

   NESTED LOOPS 3      
     OUTER LOOP 3.1         - Row source generated by NESTED LOOPS 2
     INNER LOOP 3.2

-------------------------------------------------------------------------------------
| Id | Operation                    | Name              |Rows|Bytes|Cost%CPU| Time  |
-------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT             |                   | 19 | 722 |  3 (0)|00:00:01|
|  1 |  NESTED LOOPS                |                   |    |     |       |        |
|  2 |   NESTED LOOPS               |                   | 19 | 722 |  3 (0)|00:00:01|
|* 3 |    TABLE ACCESS FULL         | DEPARTMENTS       |  2 |  32 |  2 (0)|00:00:01|
|* 4 |    INDEX RANGE SCAN          | EMP_DEPARTMENT_IX | 10 |     |  0 (0)|00:00:01|
|  5 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES         | 10 | 220 |  1 (0)|00:00:01|     <--在回表时，在次通过NL 可以批量提取数据
-------------------------------------------------------------------------------------      使用向量IO后，ORACLE将原先一批单块读所需要耗费的物理IO组合起来    
                                                                                           然后用一个向量IO去批量处理它们，
Predicate Information (identified by operation id):                                        这样实现了单块读数量不降低的情况下，减少这些单块读所需要耗费的物理IO数量，也就提交 了NL的执行效率 
---------------------------------------------------
   3 - filter("D"."DEPARTMENT_NAME"='Marketing' OR "D"."DEPARTMENT_NAME"='Sales')
   4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

在这种情况下，hr.departments表中的行构成内部嵌套循环（步骤2）的外部行源（步骤3）。索引emp_department_ix是内部嵌套循环的内部行源（步骤4）。内部嵌套循环的结果形成外部嵌套循环（行1）的外部行源（行2）。该hr.employees表是外部嵌套循环的外部行源（行5）。

对于每个获取请求，基本过程如下：

1. 数据库遍历内部嵌套循环（第2步）以获取提取中请求的行：
   
   1. 数据库读取的第一行，departments以获取名为Marketing或的部门的部门ID Sales（步骤3）。例如：
      

      Marketing,20
      

      该行集是外部循环。数据库将数据缓存在PGA中。

   2. 数据库进行扫描emp_department_ix（这是employees表上的索引）以查找employees与此部门ID对应的行ID（步骤4），然后将结果合并（步骤2）。
      

      结果集具有以下形式：

      Marketing,20,employees_rowid
      Marketing,20,employees_rowid
      Marketing,20,employees_rowid
      

   3. 数据库读取的下一行departments，进行扫描emp_department_ix以查找employees与此部门ID相对应的rowid，然后遍历循环直到满足客户请求。
      

      在此示例中，数据库仅对外部循环进行两次迭代，因为只有两行来自departments满足谓词过滤器。从概念上讲，结果集具有以下形式：

      Marketing,20,employees_rowid
      Marketing,20,employees_rowid
      Marketing,20,employees_rowid
      .
      .
      .
      Sales,80,employees_rowid
      Sales,80,employees_rowid
      Sales,80,employees_rowid
      .
      .
      .
      

      这些行成为外部嵌套循环的外部行源（步骤1）。该行集被缓存在PGA中。

2. 数据库组织在上一步中获得的rowid，以便它可以更有效地在缓存中访问它们。
3. 数据库开始通过外部嵌套循环进行迭代，如下所示：
   
   1. 数据库从上一步获得的行集中检索第一行，如以下示例所示：
      

      Marketing,20,employees_rowid
      

   2. 数据库使用rowid从中检索行employees以获得请求的值（步骤1），如以下示例所示：
      

      Michael,Hartstein,13000,Marketing
      

   3. 数据库从行集中检索下一行，使用rowid探测employees匹配的行，然后遍历循环直到检索到所有行。
      

      结果集具有以下形式：

      Michael,Hartstein,13000,Marketing
      Pat,Fay,6000,Marketing
      John,Russell,14000,Sales
      Karen,Partners,13500,Sales
      Alberto,Errazuriz,12000,Sales
      .
      .
      .
      

在某些情况下，未分配第二个连接行源，并且执行计划看起来与Oracle Database 11g之前的执行计划相同。以下列表描述了这种情况：

• 联接内部所需的所有列都在索引中，并且不需要表访问。在这种情况下，Oracle数据库仅分配一个联接行源。
• 返回的行的顺序可能与Oracle Database 12c之前的版本中返回的顺序不同。因此，当Oracle数据库尝试保留行的特定顺序（例如为了消除对ORDER BY排序的需要）时，Oracle数据库可能会将原始实现用于嵌套循环连接。
• 该OPTIMIZER_FEATURES_ENABLE初始化参数设置为之前释放Oracle数据库11g。在这种情况下，Oracle数据库将原始实现用于嵌套循环连接。

SELECT e.first_name, e.last_name, e.salary, d.department_name
FROM   hr.employees e, hr.departments d
WHERE  d.department_name IN ('Marketing', 'Sales')
AND    e.department_id = d.department_id;

在Oracle Database 11g之前的版本中，此查询的执行计划可能如下所示：

------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)| Time    |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   |    19 |   722 |     3  (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |    10 |   220 |     1  (0)| 00:00:01 |     <--传统的方式，是每回一行， 提取一行
|   2 |   NESTED LOOPS              |                   |    19 |   722 |     3  (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL        | DEPARTMENTS       |     2 |    32 |     2  (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN         | EMP_DEPARTMENT_IX |    10 |       |     0  (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("D"."DEPARTMENT_NAME"='Marketing' OR "D"."DEPARTMENT_NAME"='Sales')
   4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

1. 数据库遍历循环以获取提取中请求的行：
   
   1. 数据库读取的第一行，departments以获取名为Marketing或的部门的部门ID Sales（步骤3）。例如：
      

      市场营销20
      

      该行集是外部循环。数据库将行缓存在PGA中。

   2. 数据库进行扫描emp_department_ix（这是该employees.department_id列的索引），以查找employees与此部门ID相对应的行ID（步骤4），然后将结果合并（步骤2）。
      

      从概念上讲，结果集具有以下形式：

      Marketing,20,employees_rowid
      Marketing,20,employees_rowid
      Marketing,20,employees_rowid
      

   3. 数据库读取的下一行departments，进行扫描emp_department_ix以查找employees与此部门ID相对应的rowid，并在循环中进行迭代，直到满足客户请求为止。
      

      在此示例中，数据库仅对外部循环进行两次迭代，因为只有两行来自departments满足谓词过滤器。从概念上讲，结果集具有以下形式：

      Marketing,20,employees_rowid
      Marketing,20,employees_rowid
      Marketing,20,employees_rowid
      .
      .
      .
      Sales,80,employees_rowid
      Sales,80,employees_rowid
      Sales,80,employees_rowid
      .
      .
      .
      

2. 根据情况，数据库可以组织在上一步中获得的缓存行标识符，以便它可以更有效地访问它们。
3. 对于employees由嵌套循环生成的结果集中的每个rowid，数据库从中检索一行employees以获得请求的值（步骤1）。
   

   因此，基本过程是读取rowid并检索匹配的employees行，读取下一个rowid并检索匹配的employees行，依此类推。从概念上讲，结果集具有以下形式：

   Michael,Hartstein,13000,Marketing
   Pat,Fay,6000,Marketing
   John,Russell,14000,Sales
   Karen,Partners,13500,Sales
   Alberto,Errazuriz,12000,Sales
   .
   .
   .