数据库 · · By/蜜汁炒酸奶

【译】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条记录。该表看起来如下:

oracle_tuning_nestedloopouterjoin_01.png

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

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

oracle_tuning_nestedloopouterjoin_02.png

第一列(INDEX):按升序存储empid列的所有值。

第二列(ROWID):存储对应记录的ROWID

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

oracle_tuning_nestedloopouterjoin_03.png

针对这些表启动查询,要求显示所有员工的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 表中没有更多记录要处理,因此退出。

因此,输出结果将如下所示:

oracle_tuning_nestedloopouterjoin_04.png

现在,解释执行计划表将看起来如下:

-------------------------------------------------------------------------------------------------------- | 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 tableBonus 被视为被驱动表driven table

译者附录

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

​ —《基于Oracle的SQL优化》

扩展

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

评论已关闭

example
C
蜜汁炒酸奶

当前处于试运行期间,可能存在不稳定情况,敬请见谅。

欢迎点击此处反馈访问过程中出现的问题