Oracle优化器之解释执行计划译文（三）

本文基于Oracle 12c优化器官方文档进行了翻译，原文链接如下：
The Oracle Optimizer Explain the Explain Plan

访问路径（Access Method）

  访问方法（或访问路径）用于描述优化器如何从每个表（或索引）中访问数据。访问路径展示在执行计划的Operation字段中。

图13 访问路径可以从执行计划Operation列中查看到

  Oracle中存在9种常见的访问路径，以下进行详细说明。

全表扫描（Full Table Scan）

  从表中读取所有行，并过滤不符合Where子句谓词的行。全表扫描会使用多块IO（通常为1MB大小的IOs）。在需要访问表中的大部分行，或不存在索引、或不能使用现有的索引、或预估成本最低的情况下，优化器会选择全表扫描。优化器是否会选择全表扫描也受到以下因素的影响：

• Init.ora配置文件中的参数db_multi_block_read_count
• 并行度
• 提示（Hint）
• 缺少可使用的索引
• 使用索引成本偏高

通过ROWID访问（Table Access by ROWID）

  数据行的rowid指定数据文件、文件中的数据块和块中的数据行位置。优化器会从Where子句谓词或通过对一个或多个表索引的扫描获得rowids，然后再根据其rowid定位表中每个选定的行，并进行逐行访问。

索引唯一扫描（Index Unique Scan）

  索引唯一扫描中将仅返回一行数据。当一个惟一（B-tree）索引上存在等式谓词，或索引是由主键约束产生时，Oracle将采用这种访问路径。

图14 使用Index unique scan的执行计划

索引范围扫描（Index Range Scan）

  索引范围扫描指的是优化器在访问索引时会一并访问相邻的索引项，然后使用索引中的ROWID值从表中访问相应的数据行。索引范围扫描可以是有界的，也可以是无界的。当语句在非唯一索引上有等式谓词，或在唯一索引上有不相等或范围谓词（如=，<，>，非前导列的Like）时，Oracle优化器将使用索引范围扫描。数据行将按索引列的升序返回。

图15 使用Index range scan的执行计划

  索引范围扫描-逆序（Index range scan descending）在概念上与索引范围扫描相同，区别在于当存在ORDER BY...DESCENDING，且降序条件可以由索引来满足时，Oracle将会使用这种访问路径。

索引跳跃扫描（Index Skip Scan）

  通常，为了能使用到索引，查询条件中必须包含索引的前导列（即复合索引中靠前的索引列）。但是，如果在查询语句中引用了索引中除第一列之外的所有其他列，Oracle可以进行索引跳跃扫描，跳过索引的第一列而使用其余的列。如果复合索引的第一列只存在少量不同值，而在索引的其他列中存在许多不同的值，那么使用索引跳跃扫描将会提高查询效率。

索引全扫描（Index Full Scan）

  与其字面含义不同，索引全扫描并不会扫描索引结构中的每个块。索引全扫描会扫描一个索引的所有叶子块，并只需处理足够数量的分支块直到找到第一个叶子块。当满足语句所需的所有列都在索引中并且扫描索引成本比扫描表更低时，Oracle就会使用索引全扫描。索引全扫描使用单块IO。在下列任何一种情况中都可能会使用索引全扫描：

• 查询条件中存在Order By，且Order By中涉及到的列与索引（指组合索引）中的列一致（列及列出现的顺序），并不要求所有索引列都存在于Order By中，只出现索引列的子集也可以；
• 查询中可能需要用到Sort Merge Join，并且涉及到的所有列都存在于索引中（Index Full Scan可以消除Sort Merge Join，因为索引列此时是有序的）；
• 查询条件中访问列的顺序与索引结构中的前导列一致；
• 查询条件中存在Group By，且Group By中涉及到的列存在于索引中。

图16 索引全扫描的处理过程

索引快速全扫描（Index Fast Full Scan）

  索引快速全扫描是Table Access Full的替代选择，当索引包含查询所需的所有列，并且索引中至少有一列具有NOT NULL约束时优化器会考虑使用索引快速全扫描。索引快速全扫描不能用于消除排序操作，因为在这种方式下优化器对数据的访问顺序并不遵循索引列已有的顺序。同时，与索引全扫描不同，索引快速全扫描会使用多块读的方式来扫描索引中的所有块（所有分支和叶子块）。

索引连接（Index Join）

  表查询条件中的多个列不单独存在于该表的某个组合索引中，但又可以被该表中包含不同列的索引（单值或复合索引）连接覆盖到，那么优化器可能会考虑使用索引连接这种访问路径。在使用索引连接的情况下，优化器可以避免进行全表扫描。同样需要注意的是，使用索引连接无法消除排序操作。

位图索引（Bitmap Index）

  位图索引为每个键值使用一组比特位，并使用一个映射函数将每个比特位的位置转换为rowid。优化器可以有效地合并与Where子句中的几个谓词对应的位图索引，并使用布尔操作来解析AND和OR操作。

  如果在执行计划中看到的访问路径与我们所期望的不一样，这时候就需要检查对象（表和索引）的基数估计值是否正确，连接顺序是否能够满足我们预期的访问路径。

连接方法（Join Method）

  连接方法描述如何将来自两个数据操作符的数据连接在一起。我们可以通过查看执行计划中的Operation列来确定优化器使用的连接方法。

图17 连接方法可以在Operation列中查看到

  Oracle提供了多种连接方法和连接类型，以下进行详细说明。

哈希连接（Hash Join）

  哈希连接用于连接大型数据集。优化器使用两个表或数据源中较小的一个在内存中基于连接键构建哈希表。然后扫描较大的表，并对连接列执行相同的哈希算法。然后，优化器会探测之前在内存中构建的哈希表中的每个值，如果发现匹配，则返回匹配行。

嵌套循环连接（Nested Loops Join）

  嵌套循环连接适用于连接双方都是小数据集合，并对于其中一个表有比较高效的访问路径（如存在索引查找）的情形。在嵌套循环连接中，对于第一个表（外部表）中的每一行，优化器会通过高效的访问路径去访问第二个表（内部表）中的所有行。我们可以把它看作是两个嵌入的FOR循环。在Oracle 11g数据库中，嵌套循环连接的内部实现进行了更改，以进一步减少物理I/O的总体延迟。因此我们可能会在执行计划的Operation列中看到两个嵌套循环连接（如图18所示），而在11g以前的Oracle版本中，我们只会看到一个嵌套循环连接。

图18 Nested Loop Join示例

排序合并连接（Sort Merge Join）

  当两个表之间的连接条件是非等值连接，例如，<、<=、>或>=时，排序合并连接的成本可能会更低。对于大型数据集合，排序合并连接比嵌套循环连接效率更高。一般来说，排序合并连接包括两个步骤：

• 排序连接操作：连接双方在连接键上进行排序；
• 合并连接操作：将排序后的列表合并起来。

  如果参与连接的两个表中的其中一表存在可以消除该表连接字段排序操作的索引（索引本身有序），那么优化器会倾向于使用排序合并连接。在图19的案例中，优化器只需要对Sales表中的行进行排序（ID 5），而Products表中的行是来自于主键索引（ID 4）已经默认有序。

图19 Sort Merge Join示例

  从Oracle 12c第2个版本开始，我们还可以使用band join。Band join在某些存在between操作的连接中比排序合并连接更高效。

笛卡尔连接（Cartesian Join）

  优化器将来自一个数据源的每一行与来自另一个数据源的每一行连接起来，创建两个集合的笛卡尔积。通常情况下，只有当连接涉及的表数据集很小，或者一个或多个表与其他表都不存在连接条件时，优化器才会选择笛卡尔连接。笛卡尔连接并不常见，因此如果我们在执行计划中看到笛卡尔连接，那么就很有可能是基数估计值出现了问题。严格地来说，笛卡尔连接并不能算作一类特定的连接方法。

图20 Cartesian Join示例

连接类型（Join Type）

  Oracle提供了多种连接类型：内连接、（左）外连接、全外连接、反连接、半连接、分组外连接等。内连接是最常见的连接类型，在执行计划并没有使用关键字INNER来标识内连接。

外连接（Outer Join）

  外连接返回满足连接条件的所有表的行，同时返回不存在(+)标识的表的剩余行。以T1.x = T2.x(+)为例，这里T1是左边的表，它的非连接行将被全部保留。在ANSI标准的外连接语法中，T1是会保留非连接行的主表。同一个例子可以用ANSI标准 SQL写成T1 LEFT OUTER JOIN T2 ON (T1.x = T2.x)。

图21 外连接示例，连接类型总是与一种连接方法匹配，本例中是哈希连接

连接顺序（Join Order）

  连接顺序是指在涉及到多个表的SQL语句中，这些表进行连接操作的顺序。在一个执行计划中确定表的连接顺序，可以通过查看Operation列中表的缩进。在图22中，SALES表和PRODUCTS表是同一级缩进，且它们都比CUSTOMERS表缩进程度更大。所以这里的连接顺序是SALES表和PRODUCTS表首先通过哈希连接进行连接，然后将该连接的结果再与CUSTOMERS表进行哈希连接。

图22 连接顺序示例

  在非常复杂的SQL语句中，通过查看Operation列中表的缩进来确定连接顺序可能会十分困难。在这种情况下，我们可以通过在DBMS_XPLAN中使用FORMAT参数来显示格式化后的包含连接顺序信息的执行计划。例如，为图22所示的执行计划进行格式化操作，操作语句如下所示：

DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'TYPICAL + OUTLINE'));

图23 执行计划输出结果

  在格式化信息中，查找以单词LEADING开头的行。这一行显示了该查询的连接顺序。在这个例子中，可以看到P、S、C在这一行中被引用。这三个字母是查询中三个相关表的别名。P（PRODUCTS）表连接到S（SALES）表，然后连接到C（CUSTOMERS）表。

  连接顺序是根据成本确定的，成本受到基数估计值和访问路径的影响。优化器也在确定表的连接顺序时会遵守一些基本规则：

• 优化器会优先连接返回最多一行记录的表。优化器可以根据Unique及Primary Key约束来确定优先连接的表；
• 当存在外连接时，需要保留所有行的表（即不存在外连接运算符的表）必须位于谓词中的另一个表（存在外连接运算符的表）之后，以确保所有不满足连接条件的其他行都可以被正确添加到结果集中；
• 当一个子查询被转换成反连接或半连接时，子查询中的表必须位于它们所连接或关联的外部查询块中的表之后。然而，在某些特定的情况下，哈希反连接和半连接可以调整连接顺序；
• 如果无法进行视图合并，那么视图中的所有表将在连接到视图外的表之前被连接。

  如果执行计划中的连接顺序与我们所期望的不一样，可以考虑检查每个对象的基数估计值以及访问路径是否正确。