【译】Oracle调优技巧21：Nested Loop Outer Join

查找Oracle相关优化过程中，遇到这么篇文章，感觉解释的简单易懂，翻译过来分享给大家。原文若无法直接打开，请自行通过梯子试试。

原文：Nested Loop Outer Join : Oracle Tuning Tip#21

作者：kali

话题

Nested Loop Outer Join（嵌套循环外连接）

定义

在我们详细讨论该话题之前，我们先了解一下外连接的含义。

举个栗子，取两个必须连接的表，名为“Parent”表和“Child”表（我取这些表名，仅是为了能轻松的讨论该话题）。

如果 Child 表与 Parent 表外连接，那么可以说，将检索从两个表均能匹配到的记录以及在 Parent 表中匹配到而未在Child 表中未匹配到的记录。这被称为 outer join (外连接)。[在自然联接 natural join 或 等值联接 equi-join 中，将仅检索能 同时在两个表中匹配到的记录。]

为了执行外连接，Oracle 提供了3种不同的连接技术：

• Nested loop outer join 嵌套循环外连接
• Hash outer join 哈希散列外连接
• Sort merge outer join 排序合并外连接

在此，我们仅讨论嵌Nested loop outer join （译注：嵌套循环外连接，本文之后讨论中仅留其英文名词Nested loop outer join ，不再中英共存）。

为了执行Nested loop outer join ，Oracle 会执行如下步骤（假设 Child 表必须和 Parent 表外连接）：

1. 优化器选择 Parent 表作为外部表( outer table )或者驱动表( driving table )，Child 表作为内部表（ inner table ）或者被驱动表（ driven table ）。
2. 对于驱动表driving table 中的每一行，如果被驱动表 driven table 中存在匹配记录，将返回（在查询select 中提到）两个表的列（对应的记录）。
3. 如果在被驱动表 driven table中不存在匹配记录，则仅返回查询中提到 的 Parent 表的列（对应的记录），Child 表对应的列返回 NULL 。

如您所知，在nested loop equi-join 中，决定哪个表将成为驱动表是决定因素。通常，两个表中的小表将被视为驱动表，这样成本cost 就低了。[成本将会降低是因为只需执行最小的循环次数]。然而，在nested loop outer join 中，由于默认情况下将选择 Parent 表作为驱动表，而不管它是小表还是大表，因此决策过程变得无关紧要。原因是，连接条件决定了哪个表将成为 nested loop outer join 中的驱动表，而不是成本cost 。后者cost 是用于确定 nested loop equi-join 中驱动表的度量。

这种方式的逻辑活动图如下所示(译注：伪PL/SQL循环，感觉作者在IF-ELSE 条件中隐藏了对被驱动表中所需rows的循环)：

Loop(驱动表中所有所需rows)
	IF(被驱动表中可获得匹配记录) THEN
		输出匹配记录，并显示查询中提到的两个表的列;
	ELSE(被驱动表中不存在匹配记录) THEN
		始终输出驱动表中的该失配记录（显示查询中提及的驱动表的列），并且在查询中提及的被驱动表中的列显示为NULL;
	END IF;
END LOOP;

需要谨记的鲜为人知的事实

• /*+ USE_NL(<<inner table>>) */ 是可以用来强制使用该nested loop outer join 的 hint。
• 当表由外连接"(+)"运算符连接时，Oracle选择此方式。
• 当parent 表是小表，Child 表是大表且Child 表与 parent 表外连接时，这将非常成功。

优点

• 如果你想尽可能快的看到初始匹配记录（尽管提到了外连接），这是最好的方法。原因是，按照逻辑，虽然它有点迭代结构 iterative structure ,匹配的记录将在每次迭代中显示。
• 如果parent 表很小，循环将以最小的次数执行完成且这种技术将高效工作。

缺点

• 当选择连接两个大表时，您可能会很快看到初始匹配记录，但是显示最终匹配记录会花费很多时间。
• 这是一个非常耗资源的过程（尤其是CPU的大量使用），因为它具有迭代结构。

如何验证

如何验证在执行SQL查询时，Oracle是否遵循 nested loop outer join 执行计划。若查询遵循，就会发现类似的执行计划：

-------------------------------------------------------------------------------------------------------
| Id  | Operation                                          | Name                       | Rows       |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                   |                            |            |
|   1 |    NESTED LOOPS (OUTER)                            |                            |            |
|   2 |       TABLE ACCESS (FULL)                          | <<parent table’s name>>    |            |
|   3 |       TABLE ACCESS (FULL)                          | <<child table’s name>>     |            |

在执行计划中，只要选择此方式，就会在 Operation列显示关键字 NESTED LOOPS (OUTER) 。紧随该单词之后立即显示的表名就是驱动表（如上面执行计划id=2中的<<parent table’s name>> ），这就是为什么您会发现在父表的名称之后紧随子表的名称。

示例

为了理解nested loop outer join 的机制与 nested loop equi-join机制的不同，我以在此使用（与 Tip#18）相同示例为例，进行了一些修改并强制使用外连接。

创建一个员工表emp 并插入10条记录。该表看起来如下：

在此表上为empid 列创建唯一索引（create unique index emp_no_indx on emp(empid)）。

索引表在逻辑上看起来像这样：

第一列（INDEX）：按升序存储empid列的所有值。

第二列（ROWID）：存储对应记录的ROWID。

然后创建另一个名为奖金的表 bonus ，该表仅存储有关获得奖金的员工以及奖金数额的信息。表看起来像这样：

针对这些表启动查询，要求显示所有员工的name、salary、bonus（如果他们有得到的话），所以该查询将像这样：

Select emp.empname,emp.salary,bonus.bonus_amt
from emp, bonus
where emp.empid = bonus.empid(+);

根据这个查询，emp 表是父表，bonus是子表，因为后者bonus.empid(+)与前者外连接。

因此，Oracle将emp 作为驱动表，bonus 作为被驱动表，尽管后者没有可用的索引。这就是 成本不是决定因素，连接条件是 nested loop outer join的决定因素的原因。

根据 nested loop outer join 的设计，执行此查询时，请遵循以下步骤：

1. Oracle 选择 emp 作为驱动表，并选择bonus 作为被驱动表。
2. 获取emp 表的第一条记录，即empid:5 。
3. hits bonus 表，并尝试找到与 empid:5 相匹配的记录。
4. 由于在bonus 表中没有 empid:5 ，因此将显示emp 表中的Empname 和salary 列，而Bonus_amt 列则不显示任何内容。
5. 获取emp 表的下一条记录，即empid:10 。
6. hits bonus 表，并尝试找到与 empid:10 相匹配的记录。
7. 由于在bonus 表中没有 empid:10 ，因此将显示emp 表中的Empname 和salary 列，而Bonus_amt 列则不显示任何内容。
8. 获取emp 表的下一条记录，即empid:9 。
9. hits bonus 表，并尝试找到与 empid:9 相匹配的记录。
10. 由于在bonus 表中有 empid:9 ，因此将显示emp 表中的Empname 和salary 列，并显示bonus 表中的Bonus_amt 列。
11. 获取emp 表的下一条记录，即empid:4 。
12. hits bonus 表，并尝试找到与 empid:4 相匹配的记录。
13. 由于在bonus 表中没有 empid:4 ，因此将显示emp 表中的Empname 和salary 列，而Bonus_amt 列则不显示任何内容。
14. 获取emp 表的下一条记录，即empid:6 。
15. hits bonus 表，并尝试找到与 empid:6 相匹配的记录。
16. 由于在bonus 表中有 empid:6 ，因此将显示emp 表中的Empname 和salary 列，并显示bonus 表中的Bonus_amt 列。
17. 获取emp 表的下一条记录，即empid:7 。
18. hits bonus 表，并尝试找到与 empid:7 相匹配的记录。
19. 由于在bonus 表中没有 empid:7 ，因此将显示emp 表中的Empname 和salary 列，而Bonus_amt 列则不显示任何内容。
20. 获取emp 表的下一条记录，即empid:1 。
21. hits bonus 表，并尝试找到与 empid:1 相匹配的记录。
22. 由于在bonus 表中没有 empid:1 ，因此将显示emp 表中的Empname 和salary 列，而Bonus_amt 列则不显示任何内容。
23. 获取emp 表的下一条记录，即empid:8 。
24. hits bonus 表，并尝试找到与 empid:8 相匹配的记录。
25. 由于在bonus 表中没有 empid:8 ，因此将显示emp 表中的Empname 和salary 列，而Bonus_amt 列则不显示任何内容。
26. 获取emp 表的下一条记录，即empid:2 。
27. hits bonus 表，并尝试找到与 empid:2 相匹配的记录。
28. 由于在bonus 表中没有 empid:2 ，因此将显示emp 表中的Empname 和salary 列，而Bonus_amt 列则不显示任何内容。
29. 获取emp 表的下一条记录，即empid:3 。
30. hits bonus 表，并尝试找到与 empid:3 相匹配的记录。
31. 由于在bonus 表中有 empid:3 ，因此将显示emp 表中的Empname 和salary 列，并显示bonus 表中的Bonus_amt 列。
32. 由于emp 表中没有更多记录要处理，因此退出。

因此，输出结果将如下所示：

现在，解释执行计划表将看起来如下：

--------------------------------------------------------------------------------------------------------
| Id  | Operation                                               | Name                    | Rows       |
--------------------------------------------------------------------------------------------------------
|   4 | SELECT STATEMENT                                        |                         |     10     |
|   3 |   NESTED LOOPS (OUTER)                                  |                         |     10     |
|   1 |     TABLE ACCESS (FULL)                                 | EMP                     |     10     |
|   2 |     TABLE ACCESS (FULL)                                 | BONUS                   |     4      |

从解释计划看，我们可以说这两个表是通过 nested loop outer join 的方式外连接的。Emp表被视为驱动表driving table ，Bonus 被视为被驱动表driven table。

译者附录

1. Hint 实际上是个特殊的注释，它以一种固定的格式和位置出现在 SQL 语句的 SQL 文本中，它可以影响优化器对于执行计划的选择，但这种影响不是强制性的，优化器在某些情况下可能会忽略目标 SQL 中的 Hint，即使这个 Hint 在语法和语义上都是有效的。

​ —《基于Oracle的SQL优化》

扩展

1. 关系代数运算_关系代数
2. 多表连接的三种方式详解 hash join、merge join、 nested loop