Oracle® Database SQL Tuning Guide

Part IV SQL Operators: Access Paths and Joins

---8 Optimizer Access Paths

---9 Joins

8 优化器访问路径

8 Optimizer Access Paths

访问路径是查询用于从行源检索行的一种技术。

8.1 访问路径介绍

行源是执行计划中的一个步骤返回的一组行。行源可以是联接或分组操作的表、视图或结果。

一元操作，如访问路径，是查询用来从行源检索行的一种技术，它接受单个行源作为输入。例如，全表扫描是检索单个行源的行。相反，联接是二进制的，并且接收来自两个行源的输入，数据库对不同的关系数据结构使用不同的访问路径。下表总结了主要数据结构的常用访问路径。

优化器考虑不同的可能执行计划，然后为每个计划分配成本。优化器选择成本最低的计划。通常，对于检索一小部分表行的语句，索引访问路径更有效，而对于访问表的大部分时，全表扫描更有效。

8.2 表的访问路径

表是Oracle 数据库中数据组织的基本单位。

关系表是最常见的表类型。关系表具有以下组织特征:

• 堆组织的表不会以任何特定的顺序存储行。

• 索引组织的表根据主键值对行进行排序。

• 外部表是只读表，其元数据存储在数据库中，但其数据存储在数据库之外。

8.2.1 关于堆组织的表访问

默认情况下，表为堆组织表，这意味着数据库将行放在最适合它们的地方，而不是按照用户指定的顺序。

当用户添加行时，数据库将这些行放在数据段中第一个可用的空闲空间中。不能保证按插入的顺序检索行。

8.2.1.1 数据块和段的行存储 : 初级读本

数据库将行存储在数据块中。在表中，数据库可以在块的底部的任何地方写入一行。Oracle 数据库使用块开销 ( 包含行目录和表目录 ) 来管理块本身。

一个区段由逻辑上相邻的数据块组成。这些块在磁盘上可能不是物理上连续的。段是一组区段，包含表空间中逻辑存储结构的所有数据。例如，Oracle 数据库分配一个或多个区段来形成表的数据段。数据库还分配一个或多个区段来形成表的索引段。

默认情况下，数据库使用自动段空间管理(ASSM) 来管理永久的、本地管理的表空间。当会话第一次向表中插入数据时，数据库将格式化位图块。位图跟踪段中的块。数据库使用位图查找空闲块，然后在写入每个块之前对其进行格式化。 ASSM 在块之间展开插入，以避免并发问题。

高水位标志(HWM) 是数据块未格式化且从未使用过的段中的点。在 HWM 下面，一个块可以格式化和写入，格式化和空，或未格式化。低高水位标志 ( 低 HWM) 标志所有块已知被格式化的点，因为它们要么包含数据，要么以前包含数据。

在全表扫描期间，数据库将所有块读取到低HWM ，这是已知要格式化的，然后读取段位图，以确定 HWM 和低 HWM 之间的哪些块格式化了，可以安全读取。数据库知道不能读过 HWM ，因为这些块是非格式化的。

8.2.1.2 row id 对行访问的重要性

堆组织的表中的每一行都有一个rowid 惟一对应于行块的物理地址。 rowid 是一行的 10 字节物理地址。

rowid 指向特定的文件、块和行号。例如，在 rowid AAAPecAAFAAAABSAAA 中，最后的 AAA 表示行号。行号是行目录项的索引。行目录项包含指向块上该行位置的指针。

数据库有时可以在块的底部移动一行。例如，如果启用了行移动，那么由于分区键更新、闪回表操作、收缩表操作等原因，行可以移动。如果数据库在一个块中移动一行，那么数据库将更新目录条目来修改指针。rowid 保持不变。

Oracle 数据库在内部使用 rowids 构建索引。例如， B-tree 索引中的每个键都与一个 rowid 相关联，该 rowid 指向相关行的地址。物理行 id 提供对表行最快的可能访问，使数据库能够以最少的 I/O 检索行。

8.2.1.3 直接 路径读取 ( Direct Path Reads )

在直接路径读取中，数据库直接将缓冲区从磁盘读取到PGA ，完全绕过 SGA 。

下图显示了在SGA 中存储缓冲区的分散读取和顺序读取与直接路径读取之间的区别。

Oracle 数据库可能执行直接路径读取的情况包括 :

• 执行 CREATE TABLE AS SELECT 语句

• 执行 ALTER REBUILD 或 ALTER MOVE 语句

• 从临时表空间读取数据

• 并行查询

• 读取 LOB 段

8.2.2 全 表扫描 (Full Table Scan)

全表扫描从表中读取所有行，然后过滤掉不符合选择条件的行。

8.2.2.1 优化器何时 考虑全表扫描

通常，优化器在不能使用不同的访问路径或另一个可用的访问路径成本更高时，会选择全表扫描。

下表显示了选择全表扫描的典型原因。

原因：没有索引

如果不存在索引，那么优化器将使用全表扫描。

原因：查询谓词对索引列应用一个函数。

除非索引是基于函数的索引，否则数据库将索引该列的值，而不是应用该函数的列的值。典型的应用程序级错误是索引一个字符列( 如 char_col) ，然后使用语法 ( 如 WHERE char_col=1) 查询该列。数据库隐式地将 TO_NUMBER 函数应用于常数 1 ，这阻止了索引的使用。

原因：一个SELECT COUNT (*) 发出查询，并且存在索引，但是索引的列包含空值。

优化器不能使用索引来计算表行数，因为索引不能包含空条目。

原因： 查询谓词不使用B-tree 索引 前导列 。

例如，一个索引可能存在于employees 表 (first_name,last_nam e) 列。如果一个用户发出一个查询，其中last_name='KING' 的谓词，那么优化器可能不会选择一个索引，因为列 first_name 不在谓词中。但是，在这种情况下，优化器也可能选择使用索引跳跃扫描。

原因：查询选择性很差

如果优化器确定查询需要表中的大多数块，那么它将使用全表扫描，即使有可用的索引。全表扫描可以使用较大的I/O 调用。进行更少的大型 I/O 调用比进行许多小型调用成本更低。

原因：表统计信息过时了

例如，一个表曾经很小，但是现在变大了。如果表统计信息已经过时，并且没有反映表的当前大小，那么优化器不知道现在走索引扫描比全表扫描更高效。

原因：表很小

如果一个表在高水位线下包含少于n 个块，其中 n 等于 DB_FILE_MULTIBLOCK_READ_COUNT 的设置初始化参数，则全表扫描可能比索引范围扫描成本更低。不管被访问的表或索引有多少，扫描的成本都可能较低。

原因：表具有高度的并行性。

表的高度并行性使优化器倾向于全表扫描而不是范围扫描。查询ALL_TABLES 中的值。度列确定并行度。

原因：该查询使用一个全表表扫描hint 。

提示FULL( 表别名 ) 指示优化器使用全表扫描。

例如：select /*+ full(t1) */ * from t1;

8.2.2.2 全表扫描的工作原理

在全表扫描中，数据库顺序读取高水位标记下的每个格式化块。数据库只读取每个块一次。

下面的图形描述了对表段的扫描，显示了扫描如何跳过高水位标记下的未格式化块。

由于这些块是相邻的，因此数据库可以通过使I/O 调用大于单个块 ( 称为多块读 ) 来加快扫描速度。读调用的大小范围从一个块到DB_FILE_MULTIBLOCK_READ_COUNT 初始化参数指定的块的数量。例如，将此参数设置为 4 指示数据库在单个调用中读取最多 4 个块。

在全表扫描期间缓存块的算法非常复杂。例如，数据库根据表的大小以不同的方式缓存块。

8.2.2.3 全表扫描 : 示例

这个示例扫描hr.employees 表。

以下是关于月薪超过$4000 的查询 :

SELECT salary FROM hr.employees WHERE salary > 4000;

例8-1 全表扫描

使用DBMS_XPLAN.DISPLAY_CURSOR 函数查看执行计划。由于salary 列上不存在索引，优化器无法使用索引范围扫描，因此使用全表扫描。

8.2.3 通过 Rowid 访问表 (Table Access by Rowid)

rowid 是数据存储位置的内部表示。

row 的 rowid 指定数据文件和数据块，其中包含该行和该行在该块中的位置。通过指定一行的 rowid 来定位该行是检索单个行最快的方法，因为它指定了该行在数据库中的确切位置。

注意:

Rowids 可以在不同的版本之间进行更改。不建议基于位置访问数据，因为行可以移动。

8.2.3.1 优化器通过Rowid 选择表访问

在大多数情况下，数据库在扫描一个或多个索引之后通过rowid 访问表。

但是，rowid 对表的访问不需要每次扫描索引。如果索引包含所有需要的列，则 rowid 访问可能不会发生。

8.2.3.2 Rowid 的表访问 工作原理

要通过rowid 访问表，数据库执行多个步骤。数据库的工作如下 :

1. 从语句 WHERE 子句或通过一个或多个索引的索引扫描获取所选行的 rowid, 对于不在索引中出现的语句中的列，可能需要访问表。

2. 根据表中的 rowid 定位每个选定的行。

8.2.3.3 Rowid 访问表 : 示例

这个例子演示了hr.employees 表的rowid 访问。假设您运行以下查询:

SELECT * FROM employees WHERE employee_id > 190;

以下计划的第2 步显示 hr.EMPLOYES 表上 emp_emp_id_pk 索引的范围扫描。数据库使用从索引获得的 rowid 从 employees 表中查找相应的行，然后检索它们。步骤 1 中显示的批处理访问意味着数据库从索引中检索一些 rowid ，然后尝试按块访问行，以改进群集并减少数据库必须访问块的次数。

8.2.4 样本表扫描 (Sample Table Scans)

样本表扫描从简单表或复杂的SELECT 语句 ( 例如涉及连接和视图的语句 ) 中检索随机数据样本。

8.2.4.1 优化器 何时考虑 选择样本表扫描

当语句FROM 子句包含 sample 关键字时，数据库使用示例表扫描。

本条格式如下：

• 样本（样本百分比）

数据库读取表中指定百分比的行以执行示例表扫描。

• 样本块（样本百分比）

数据库读取指定百分比的表块以执行示例表扫描。

sample_percent 指定要包含在样本中的总行或块计数的百分比。该值必须在 .000001 到 100 （但不包括 100 ）之间。

此百分比表示为样本选择的每一行或块采样中的每一行簇的概率。这并不意味着数据库会准确地检索行的样本百分比。

注：

只有在全表扫描或索引快速全扫描期间才可以进行块采样。如果存在更有效的执行路径，则数据库不采样块。要确保对特定表或索引进行块采样，请使用FULL 或 index_FFS 提示。

8.2.4.2 样本表扫描：示例

此示例使用示例表扫描访问1% 的 employees 表，按块而不是行进行采样。

示例8-2 示例表扫描

SELECT * FROM hr.employees SAMPLE BLOCK (1);

此语句的解释计划输出可能如下所示：

8.2.5 In-Memory 表扫描 (In-Memory Table Scans)

In-Memory 表扫描从内存列存储（IM 列存储）中检索行。

IM 列存储是一个可选的SGA 区域，它以一种特殊的列格式存储表和分区的副本，这种格式是为快速扫描而优化的。

8.2.5.1 优化器选择内存表扫描时

优化器成本模型知道IM 列存储的内容。

当用户执行引用IM 列存储中的表的查询时，优化器计算所有可能的访问方法（包括内存表扫描）的成本，并选择成本最低的访问方法。

8.2.5.2In-Memory 查询控件

可以使用初始化参数控制 In-Memory 查询。

以下数据库初始化参数会影响 In-Memory 的特性:

•INMEMORY_QUERY

此参数在会话级或系统级启用或禁用数据库的 In-Memory 查询。当您希望测试带有或不带有IM 列存储的工作负载时，此参数非常有用。

•OPTIMIZER_INMEMORY_AWARE

该参数启用(TRUE) 或禁用 (FALSE) 优化器成本模型、表扩展、 bloom 过滤器等的所有In-Memory 增强。将参数设置为FALSE 会导致优化器在优化 SQL 语句时忽略表的In-Memory 属性。

•OPTIMIZER_FEATURES_ENABLE

当设置为小于12.1.0.2 的值时，此参数的效果与将 OPTIMIZER_INMEMORY_AWARE 设置为 FALSE 相同。

要启用或禁用In-Memory 查询，您可以指定 INMEM ORY 或 NO_INMEMORY 提示，它们相当于每个查询的 INMEMORY_QUERY 初始化参数。如果 SQL 语句使用 INMEMORY 提示，但是它引用的对象还没有加载到 IM 列存储中，那么数据库在执行语句之前不会等待对象被填充到 IM 列存储中。

但是，对象的初始访问将触发内存列存储中的对象填充。

8.2.5.3 内存表扫描 In-Memory Table Scans: 示例

这个示例显示了一个执行计划，其中包括In-Memory Table Scans 操作。

Example 8-3 In-Memory Table Scan

SELECT *

FROM oe.product_information WHERE list_price > 10

ORDER BY product_id

该语句的计划如下所示，第2 步中的 INMEMORY 关键字表示从 IM 列存储中访问了部分或全部对象 :

8.3 B-Tree 索引访问路径

索引是一个可选的结构，与表或聚簇表相关联，有时可以加快数据访问速度。

通过在表的一个或多个列上创建索引，您可以在某些情况下从表中检索随机分布的一小组行。索引是减少磁盘I/O 的许多方法之一。

8.3.1 关于 B-Tree 索引访问

b -tree( 平衡树的缩写 ) 是最常见的数据库索引类型。

B- tree 索引是按范围划分的值的有序列表。通过将键与行或行范围相关联， B-Tree 为广泛的查询( 包括精确匹配和范围搜索 ) 提供了出色的检索性能。

8.3.1.1 B-Tree 索引结构

B-tree 索引有两种类型的块 : 用于搜索的分支块和存储值的叶块。

下图说明了 B-Tree 索引的逻辑结构。分支块存储在两个键之间进行分支决策所需的最小键前缀。叶块包含每个索引数据值和用于定位实际行的相应rowid 。每个索引条目按 ( 键， rowid) 排序。叶块是双向连接的。

8.3.1.2 索引存储如何影响索引扫描

位图索引块可以出现在索引段的任何地方。

图8-3 显示了相邻的叶块。例如， 1-10 块在 11-19 块的旁边和前面。这个排序说明了连接索引项的链表。但是，索引块不需要按顺序存储在索引段中。例如， 246-250 块可以出现在段中的任何位置，包括 1-10 块的前面。因此，有序的索引扫描必须执行单块 I/O 。数据库必须读取一个索引块，以确定接下来必须读取哪个索引块。

索引块体将索引项存储在堆中，就像表行一样。例如，如果值10 首先插入到一个表中，那么带有键 10 的索引条目可能会插入到索引块的底部。如果 0 是下一个插入到表中的，那么键 0 的索引条目可能会被插入到条目的顶部，其长度为 10 。因此，块体中的索引项不是按键顺序存储的。但是，在索引块中，行标头按键顺序存储记录。例如，标头中的第一条记录用键 0 指向索引条目，以此类推，直到用键 10 指向索引条目的记录为止。因此，索引扫描可以读取行标头来确定从何处开始和结束范围扫描，从而避免了读取块中的每个条目。

8.3.1.3 唯一和非唯一索引

在非惟一索引中，数据库通过将rowid 作为额外的列附加到键上来存储它。该条目添加一个长度字节以使键唯一。

例如，图8-3 所示的非唯一索引中的第一个索引键是对 0,rowid ，而不是简单的 0 。数据库按索引键值排序，然后按 rowid 升序排序。例如，条目的排序如下 :

0,AAAPvCAAFAAAAFaAAa

0,AAAPvCAAFAAAAFaAAg

0,AAAPvCAAFAAAAFaAAl

2,AAAPvCAAFAAAAFaAAm

在唯一索引中，索引键不包括rowid 。数据库仅根据索引键值( 如 0 、 1 、 2 等 ) 对数据进行排序。

8.3.1.4 B-Tree 索引和 Null

B-tree 索引从不存储完全空值，这对于优化器如何选择访问路径非常重要。这个规则的结果是单列B -Tree 索引从不存储空值。

一个例子有助于说明。hr.employees 表在 employee_id 上有一个主键索引，在 department_id 上有一个唯一的索引。 department_id 列可以包含空值，使其成为可空列，但 employee_id 列不能。

SQL> SELECT COUNT(*) FROM employees WHERE department_id IS NULL;

COUNT(*)

----------

1

SQL> SELECT COUNT(*) FROM employees WHERE employee_id IS NULL;

COUNT(*)

----------

0

下面的示例显示优化器为hr.employees 中所有部门 id 的查询选择了一个全表扫描。优化器无法对employees.department_id 使用索引，因为索引不能保证包含表中每一行的条目。

下面的示例显示优化器可以使用department_id 上的索引查询特定的 department id ，因为所有非空行都被索引。

以下示例显示，当谓词排除空值时，优化器选择索引扫描：

8.3.2 索引唯一扫描 (Index Unique Scans)

索引唯一扫描最多返回1 行数据。

8.3.2.1 优化器 何时 考虑索引唯一扫描

索引唯一扫描需要相等谓词。

具体来说，只有当查询谓词使用相等运算符引用唯一索引键中的所有列时，数据库才会执行唯一扫描，例如prod_id=10 。

唯一或主键约束本身不足以生成索引唯一扫描，因为列上可能已存在非唯一索引。考虑以下示例，该示例创建t_ table ，然后在numcol 上创建非唯一索引：

SQL> CREATE TABLE t_table(numcol INT);

SQL> CREATE INDEX t_table_idx ON t_table(numcol);

SQL> SELECT UNIQUENESS FROM USER_INDEXES WHERE INDEX_NAME = 'T_TABLE_IDX';

UNIQUENES

---------

NONUNIQUE

以下代码在具有非唯一索引的列上创建主键约束，从而导致索引范围扫描而不是索引唯一扫描：

SQL> ALTER TABLE t_table ADD CONSTRAINT t_table_pk PRIMARY KEY(numcol);

SQL> SET AUTOTRACE TRACEONLY EXPLAIN

SQL> SELECT * FROM t_table WHERE numcol = 1;

可以使用INDEX （ alias INDEX_name ）提示指定要使用的索引，但不能指定特定类型的索引访问路径。

8.3.2.2 索引唯一扫描的工作原理

扫描将按顺序搜索索引以查找指定的键。索引唯一扫描在找到第一条记录后立即停止处理，因为不可能有第二条记录。数据库从索引项获取rowid ，然后检索 rowid 指定的行。

下图说明了索引唯一扫描。语句请求prod_ID 列中产品 ID 19 的记录，该列具有主键索引。

8.3.2.3 索引唯一扫描：示例

此示例使用唯一扫描从products 表检索行。

以下语句查询sh.products 表中产品 19 的记录：

SELECT * FROM sh.products WHERE prod_id = 19;

由于products.prod_id 列上存在主键索引，并且 WHERE 子句使用相等运算符引用所有列，优化器选择唯一扫描：

8.3.3 索引 范围扫描 (Index Range Scans)

索引范围扫描是值的有序扫描。

扫描中的范围可以在两边都有界，也可以在一边或两边都无界。优化器通常为具有高选择性的查询选择范围扫描。

默认情况下，数据库按升序存储索引，并以相同的顺序扫描索引。例如，使用谓词department_id >= 20 的查询使用范围扫描来返回按索引键 20 、 30 、 40 等排序的行。如果多个索引项具有相同的键，那么数据库将按 rowid 升序返回它们，因此 0 、 AAAPvCAAFAAAAFaAAa 后面紧跟着 0 、 AAAPvCAAFAAAAFaAAg ，以此类推。

索引范围降序扫描与索引范围降序扫描相同，不同之处在于数据库按降序返回行。通常，在按降序排列数据时，或者在查找小于指定值的值时，数据库使用降序扫描。

8.3.3.1 优化器 何时 考虑索引范围扫描

对于索引范围扫描，索引键必须可能有多个值。具体来说，优化器考虑索引范围扫描在以下情况:

• 在条件中指定一个索引的一个或多个前导列。

条件指定一个或多个表达式和逻辑( 布尔 ) 操作符的组合，并返回 TRUE 、 FALSE 或 UNKNOWN 值。条件的例子包括 :

– department_id = :id

– department_id < :id

– department_id > :id

– 和前面的条件的组合，如 department_id >:low 和 department_id <:hi 。

注意：

为了让优化器考虑范围扫描，像'%ASD' 这样的形式 col1 的通配符搜索不能位于前导位置。

• 索引键可以有 0 、 1 或更多的值。

提示：

如果需要排序的数据，请使用ORDER BY 子句，不要依赖索引。如果索引可以满足 ORDERBY 子句，那么优化器将使用此选项，从而避免排序。

当索引可以满足ORDER BY descending 子句时，优化器会考虑索引范围扫描递减。

如果优化器选择完整表扫描或其他索引，则可能需要提示来强制此访问路径。INDEX （ tbl_alias ix_name ）和 INDEX_DESC （ tbl_alias ix_name ）提示指示优化器使用特定索引。

8.3.3.2 索引范围扫描的工作原理

在索引范围扫描期间，Oracle 数据库从根目录转到分支目录。一般来说，扫描算法如下：

1. 读取根块。

2. 读取分支块。

3. 交替执行以下步骤，直到检索到所有数据：

a 、读取叶块以获取 rowid 。

b 、读取表块以检索行。

注：

在某些情况下，索引扫描读取一组索引块，对行ID 进行排序，然后读取一组表块。

因此，为了扫描索引，数据库在叶块中向后或向前移动。例如，对介于20 和 40 之间的 IDs 的扫描定位具有 20 或更大的最小键值的第一个叶块。扫描在叶节点的链接列表中水平进行，直到找到大于 40 的值，然后停止。

下图说明了使用升序的索引范围扫描。语句请求department_id 列中值为 20 的员工记录，该列具有非唯一索引。在本例中，存在部门 20 的 2 个索引项。

8.3.3.3 索引范围扫描：示例

此示例使用索引范围扫描从employees 表检索一组值。

以下语句查询部门20 中薪资大于 1000 的员工的记录：

SELECT *

FROM employees

WHERE department_id = 20 AND salary > 1000;

前面的查询基数较低（返回几行），因此该查询使用department _ id 列上的索引。数据库扫描索引，从 employees 表中获取记录，然后对这些获取的记录应用 salary>1000 筛选器以生成结果。

8.3.3.4 索引范围扫描 降序 ：示例

本例使用索引按排序顺序从employees 表中检索行。

以下语句按降序查询部门20 中员工的记录：

SELECT *

FROM employees

WHERE department_id < 20 ORDER BY department_id DESC;

前面的查询基数较低，因此查询使用department_id 列上的索引

数据库将定位第一个索引叶块，该叶块包含的最大键值不超过20 。然后，扫描通过叶节点的链接列表水平向左进行。数据库从每个索引项获取 rowid ，然后检索 rowid 指定的行。

8.3.4 索引全扫描 (Index Full Scans)

索引完全扫描按顺序读取整个索引。索引完全扫描可以消除单独的排序操作，因为索引中的数据是按索引键排序的。

8.3.4.1 优化器 何时 考虑索引全扫描

优化器在各种情况下考虑索引完全扫描。这些情况包括：

• 谓词引用索引中的列。此列不必是前导列。

• 未指定谓词，但满足以下所有条件：

– 表和查询中的所有列都在索引中。

– 至少有一个索引列不为空。

• 查询包括索引的不可为空列上的 ORDER BY 操作。

8.3.4.2 索引完整扫描的工作原理

数据库读取根块，然后向下导航索引的左侧（如果执行降序全扫描，则向右导航），直到到达叶块。

然后数据库到达一个叶块，扫描在索引的底部进行，一次一个块，按排序顺序进行。数据库使用单块I/O ，而不是多块 I/O 。

下图显示了索引完全扫描。一个语句请求按部门id 排序查询部门记录。

8.3.4.3 索引全扫描：示例

本例使用索引完全扫描来满足带有ORDERBY 子句的查询。

以下语句按部门ID 的顺序查询部门的 ID 和名称：

SELECT department_id, department_name FROM departments

ORDER BY department_id;

数据库定位第一个索引叶块，然后通过叶节点的链接列表水平向右移动。对于每个索引项，数据库从该项获取rowid ，然后检索由 rowid 指定的表行。

因为索引是按部门id 排序的，所以数据库避免了对检索到的行进行排序的单独操作。

8.3.5 索引快速全扫描 (Index Fast Full Scans)

索引几乎完全扫描按未排序的顺序读取索引块，因为它们存在于磁盘上。此扫描不使用索引来探测表，而是读取索引而不是表，实质上是将索引本身用作表。

8.3.5.1 优化器 何时 考虑索引快速全扫描

当查询只访问索引中的属性时，优化器会考虑此扫描。

注：

与完全扫描不同，快速完全扫描无法消除排序操作，因为它没有按顺序读取索引。

INDEX_FFS （ table_name INDEX_name ）提示强制执行快速完整索引扫描。

8.3.5.2 索引快速全扫描的工作原理

数据库使用多块I/O 读取根块以及所有叶块和分支块。数据库忽略分支和根块，并读取叶块上的索引项。

8.3.5.3 索引快速全扫描：示例

此示例使用快速的完整索引扫描作为优化器提示的结果。

以下语句按部门ID 的顺序查询部门的 ID 和名称：

SELECT /*+ INDEX_FFS(departments dept_id_pk) */ COUNT(*) FROM departments;

以下计划显示优化器选择了快速全索引扫描：

8.3.6 索引 跳跃扫描 (Index Skip Scans)

当组合索引的初始列被“ 跳过 ” 或未在查询中指定时，将发生索引跳过扫描。

8.3.6.1 优化器 何时考虑跳跃 索引扫描

通常，跳过扫描索引块比扫描表块快，也比执行全索引扫描快。

当满足以下条件时，优化器将考虑跳过扫描:

• 在查询谓词中没有指定复合索引的前导列。

例如，查询谓词不引用cust_gender 列，而复合索引键是 (cust_gender,cust_email) 。

• 复合索引的前导列中 选择性特别差 ，但是索引的非前导键 选择性特别高 。

例如，如果复合索引键是(cust_gender,cust_email) ，那么 cust_gender 列只有两个不同的值，而 cust_email 有数千个。

8.3.6.2 索引跳跃 扫描的工作原理

索引跳跃扫描在逻辑上将复合索引拆分为更小的子索引。

索引前导列中不同值的数目决定逻辑子索引的数目。数目越少，优化器必须创建的逻辑子索引就越少，扫描的效率就越高。扫描会分别读取每个逻辑索引，并“ 跳过 ” 非前导列上不符合筛选条件的索引块。

8.3.6.3 索引跳跃 扫描：示例

本例使用索引跳跃扫描来满足sh.customers 表的查询。

customers 表包含一列 cust_gender ，其值为 M 或 F 。以用户 sh 的身份登录数据库时，可以在列（ cust_gender ， cust_email ）上创建复合索引，如下所示：

CREATE INDEX cust_gender_email_ix

ON sh.customers (cust_gender, cust_email);

从概念上讲，索引的一部分可能如下所示，其中F 或 M 的性别值是索引的前导列。

F,Wolf@company.example.com,rowid

F,Wolsey@company.example.com,rowid

F,Wood@company.example.com,rowid

F,Woodman@company.example.com,rowid

F,Yang@company.example.com,rowid

F,Zimmerman@company.example.com,rowid

M,Abbassi@company.example.com,rowid

M,Abbey@company.example.com,rowid

在sh.customers 表中对客户运行以下查询：

SELECT *

FROM sh.customers

WHERE cust_email = 'Abbey@company.example.com';

数据库可以使用客户性别电子邮件索引的跳过扫描，即使在WHERE 子句中未指定 cust 性别。在示例索引中，前导列 cust_gender 有两个可能的值： F 和 M 。数据库在逻辑上将索引拆分为两个。一个子索引具有键 F ，其项的格式如下：

F,Wolf@company.example.com,rowid

F,Wolsey@company.example.com,rowid

F,Wood@company.example.com,rowid

F,Woodman@company.example.com,rowid

F,Yang@company.example.com,rowid

F,Zimmerman@company.example.com,rowid

第二个子索引具有键M ，其条目的格式如下：

M,Abbassi@company.example.com,rowid

M,Abbey@company.example.com,rowid

在搜索电子邮箱为 Abbey@company.example.com 的客户记录时，数据库首先搜索前导值为 F 的子索引，然后搜索前导值为 M 的子索引。概念上，数据库按如下方式处理查询：

执行计划如下：

8.3.7 索引连接扫描 (Index Join Scans)

索引联接扫描是多个索引的哈希联接，这些索引一起返回查询请求的所有列。数据库不需要访问表，因为所有数据都是从索引中检索的。

8.3.7.1 优化器 何时 考虑索引连接扫描

在某些情况下，避免表访问是最经济高效的选择。优化器在以下情况下考虑索引联接：

• 多个索引的哈希连接检索查询请求的所有数据，而不需要表访问。

• 从表中检索行的成本高于不从表中检索行而读取索引的成本。索引连接通常很昂贵。

例如，当扫描两个索引并将它们连接起来时，选择最具选择性的索引，然后探测表通常成本较低。

您可以使用index_join （ table_name ）提示指定索引联接。

8.3.7.2 索引连接扫描的工作 原理

索引联接涉及扫描多个索引，然后对从这些扫描中获得的 row id 使用哈希联接返回行。

在索引连接扫描中，总是避免表访问。例如，在一个表上连接两个索引的过程如下：

1. 扫描第一个索引以检索 rowid 。

2. 扫描第二个索引以检索 rowid 。

3. 按 rowid 执行哈希联接以获取行。

8.3.7.3 索引连接扫描：示例

此示例查询姓氏以A 开头并指定索引联接的员工的姓氏和电子邮件。

SELECT /*+ INDEX_JOIN(employees) */ last_name, email FROM employees

WHERE last_name like 'A%';

在（姓氏、名字）和电子邮件列上存在单独的索引。emp_name_ix 索引的一部分可能如下所示：

Banda,Amit,AAAVgdAALAAAABSABD

Bates,Elizabeth,AAAVgdAALAAAABSABI

Bell,Sarah,AAAVgdAALAAAABSABc

Bernstein,David,AAAVgdAALAAAABSAAz

Bissot,Laura,AAAVgdAALAAAABSAAd

Bloom,Harrison,AAAVgdAALAAAABSABF

Bull,Alexis,AAAVgdAALAAAABSABV

emp_email_uk 索引的第一部分可能如下所示：

ABANDA,AAAVgdAALAAAABSABD

ABULL,AAAVgdAALAAAABSABV

ACABRIO,AAAVgdAALAAAABSABX

AERRAZUR,AAAVgdAALAAAABSAAv

AFRIPP,AAAVgdAALAAAABSAAV

AHUNOLD,AAAVgdAALAAAABSAAD

AHUTTON,AAAVgdAALAAAABSABL

以下示例使用DBMS_XPLAN.DISPLAY_CURSOR 函数检索计划。数据库检索 emp_email_uk 索引中的所有 rowid ，然后检索 emp_name_ix 中以 A 开头的姓氏的 rowid 。数据库使用哈希联接来搜索两组 rowid 中的匹配项。例如， rowid aaavgdalaaaabsabd 同时出现在两组 rowid 中，因此数据库会在 employees 表中探测与此 rowid 对应的记录。

8.4 位图索引访问路径

Bitmap Index Access Paths

位图索引将索引的数据与rowid 范围相结合。

8.4.1 关于位图索引访问

在传统的B -Tree 索引中，一个索引入口指向一行。在位图索引中，键是索引数据和rowid 范围的组合。

数据库为每个索引键存储至少一个位图。位图中的每个值（由1 和 0 个值组成）都指向 rowid 范围内的一行。因此，在位图索引中，一个索引条目指向一组行，而不是一行。

8.4.1.1 位图索引和 B 树索引之间的差异

位图索引使用与B 树索引不同的键，但存储在 B 树结构中。

下表显示了索引项类型之间的差异。

数据库将位图索引存储在B 树结构中。数据库可以在键的第一部分（即定义索引的属性集）上快速搜索 B 树，然后获得相应的 rowid 范围和位图。

8.4.1.2 位图索引的用途

位图索引通常适用于具有少量或中度不同值（NDV ）的不经常修改的数据。

一般来说，B- 树索引适用于 NDV 高、 DML 活动频繁的列。例如，优化器可以为 sales.amount 列的查询选择一个 B 树索引，该列返回很少的行。相比之下， customers.state 和 customers.county 列是位图索引的候选列，因为它们很少有不同的值，很少更新，并且可以从高效的 and 和 OR 操作中获益。

位图索引是在数据仓库中加速特殊查询的有用方法。它们是星型转换的基础。具体来说，位图索引在包含以下内容的查询中非常有用：

• 在访问表本身之前， WHERE 子句中有多个条件，数据库会筛选出满足某些条件（而不是全部条件）的行。

• AND 、 OR 和 NOT 在具有低或中等NDV 列上操作

组合位图索引使这些操作更有效。数据库可以很快地从位图索引中合并位图。例如，如果customers.state 和 customers.county 列上存在位图索引，则这些索引可以极大地提高以下查询的性能：

SELECT *

FROM customers WHERE state = 'CA'

AND county = 'San Mateo'

数据库可以有效地将合并位图中的1 个值转换为 rowids 。

• 计数功能

数据库可以扫描位图索引，而无需扫描表。

• 选择空值的谓词

与B 树索引不同，位图索引可以包含空值。对列中的空值进行计数的查询可以使用位图索引，而无需扫描表。

• 低频率的 DM L 操作

原因是一个索引键指向许多行。如果会话修改了索引数据，那么数据库就不能锁定位图中的一个位：相反，数据库会锁定整个索引项，这实际上会锁定位图指向的行。例如，如果特定客户的居住县从San Mateo 更改为 Alameda ，则数据库必须独占访问位图中的 San Mateo 索引项和 Alameda 索引项。在提交之前，不能修改包含这两个值的行。

8.4.1.3 位图和 rowids

对于位图中的特定值，如果行值与位图条件匹配，则该值为1 ；如果不匹配，则该值为 0 。基于这些值，数据库使用内部算法将位图映射到 rowid 。

位图项包含索引值、rowid 范围（起始行和结束行 id ）和位图。位图中的每个 0 或 1 值都是 rowid 范围的偏移量，并映射到表中的潜在行，即使该行不存在。因为块中可能的行数是预先确定的，所以数据库可以使用范围端点来确定范围中任意行的 row id 。

注：

Hakan 因子是位图索引算法用于限制 Oracle 数据库假定可以存储在单个块中的行数的优化。通过人为地限制行数，数据库减小了位图的大小。

表8-4 显示了 sh.customers.cust_martin_status 列的部分示例位图，该位图可以为空。实际索引有 12 个不同的值。示例中仅显示 3 个：空、已婚和单身。

如表8-4 所示，位图索引可以包含完全由空值组成的键，这与 B 树索引不同。在表 8-4 中，范围内第 6 行的空值为 1 ，这意味着范围内第 6 行的客户婚姻状况值为空。索引空值对于某些 SQL 语句（例如具有聚合函数计数的查询）非常有用。

8.4.1.4 位图连接索引 (Bitmap Join Indexes)

位图连接索引是用于连接两个或多个表的位图索引。

优化器可以使用位图连接索引来减少或消除计划执行期间必须连接的数据量。位图连接索引在存储方面比物化连接视图更有效。

以下示例在sh.sales 和 sh.customers 表上创建位图索引：

CREATE BITMAP INDEX cust_sales_bji ON sales(c.cust_city) FROM sales s, customers c

WHERE c.cust_id = s.cust_id LOCAL;

前面CREATE 语句中的 FROM 和 WHERE 子句表示表之间的联接条件。 customers.cust_city 列是索引键。

索引中的每个键值表示customers 表中可能的城市。从概念上讲，索引的键值可能如下所示，每个键值关联一个位图：

位图中的每个位对应于sales 表中的一行。在 Smithville 键中，值 1 表示 sales 表中的第一行对应于销售给 Smithville 客户的产品，而值 0 表示第二行对应于未销售给 Smithville 客户的产品。

考虑以下对史密斯维尔客户的单独销售数量的查询：

SELECT COUNT (*)

FROM sales s, customers c WHERE c.cust_id = s.cust_id

AND c.cust_city = 'Smithville';

下面的计划显示，数据库读取Smithville 位图来获得 Smithville sales 的数量 ( 步骤 4) ，从而避免了 customer 和 sales 表的连接。

8.4.1.5 位图存储 (Bitmap Storage)

位图索引位于B -Tree 结构中，使用分支块和叶块，就像在B -Tree 中一样。

例如，如果customers.cust_martin_status 列有 12 个不同的值，则一个分支块可能指向 keys married 、 rowid range 和 single 、 rowid range ，另一个分支块可能指向 widowed 、 rowid range 键，依此类推。或者，单个分支块可以指向包含所有 12 个不同键的叶块。

每个索引列值可以有一个或多个位图块，每个位图块都有自己的rowid 范围，在一个或多个区段中占据一组连续的行。数据库可以使用位图块来分解相对于块大小较大的索引项。例如，数据库可以将一个索引项分成三个部分，前两个部分位于同一区段的单独块中，最后一个部分位于不同区段的单独块中。

为了节省空间，Oracle 数据库可以压缩 0 个值的连续范围。

8.4.2 位图转换为 Rowid (Bitmap Conversion to Rowid)

位图转换在位图中的条目和表中的行之间进行转换。转换可以从条目转到行（到ROWID ），也可以从行转到条目（从 ROWID ）。

8.4.2.1 优化器 何时考虑 选择将位图转换为Rowid

每当优化器使用位图索引项从表中检索一行时，都会使用转换。

8.4.2.2 如何将位图转换为 Rowid

从概念上讲，位图可以表示为表。

例如，表8-4 将位图表示为一个表，其中客户行号作为列， cust_marital_status 值作为行。表 8-4 中的每个字段的值都是 1 或 0 ，表示一行中的一个列值。从概念上讲，位图转换使用一个内部算法，该算法表示 “ 位图中的 F 字段对应于表中第 m 个块的第 n 行 ” ，或者 “ 表中第 m 个块的第 n 行对应于位图中的 F 字段 ” 。

8.4.2.3 位图 转换 Rowid: 示例

在本例中，优化器选择位图转换操作来满足使用范围谓词的查询。

对sh.customers 表的查询选择 1918 年以前出生的所有客户的姓名 :

SELECT cust_last_name, cust_first_name FROM customers

WHERE cust_year_of_birth < 1918;

下面的计划显示，数据库使用范围扫描查找所有小于1918 的键值 ( 步骤 3) ，将位图中的 1 个值转换为 rowids( 步骤 2) ，然后使用 rowids 从 customers 表中获取行 ( 步骤 1):

8.4.3 位图索引单值 (Bitmap Index Single Value)

这种访问路径使用位图索引查找单个键值。

8.4.3.1 优化器 何时 考虑位图索引单值

当谓词包含相等运算符时，优化器将考虑此访问路径。

8.4.3.2 位图索引单值的工作原理

查询扫描单个位图以查找包含1 值的位置。数据库将 1 值转换为 rowids ，然后使用 rowids 查找行。

数据库只需要处理一个位图。例如，下表表示sh.customers.cust_martin_status 列中 widown 值的位图索引（两个位图）。为了满足对状态为 widown 的客户的查询，数据库可以搜索 widown 位图中的每个 1 值，并找到相应行的 rowid 。

8.4.3.3 位图索引单值：示例

在本例中，优化器选择位图索引单值操作来满足使用相等谓词的查询。

查询sh.customers 表将选择所有丧偶客户：

SELECT *

FROM customers

WHERE cust_marital_status = 'Widowed';

以下计划显示，数据库使用客户位图索引中的丧偶键读取条目（步骤3 ），将位图中的 1 值转换为 rowids （步骤 2 ），然后使用 rowids 从客户表获取行（步骤 1 ）：

8.4.4 位图索引范围扫描 (Bitmap Index Range Scans)

这种访问路径使用位图索引查找一系列值。

8.4.4.1 优化器 何时 考虑位图索引范围扫描

当谓词选择一系列值时，优化器将考虑此访问路径。

扫描中的范围可以在两侧有界，也可以在一侧或两侧无界。优化器通常为选择性查询选择范围扫描。

8.4.4.2 位图索引范围扫描的工作原理

此扫描的工作原理与B 树范围扫描类似。

例如，下表表示sh.customers.cust_year_of_birth 列的位图索引中的三个值。如果查询请求 1917 年之前出生的所有客户，则数据库可以扫描此索引以查找低于 1917 年的值，然后获取具有 1 的行的 rowid 。

8.4.4.3 位图索引范围扫描：示例

本例使用范围扫描来选择在一年前出生的客户。

查询sh.customers 表可以选择 1918 年以前出生的客户的姓名：

SELECT cust_last_name, cust_first_name

FROM customers

WHERE cust_year_of_birth < 1918

以下计划显示，数据库获取低于1918 的 cust year 出生键值的所有位图（步骤3 ），将位图转换为 rowid （步骤2 ），然后获取行（步骤 1 ）：

8.4.5 位图合并 (Bitmap Merge)

此访问路径合并多个位图，结果返回单个位图。位图合并由执行计划中的位图合并操作指示。

8.4.5.1 优化器考虑位图合并时

优化器通常使用位图合并来合并从位图索引范围扫描生成的位图。

8.4.5.2 位图合并的工作原理

合并在两个位图之间使用布尔或运算。结果位图选择第一个位图中的所有行，加上每个后续位图中的所有行。

查询可以选择1918 年以前出生的所有客户。以下示例显示了三个 customers.cust_year_of_birth keys:1917 、 1916 和 1915 的示例位图。如果任何位图中的任何位置有 1 ，则合并的位图在同一位置有 1 。否则，合并位图的值为 0 。

结果位图中的1 值对应于包含值 1915 、 1916 或 1917 的行。

8.4.5.3 位图合并：示例

此示例显示数据库如何合并位图，以使用范围谓词优化查询。

查询sh.customers 表可以选择 1918 年以前出生的女性客户的姓名：

SELECT cust_last_name, cust_first_name

FROM customers

WHERE cust_gender = 'F'

AND cust_year_of_birth < 1918

以下计划显示数据库获得所有的位图cust_year_of_birth 键低于 1918( 步骤 6), 然后合并这些位图使用或逻辑创建一个位图 ( 步骤 5) 。数据库获取一个位图 cust_gender 关键的 F( 步骤 4), 然后执行一个操作这两个位图。结果是一个单一的位图，包含 1 个请求行的值 ( 步骤 3) 。

8.5 聚簇表 访问路径

Table Cluster Access Paths

聚簇表是一组共享公共列并在相同块中存储相关数据的表。当对表进行集群化时，单个数据块可以包含来自多个表的行。

8.5.1 聚簇扫描

聚簇索引是使用索引定位数据的表集群。

集群索引是集群键上的一个B 树索引。集群扫描从存储在聚簇索引中的表中检索具有相同集群键值的所有行。

8.5.1.1 优化器 何时考虑聚簇扫描

当查询访问索引集群中的表时，数据库将考虑集群扫描。

8.5.1.2 聚簇 扫描的工作原理

在聚簇索引中，数据库将具有相同集群键值的所有行存储在相同的数据块中。

例如，如果hr . employees2 和 hr.departments2 表被聚集在 emp_dept_cluster 中，如果集群键是 department_id ，那么数据库将 department 10 中的所有员工存储在同一个块中， department 20 中的所有员工存储在同一个块中，以此类推。

B-tree 聚簇索引将集群键值与包含数据的块的数据库块地址(DBA) 关联起来。例如， key 30 的索引项显示了包含 department 30 员工行的 block 的地址 :

30,AADAAAA9d

当用户请求聚簇表中的行时，数据库扫描索引以获得包含这些行的块的dba 。然后， Oracle 数据库根据这些 dba 定位行。

8.5.1.3 聚簇扫描：示例

此示例将employees 和 departments 表聚集在 department _ id 上列，然后查询单个部门的群集。

作为用户hr ，可以在集群中创建聚簇表、聚簇索引和表，如下所示 :

CREATE CLUSTER employees_departments_cluster

(department_id NUMBER(4)) SIZE 512;

CREATE INDEX idx_emp_dept_cluster

ON CLUSTER employees_departments_cluster;

CREATE TABLE employees2

CLUSTER employees_departments_cluster (department_id)

AS SELECT * FROM employees;

CREATE TABLE departments2

CLUSTER employees_departments_cluster (department_id)

AS SELECT * FROM departments;

您可以查询部门30 中的员工，如下所示：

SELECT *

FROM employees2

WHERE department_id = 30;

为了执行扫描，Oracle 数据库首先通过扫描聚簇索引获得描述部门30 的行的 rowid （步骤 2 ）。然后， Oracle 数据库使用这个 rowid 定位 employees2 中的行（步骤 1 ）

8.5.2 哈希扫描 (Hash Scans)

哈希聚簇类似于索引聚簇，只是索引键替换为哈希函数。不存在单独的索引聚簇。

在哈希聚簇中，数据是索引。数据库使用哈希扫描根据哈希值定位哈希聚簇中的行。

8.5.2.1 优化器 何时 考虑哈希扫描

当查询访问哈希聚簇中的表时，数据库将考虑哈希扫描。

8.5.2.2 哈希扫描的工作 原理

在哈希聚簇中，具有相同哈希值的所有行都存储在相同的数据块中。

要执行集群的哈希扫描，Oracle 数据库首先通过向语句指定的集群键值应用哈希函数来获得哈希值。然后，Oracle 数据库扫描包含具有该哈希值的行的数据块。

8.5.2.3 哈希 扫描：示例

此示例对department_id 列上的 employees 和 departments 表进行哈希，然后查询集群中的单个部门。在集群中创建哈希集群和表，如下所示：

CREATE CLUSTER employees_departments_cluster

(department_id NUMBER(4)) SIZE 8192 HASHKEYS 100;

CREATE TABLE employees2

CLUSTER employees_departments_cluster (department_id)

AS SELECT * FROM employees;

CREATE TABLE departments2

CLUSTER employees_departments_cluster (department_id)

AS SELECT * FROM departments;

您可以查询部门30 中的员工，如下所示：

SELECT *

FROM employees2

WHERE department_id = 30

为了执行哈希扫描，Oracle 数据库首先通过对键值 30 应用哈希函数来获取哈希值，然后使用该哈希值扫描数据块并检索行（步骤1 ）。

欢迎关注我的微信公众号"IT小Chen"，共同学习，共同成长！！！

Oracle 访问路径