Oracle 篇：深入了解执行计划中的访问路径

转自：潇湘秦

前言

平时做 SQL 优化的大佬都知道，优化 SQL 语句的核心步骤之一就是分析和优化其执行计划。而在执行计划中，访问路径的选择更是至关重要。访问路径决定了数据库在执行查询时采用的数据访问方式，直接影响查询的执行效率。

平常常见的访问路径包括：

全表扫描（Full Table Scan）
索引范围扫描（Index Range Scan）
索引快速全扫描（Index Fast Full Scan）
索引跳跃扫描（Index Skip Scan）

等等，然而，访问路径的种类远不止这些。

博主为了更深入理解这些访问路径，花了一周的时间深入学习了官方文档中关于访问路径的知识点，并且也结合了自己在工作中的经验从而整理了这篇文章，旨在与大家分享 Oracle 中的所有访问路径及其应用。因为只有非常了解访问路径才能更好的优化 SQL 语句的执行计划。

官方文档对访问路径的介绍（Oracle 12c）：Optimizer Access Paths^[1]

访问路径介绍

什么是访问路径：

在数据库查询中，访问路径（Access Path）指的是数据库在执行查询时，选择的数据访问方法。简单来说，就是数据库从存储的数据中找到并检索所需数据的方式。每个查询都有不同的访问路径，而数据库通过执行计划来决定使用哪一种访问路径，进而影响查询的效率和性能。
Oracle 在处理 SQL 查询时，会根据表结构、索引、查询条件等因素，选择不同的访问路径来执行查询操作。

访问路径介绍：

Oracle 对不同的关系数据结构使用不同的访问路径，下表总结了主要数据结构的常见访问路径。

计划生成器 （可参考文章：👉 Oracle 篇：一条 SQL 语句的执行全流程解析）通过尝试不同的访问路径、连接方法和连接顺序来探索查询块的各种计划。许多计划都是可能的，因为数据库可以使用各种组合来产生相同的结果。优化器选择成本最低的计划。一般来说，索引访问路径对于访问表的小部分行数据时更有效，而在访问表的大部分行数据时，全表扫描更有效。

一、表级别的访问路径（Oracle 默认表类型是堆表）

表是 Oracle 数据库中数据组织的基本单位。关系表是最常见的表类型，不同的关系表具有以下组织特征：

堆组织的表（Heap-Organized Tables）不按任何特定的顺序存储行数据（Oracle 的默认表类型）。
按索引组织的表（B-Tree Indexes and IOTs）根据主键值对行数据进行排序（MySQL 的默认表类型）。
外部表（external table）是只读表，其元数据存储在数据库中，但其数据存储在数据库之外。
簇表（Cluster Table）是一种特殊的存储数据方式，它将一组经常一起使用的表中相同的列存储在相同的数据块中。

虽然上面列了几种表类型，但下面主要还是介绍下堆组织的表（Heap-Organized Tables）的优化器访问路径，因为 Oracle 默认的表类型是堆表，所以需要重点了解下。

堆表介绍：

默认情况下，表被组织为一个堆，这意味着数据库将行放在它们最适合的位置，而不是按照用户指定的顺序。当用户添加行时，数据库将这些行放在数据段的第一个可用空间中。不能保证按照插入行的顺序检索行。

数据块和数据段中的行存储：

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

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

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

高水位线(HWM)是数据段中的一个点，超过该点，数据块将被取消格式化，并且从未被使用过。在 HWM 之下，块可以被格式化和写入，格式化和空，或者未格式化。低高水位线(低 HWM)标记这样一个点，在该点之下，所有数据块都被认为已格式化，因为它们包含数据或以前包含的数据。

在全表扫描过程中，数据库读取低 HWM（高水位）之前的所有块，这些块已知已被格式化，然后读取段位图以确定 HWM 和低 HWM 之间的哪些块已被格式化并且可以安全读取。数据库知道不要读取 HWM，因为这些块是未格式化的。

ROWID 对于行访问的重要性：

堆组织的表中的每一行都有一个 rowid，这个 rowid 对于这个表是唯一的，它对应于一个行段的物理地址。rowid 是一行的 10 字节物理地址。

rowid 指向特定的文件、块和行号。例如，rowid 为 AAAPecAAFAAAABSAAA，最后一个 AAA 表示行号。行号是行目录条目的索引。行目录条目包含指向块中行位置的指针。博主手绘了一张 rowid 的逻辑解析图。

rowid 以四段格式显示，OOOOOOFFFBBBBBBRRR，该格式分为以下几个部分：

OOOOOO：数据对象编号标识该段（在示例中为 AAAPec）。数据对象编号被分配给每个数据库段。同一段中的模式对象(如表簇)具有相同的数据对象编号。
FFF：与表空间相关的数据文件号标识包含该行的数据文件（在示例中为 AAF）。
BBBBBB：数据块编号标识包含该行的块（在示例中为 AAAABS）。块号与它们的数据文件相关，而不是与它们的表空间相关。因此，具有相同块号的两行可能位于同一表空间的不同数据文件中。
RRR：行号标识块中的行（在示例中为 AAA）。

在某些特殊情况下，rowid 可能会发生变化，尤其是在启用了行移动功能时。例如，rowid 可能会因为以下原因而改变：分区键更新、闪回表操作或收缩表操作等。如果禁用了行移动功能，那么在使用 Oracle 数据库的导出和导入工具时，rowid 也会发生变化。

Oracle 数据库在内部使用 rowids 来构建索引。例如，B 树索引中的每个键都与指向相关行地址的 rowid 相关联。物理 rowids 提供了对表行最快的访问，使数据库只需一次 I/O 就可以检索一行。

1、执行计划访问路径之全表扫描（Full Table Scans）

全表扫描从表中读取所有行，然后筛选出不符合选择条件的行。一般来说，当优化器无法使用不同的访问路径，或者另一个可用的访问路径成本较高时，它会选择全表扫描（并不是所有的执行计划适合走索引扫描，小表走全表，大表走索引）。下表显示了选择全表扫描的典型原因。

1.1、全表扫描（Full Table Scans）的工作原理

全表扫描是如何读取数据块的：

在全表扫描中，数据库按顺序读取高水位线（high water mark）下的每个格式化块。数据库只读取每个块一次。下图显示了表段的扫描，显示了扫描如何跳过高水位线以下的未格式化块。

此图显示了一系列水平块，前 5 块是灰色的，最后一个灰色块的右边缘标记为“低 HWM（Low HWM）”。接下来的 2 个是空的，接着是 1 个灰色的块，然后是 3 个空的块。

图例中空块表示为“从未使用，未格式化（Never Used, Unformatted）”；灰色块表示为“已使用（Used）”。箭头从左向右，落在每个用过的方块上，然后停在高水位线（HWM）处。

由于这些块是相邻的，数据库可以通过使 I/O 调用大于单个块来加速扫描，这被称为“多块读取（multiblock read）”。读取调用的大小从一个块到 DB_FILE_MULTIBLOCK_READ_COUNT 初始化参数指定的块数不等。例如，将此参数设置为 4 指示数据库在一次调用中最多读取 4 个块。

在全表扫描期间缓存块的算法很复杂。例如，数据库缓存块的方式因表的大小而异。

1.2、全表扫描（Full Table Scans）案例讲解

查询 employees 表，通过 salary 字段筛选出月薪超过 4000 美元的人，并且 salary 字段并没有创建索引：

SELECT salary
FROM   hr.employees
WHERE  salary > 4000;

通过 dbms_xplan.DISPLAY_CURSOR 方式查看 SQL 语句的执行计划，由于 salary 字段上不存在索引，优化器无法使用索引范围扫描，因此使用了全表扫描：

SQL_ID  54c20f3udfnws, child number 0
-------------------------------------
select salary from hr.employees where salary > 4000

Plan hashvalue: 3476115102

---------------------------------------------------------------------------
| Id| Operation         | Name      | Rows | Bytes |Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|  0| SELECTSTATEMENT  |           |      |       |    3 (100)|          |
|* 1|  TABLEACCESSFULL| EMPLOYEES |   98 |  6762 |    3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identifiedby operation id):
---------------------------------------------------

   1 - filter("SALARY">4000)

2、执行计划访问路径之按 Rowid 访问表（Table Access by Rowid）

rowid 是数据存储位置的内部表示。行的 rowid 指定了包含该行的数据文件和数据块以及该行在该块中的位置。通过指定行 ID 来定位行是检索单行的最快方法，因为它指定了行在数据库中的确切位置。

小提示 💥：Rowid 可以在不同版本之间更改，不建议根据位置访问数据，因为行可以移动。

当优化器选择按 Rowid 访问表时，在大多数情况下，数据库在扫描一个或多个索引后通过 rowid 访问表。但是，通过 rowid 访问表不需要遵循每次索引扫描。如果索引包含所有需要的列，则可能无法通过 rowid 进行访问。

2.1、按 Rowid 访问表（Table Access by Rowid）的工作原理

要通过 rowid 访问表，数据库将执行多个步骤。数据库执行以下操作：

从语句 WHERE 子句或通过一个或多个索引的索引扫描获取选定行的 Rowid，对于索引中不存在的语句中的列，可能需要表访问。
根据 Rowid 定位表中的每个选定行。

2.2、按 Rowid 访问表（Table Access by Rowid）案例讲解

查询 employees 表，通过 employee_id 字段筛选出来 id 大于 190 的行数据，employee_id 字段作为主键，那么数据库为其创建了 emp_emp_id_pk 主键索引：

SELECT *
FROM   employees
WHERE  employee_id > 190;

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

--------------------------------------------------------------------------------
|Id| Operation                           | Name     |Rows|Bytes|Cost(%CPU)|Time|
--------------------------------------------------------------------------------
| 0| SELECTSTATEMENT                    |             |  |    |2(100)|        |
| 1|  TABLEACCESSBYINDEXROWID BATCHED|EMPLOYEES    |16|1104|2  (0)|00:00:01|
|*2|   INDEXRANGESCAN                  |EMP_EMP_ID_PK|16|    |1  (0)|00:00:01|
--------------------------------------------------------------------------------

Predicate Information (identifiedby operation id):
---------------------------------------------------

   2 - access("EMPLOYEE_ID">190)

3、执行计划访问路径之表取样扫描（Sample Table Scans）

表取样扫描（Sample Table Scans）从简单的表或复杂的 SELECT 语句(如涉及联接和视图的语句)中检索随机样本数据。当语句 FROM 子句包含 sample 关键字时，数据库使用表取样扫描。FROM 子句中的 sample 关键字具有以下形式：

SAMPLE（sample_percent）：数据库读取表中指定百分比的行来执行表取样扫描。
SAMPLE BLOCK (sample_percent)：数据库读取指定百分比的表块来执行表取样扫描。

sample_percent 指定要包含在样本中的总行数或块数的百分比。该值必须在. 000001 到 100 的范围内，但不包括 100。此百分比表示块抽样中的每一行或每一组行被选为样本的概率。这并不意味着数据库准确地检索 sample_percent 的行。

小提示 💥：只有在全表扫描（full table scans）或索引快速全扫描（index fast full scans）期间才可能进行块采样。如果存在更有效的执行路径，则数据库不会对块进行采样。若要保证对特定表或索引进行块采样，请使用 FULL 或 INDEX_FFS 进行 Hint 干预。

3.1、表取样扫描（Sample Table Scans）案例讲解

此示例使用表取样扫描（Sample Table Scans）来访问员工表的 1%，按块而不是行进行采样。

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

通过 dbms_xplan.DISPLAY_CURSOR 方式查看 SQL 语句的执行计划：

-------------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     1 |    68 |     3  (34)|
|   1 |  TABLE ACCESS SAMPLE | EMPLOYEES   |     1 |    68 |     3  (34)|
-------------------------------------------------------------------------

二、B 树索引的访问路径

索引是一种可选结构，与表或表簇相关联，可以加快数据访问。通过在表的一列或多列上创建索引，在某些情况下，可以从表中检索一小组随机分布的行。索引是减少磁盘 I/O 的许多方法之一。

B 树是 balanced trees（平衡树）的缩写，是最常见的数据库索引类型。B 树索引是按范围划分的有序值列表。通过将一个键与一行或一系列行相关联，B 树为各种查询提供了出色的检索性能，包括精确匹配和范围搜索。

B 树索引结构：B-tree（balance tree）即平衡树，左右两个分支相对平衡

下图是博主手绘了一张 B 树索引的逻辑结构。分支块存储在两个关键字之间进行分支决策所需的最小关键字前缀。叶块包含每个索引数据值和用于定位实际行的相应 rowid。每个索引条目按(key，rowid)排序。叶块是双重链接的。

该图形分为两个带虚线边框的框，一个在另一个的上面。顶部标记为“分支块（Branch Blocks）”，下部标记为“叶块（Leaf Blocks）”。该图形包含一个方框树。顶部是一个包含以下值的框：0-40、41-90 等等。三个箭头指向第二排分支块。这一行中的一个块具有值 1-10、11-19、20-25 等等。相邻块的值为 41-48、49-53 等等。最后一个黑色的值为 200-209、210-220 等等。

最左边和最右边的块各有一对向下的箭头。最左边的箭头指向包含以下值的叶块（Leaf Blocks）：0,rowid、0,rowid、10,rowid 等等。右边的块包含以下值：11,rowid、11,rowid 等等。下一个块包含值:221,rowid、222,rowid 等等。最右边的块包含以下值：246,rowid、248,rowid 等等。除了最左边和最右边的块之外，底行中的每个叶块都通过双向箭头链接到两侧的块。

B 树索引是如何影响扫描的：

上图中显示了彼此相邻的叶块。例如，1-10 块位于 11-19 块的旁边和之前。此排序显示了连接索引条目的链表。然而，索引块不需要在索引段内按顺序存储。例如，246-250 块可以出现在段中的任何位置，包括直接在 1-10 块之前。因此，有序索引扫描必须执行单块 I/O。数据库必须读取索引块以确定下一步必须读取哪个索引块。

索引块体将索引条目存储在堆中，就像表行一样。例如，如果值 10 首先插入到表中，则具有键 10 的索引条目可能会插入到索引块的底部。如果接下来将 0 插入表中，则键 0 的索引条目可能会插入到 10 的条目的顶部。因此，块体中的索引条目不是按关键字顺序存储的。但是，在索引块中，行标题按键顺序存储记录。例如，标题中的第一条记录指向键为 0 的索引条目，依此类推，直到指向键为 10 的索引条目的记录。因此，索引扫描可以读取行标题以确定范围扫描的开始和结束位置，从而避免了读取块中每个条目的必要性。

B 树索引之唯一和非唯一索引：

在非唯一索引中，数据库通过将 rowid 作为额外的列附加到键来存储 rowid。该条目添加了一个长度字节，以使密钥唯一。例如，在上图所示的非唯一索引中，第一个索引键是 0,rowid，而不仅仅是 0。数据库先按索引键值，然后按 rowid 升序对数据进行排序。例如，条目排序如下：

0,AAAPvCAAFAAAAFaAAa
0,AAAPvCAAFAAAAFaAAg
0,AAAPvCAAFAAAAFaAAl
2,AAAPvCAAFAAAAFaAAm

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

B 树索引对于空值的处理：

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

在下列两个查询中，employees 表在 employee_id 上有主键索引，在 department_id 上有唯一索引。department_id 列可以包含 null 值，但 employee_id 列不能存在 null 值。

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

  COUNT(*)
----------
         1

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

  COUNT(*)
----------
         0

以下示例中显示了优化器为 employees 中所有部门 ID 的查询选择全表扫描。优化器无法在 employees 表中的 department_id 字段上使用索引，因为不能保证索引包含表中每一行的条目。

SQL> EXPLAIN PLAN FORSELECT department_id FROM employees;

Explained.

SQL> SELECT PLAN_TABLE_OUTPUT FROMTABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 3476115102

---------------------------------------------------------------------------
|Id | Operation         | Name      | Rows| Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
| 0 | SELECTSTATEMENT  |           | 107 |   321 |     2   (0)| 00:00:01 |
| 1 |  TABLEACCESSFULL| EMPLOYEES | 107 |   321 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

SQL> EXPLAIN PLAN FORSELECT department_id FROM employees WHERE department_id=10;

Explained.

SQL> SELECT PLAN_TABLE_OUTPUT FROMTABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 67425611

---------------------------------------------------------------------------
|Id| Operation        | Name              |Rows|Bytes|Cost (%CPU)| Time   |
---------------------------------------------------------------------------
| 0| SELECTSTATEMENT |                   | 1 |   3 |   1   (0)| 00:00:01|
|*1|  INDEXRANGESCAN| EMP_DEPARTMENT_IX | 1 |   3 |   1   (0)| 00:00:01|
---------------------------------------------------------------------------

Predicate Information (identifiedby operation id):
   1 - access("DEPARTMENT_ID"=10)

以下示例中显示了当条件中排除空值时，优化器会选择索引扫描：

SQL> EXPLAIN PLAN FORSELECT department_id FROM employees
WHERE department_id ISNOTNULL;

Explained.

SQL> SELECT PLAN_TABLE_OUTPUT FROMTABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 1590637672

---------------------------------------------------------------------------
| Id| Operation        | Name              |Rows|Bytes| Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0| SELECTSTATEMENT |                   |106| 318 |   1   (0)| 00:00:01|
|*1|  INDEXFULLSCAN | EMP_DEPARTMENT_IX |106| 318 |   1   (0)| 00:00:01|
---------------------------------------------------------------------------

Predicate Information (identifiedby operation id):
   1 - filter("DEPARTMENT_ID"ISNOTNULL)

1、执行计划访问路径之索引唯一扫描（Index Unique Scans）

索引唯一扫描最多返回 1 个 rowid。索引唯一扫描（Index Unique Scans）需要在 where 条件中使用等于运算符。具体来说，只有当 where 查询使用相等运算符引用唯一索引键中的所有列时，数据库才会执行唯一扫描，例如，当 WHERE prod_id=10 时。

unique 或 primary key 约束本身不足以生成索引唯一扫描（Index Unique Scans），因为该列上可能已经存在非唯一索引。下面示例中创建了一个 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 中在具有非唯一索引的列上创建 primary key 约束，从而导致索引范围扫描，而不是索引唯一扫描：

SQL> ALTER TABLE t_table ADDCONSTRAINT t_table_pk PRIMARY KEY(numcol);
SQL> SET AUTOTRACE TRACEONLY EXPLAIN
SQL> SELECT * FROM t_table WHERE numcol = 1;

Execution Plan
----------------------------------------------------------
Plan hash value: 868081059

---------------------------------------------------------------------------
| Id | Operation        | Name        |Rows  |Bytes |Cost (%CPU)|Time     |
---------------------------------------------------------------------------
|  0 | SELECTSTATEMENT |             |    1 |   13 |    1   (0)|00:00:01 |
|* 1 |  INDEXRANGESCAN| T_TABLE_IDX |    1 |   13 |    1   (0)|00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identifiedby operation id):
---------------------------------------------------
    1 - access("NUMCOL"=1)

1.1、索引唯一扫描（Index Unique Scans）的工作原理

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

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

该图形分为两个带虚线边框的框，一个在另一个的上面。顶部标记为“分支块（Branch Blocks）”，下部标记为“叶块（Leaf Blocks）”该图形包含一个方框树。顶部是一个包含以下值的框:0-40、41-90 等等。三个箭头指向第二排分支块。这一行中的一个块具有值：1-10、11-19、20-25 等等。相邻块的值为 41-48、49-53 等等。最后一个黑色的值为 200-209、210-220 等等。

最左边和最右边的块各有一对向下的箭头。最左边的箭头指向包含以下值的叶块：0,rowid、1,rowid、10,rowid 等等。右边的块包含以下值:11,rowid、12,rowid 等等。下一个块包含值:221,rowid、222,rowid 等等。最右边的块包含以下值:246,rowid、248,rowid 等等。除了最左边和最右边的块之外，底行中的每个叶块都通过双向箭头链接到两侧的块。

1.2、索引唯一扫描（Index Unique Scans）案例讲解

此示例使用唯一扫描从 products 表中检索一行，查询 products 表中 prod_id 字段为 19 的记录：

SELECT *
FROM   sh.products
WHERE  prod_id = 19;

因为 products 表中的 prod_id 列上存在主键索引，并且 WHERE 子句使用相等运算符引用所有列，所以优化器选择唯一扫描：

SQL_ID  3ptq5tsd5vb3d, child number 0
-------------------------------------
select * from sh.products where prod_id = 19

Plan hashvalue: 4047888317

---------------------------------------------------------------------------
| Id| Operation                   | Name   |Rows|Bytes|Cost (%CPU)|Time   |
---------------------------------------------------------------------------
|  0| SELECTSTATEMENT            |             |  |     |1 (100)|        |
|  1|  TABLEACCESSBYINDEXROWID| PRODUCTS    |1 | 173 |1   (0)|00:00:01|
|* 2|   INDEXUNIQUESCAN         | PRODUCTS_PK |1 |     |0   (0)|        |
---------------------------------------------------------------------------

Predicate Information (identifiedby operation id):
---------------------------------------------------

   2 - access("PROD_ID"=19)

2、执行计划访问路径之索引范围扫描（Index Range Scans）

索引范围扫描是对值的有序扫描。扫描的范围可以在两侧有界，或者在一侧或两侧无界。优化器通常为具有高选择性的查询选择范围扫描。

默认情况下，数据库以升序存储索引，并以相同的顺序扫描它们。例如，wehere 统计中 department_id >= 20 的查询使用范围扫描返回按索引键 20、30、40 等排序的行。如果多个索引条目具有相同的键，则数据库按 rowid 的升序返回它们，因此 0,AAAPvCAAFAAAAFaAAa 后面跟 0,AAAPvCAAFAAAAFaAAg，依此类推。

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

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

在条件中指定了索引的一个或多个前导列。条件指定一个或多个表达式和逻辑(布尔)运算符的组合，并返回值 TRUE、FALSE 或 UNKNOWN。条件的示例包括：

department_id = :id
department_id < :id
department_id > :id
以及索引中前导列的前述条件的组合，例如：department_id > :low AND department_id < :hi

一个索引键可能有 0、1 或多个值。

当索引可以满足 ORDER BY DESCENDING 子句时，优化器会将索引范围扫描视为降序。如果优化器选择全表扫描或另一个索引，那么可能需要一个提示来强制使用这个访问路径。索引(tbl_alias ix_name)和索引_DESC(tbl_alias ix_name)提示指示优化器使用特定的索引。

2.1、索引范围扫描（Index Range Scans）的工作原理

在索引范围扫描期间，Oracle 数据库从根到分支进行扫描。通常，扫描算法如下：

读取根块。
读取分支块。
交替执行以下步骤，直到检索到所有数据：

a. 读取叶块以获得rowid
b. 读取表块以检索行

在某些情况下，索引扫描会读取一组索引块，对 rowids 进行排序，然后读取一组表块。因此，为了扫描索引，数据库在叶块中向前或向后移动。例如，对介于 20 和 40 之间的 id 的扫描定位具有 20 或更大的最低键值的第一个叶块。扫描在叶节点的链表中水平进行，直到找到一个大于 40 的值，然后停止。

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

该图形分为两个带虚线边框的框，一个在另一个的上面。顶部标记为“分支块（Branch Blocks）”，下部标记为“叶块（Leaf Blocks）”该图形包含一个方框树。顶部是一个包含以下值的框：0-40、41-90 等等。三个箭头指向第二排分支块。这一行中的一个块具有值：1-10、11-19、20-25 等等。相邻块的值为 41-48、49-53 等等。最后一个黑色的值为：200-209、210-220 等等。

最左边和最右边的块各有一对向下的箭头。最左边的箭头指向包含以下值的叶块：0,rowid、0,rowid、10,rowid 等等。右边的块包含以下值：11,rowid、11,rowid 等等。下一个块包含值:221,rowid、222,rowid 等等。最右边的块包含以下值:246,rowid、248,rowid 等等。除了最左边和最右边的块之外，底行中的每个叶块都通过双向箭头链接到两侧的块。

2.2、索引范围扫描（Index Range Scans）案例讲解

此示例使用索引范围扫描从 employees 表中检索一组值。以下语句查询部门 20 中，薪金高于 1000 的雇员的记录：

SELECT *
FROM   employees
WHERE  department_id = 20
AND    salary > 1000;

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

SQL_ID  brt5abvbxw9tq, child number 0
-------------------------------------
SELECT * FROM   employees WHERE  department_id = 20AND    salary > 1000

Plan hashvalue: 2799965532

-------------------------------------------------------------------------------------------
|Id | Operation                           | Name             |Rows|Bytes|Cost(%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECTSTATEMENT                    |                  |    |     | 2 (100)|        |
|*1 |  TABLEACCESSBYINDEXROWID BATCHED| EMPLOYEES        |  2 | 138 | 2   (0)|00:00:01|
|*2 |   INDEXRANGESCAN                  | EMP_DEPARTMENT_IX|  2 |     | 1   (0)|00:00:01|
-------------------------------------------------------------------------------------------

Predicate Information (identifiedby operation id):
---------------------------------------------------

   1 - filter("SALARY">1000)
   2 - access("DEPARTMENT_ID"=20)

2.3、索引范围扫描（Index Range Scans）降序案例讲解

此示例使用索引按排序顺序从 employees 表中检索行。以下语句按降序查询部门 20 中的雇员记录：

SELECT *
FROM   employees
WHERE  department_id < 20
ORDER BY department_id DESC;

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

SQL_ID  8182ndfj1ttj6, child number 0
-------------------------------------
SELECT * FROM employees WHERE department_id<20ORDERBY department_id DESC

Plan hashvalue: 1681890450
---------------------------------------------------------------------------
|Id| Operation                    | Name      |Rows|Bytes|Cost(%CPU)|Time |
---------------------------------------------------------------------------
| 0| SELECTSTATEMENT             |                 | |   |2(100)|        |
| 1|  TABLEACCESSBYINDEXROWID |EMPLOYEES        |2|138|2  (0)|00:00:01|
|*2|   INDEXRANGESCAN DESCENDING|EMP_DEPARTMENT_IX|2|   |1  (0)|00:00:01|
---------------------------------------------------------------------------

Predicate Information (identifiedby operation id):
---------------------------------------------------

   2 - access("DEPARTMENT_ID"<20)

数据库定位包含最高键值(等于或小于 20)的第一个索引叶块。然后，扫描通过叶节点的链表向左水平进行。数据库从每个索引条目中获取 rowid，然后检索由 rowid 指定的行。

3、执行计划访问路径之索引全扫描（Index Full Scans 或者 Index Full Scans (MIN/MAX)）

与全表扫描（Table Full Scan）相比，Index Full Scan 只扫描了索引文件，因此通常比全表扫描更快速。但是，在索引文件较大的情况下，仍可能会导致性能瓶颈和资源浪费。

索引全扫描按顺序读取整个索引。索引全扫描可以消除单独的排序操作，因为索引中的数据是按索引键排序的。优化器在以下情况下都会考虑进行索引全扫描：

where 条件中引用索引中的列，该列不必是前导列。
未指定 where 条件，但满足以下所有条件：

a. 表和查询中的所有列都在索引中。
b. 至少有一个索引列不为空。

查询包括对索引的不可为空的列的 ORDER BY。

3.1、索引全扫描（Index Full Scans）的工作原理

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

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

下图说明了索引全扫描，一条语句请求按 department_id 排序的部门表 departments 记录。

该图形分为两个带虚线边框的框，一个在另一个的上面。顶部标记为“分支块（Branch Blocks）”，下部标记为“叶块（Leaf Blocks）”该图形包含一个方框树。顶部是一个包含以下值的框：0-40、41-80 等等。三个箭头指向第二排分支块。这一行中的一个块具有值：1-10、11-19、20-25 等等。相邻块的值为：41-48、49-53 等等。最后一个黑色的值为 200-209、210-220 等等。

最左边和最右边的块各有一对向下的箭头。最左边的箭头指向包含以下值的叶块：0,rowid、1,rowid、10,rowid 等等。右边的块包含以下值：11,rowid、12,rowid 等等。下一个块包含值：221,rowid、222,rowid 等等。最右边的块包含以下值：246,rowid、247,rowid 等等。除了最左边和最右边的块之外，底行中的每个叶块都通过双向箭头链接到两侧的块。

3.2、INDEX FULL SCAN 和 INDEX FULL SCAN (MIN/MAX)的区别：

INDEX FULL SCAN (MIN/MAX)是一种特殊的索引扫描方式，用于快速查找最小或最大值。它是基于 B-Tree 索引实现的，通过遍历索引树的最左或最右分支来查找最小或最大的键值。与普通的 INDEX FULL SCAN 相比，INDEX FULL SCAN (MIN/MAX)只需要扫描部分索引树而不是整个索引文件，因此通常比普通的索引扫描更快。

需要注意的是，使用 INDEX FULL SCAN (MIN/MAX)必须保证索引列是有序的，否则将无法正确地找到最小或最大值。此外，如果查询涉及到的数据量较大，则继续使用该方法可能会导致性能下降。

总之，INDEX FULL SCAN (MIN/MAX)适用于需要查找最小和最大值的情况，并且索引列已经有序的情况下，可以提高查询的性能。但是，在使用时需要权衡其适用范围和实际性能表现。

而 INDEX FULL SCAN 则是指数据库系统在执行查询时对某个索引进行全表扫描的操作，即读取整个索引文件中的所有数据页来查找符合条件的数据。它通常发生在没有合适的索引可供使用或优化器选择错误索引的情况下，会导致性能瓶颈和资源浪费。

3.3、索引全扫描（Index Full Scans 或者 Index Full Scans (MIN/MAX)）案例讲解

INDEX FULL SCAN (MIN/MAX)：

jobid 列为索引键，索引全扫描对有索引键的列的叶子节点和根节点的数据进行全扫描。

select max(jobid) from yg;
select min(jobid) from yg;

INDEX FULL SCAN：

此示例使用索引全扫描来满足带有 ORDER BY 子句的查询。以下语句按部门 ID 的顺序，查询部门的 ID 和名称：

SELECT department_id, department_name
FROM   departments
ORDER BY department_id;

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

SQL_ID  94t4a20h8what, child number 0
-------------------------------------
select department_id, department_name from departments orderby department_id

Plan hashvalue: 4179022242

------------------------------------------------------------------------
|Id | Operation                 | Name     |Rows|Bytes|Cost(%CPU)|Time |
------------------------------------------------------------------------
|0| SELECTSTATEMENT            |            |   |   |2 (100)|         |
|1|  TABLEACCESSBYINDEXROWID|DEPARTMENTS |27 |432|2   (0)|00:00:01 |
|2|   INDEXFULLSCAN           |DEPT_ID_PK  |27 |   |1   (0)|00:00:01 |
------------------------------------------------------------------------

数据库定位第一个索引叶块，然后水平向右遍历叶节点的链表。对于每个索引条目，数据库从条目中获取 rowid，然后检索由 rowid 指定的表行。因为索引是按 department_id 排序的，所以数据库避免了对检索到的行进行排序的单独操作。

4、执行计划访问路径之索引快速全扫描（Index Fast Full Scans）

索引快速全扫描以未排序的顺序读取索引块，因为它们存在于磁盘上。这种扫描不使用索引来探测表，而是读取索引而不是表，本质上是将索引本身用作表。

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

小提示 💥：与索引全扫描（Index Full Scans）不同，索引快速全扫描（Index Fast Full Scans）无法消除排序操作，因为它不会按顺序读取索引。

4.1、索引快速全扫描（Index Fast Full Scans）的工作原理

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

索引快速全扫描（(Index Fast Full Scans）是一种高效的索引扫描方式，它扫描整个索引来查找满足查询条件的行。Oracle 在以下情况可能会使用索引快速全扫描：

1、查询中的列都被包含在索引中：如果 select 语句中的列都被包含在组合索引中，而且 where 条件中没有出现组合索引的引导列，并且需要检索出大部分数据，那么可能会执行 Index Fast Full Scan。

2、查询涉及到索引列的函数操作：如果查询条件涉及到索引列的函数操作，而该函数操作对于索引列中的每个值都是可确定的，那么 Oracle 优化器可能会选择使用 Index Fast Full Scan 来提高查询性能。

注意：Index Fast Full Scan 并不总是比其他扫描方式更快。在某些情况下，全表扫描可能比使用 Index Fast Full Scan 更快。因此，Oracle 优化器会根据查询条件、表统计信息、索引统计信息等多种因素来决定是否使用 Index Fast Full Scan。在实际应用中，应该根据具体情况进行性能测试和调优，以确定最佳的查询策略。

4.2、索引快速全扫描（Index Fast Full Scans）案例讲解

下面例子中使用了 Hint 干预让执行计划走索引快速全扫描。索引快速全扫描的 Hint 干预语法为：/*+ INDEX_FFS(table_name index_name) */

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

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

SQL_ID  fu0k5nvx7sftm, child number 0
-------------------------------------
select/*+ index_ffs(departments dept_id_pk) */count(*) from departments

Plan hashvalue: 3940160378
--------------------------------------------------------------------------
| Id | Operation             | Name       | Rows  |Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|  0 | SELECTSTATEMENT      |            |       |    2 (100)|          |
|  1 |  SORTAGGREGATE       |            |     1 |           |          |
|  2 |   INDEXFASTFULLSCAN| DEPT_ID_PK |    27 |    2   (0)| 00:00:01 |
--------------------------------------------------------------------------

5、执行计划访问路径之索引跳跃扫描（Index Skip Scans）

当组合索引的前置列被“跳过（skipped）”或未在查询中指定时，会发生索引跳跃扫描。通常来说，跳过扫描索引块（skip scanning index blocks）比扫描表块（scanning table blocks）要快很多，也比执行完全索引扫描（full index scans）快。

当满足以下条件时，优化器会考虑进行索引跳跃扫描：

where 条件中未指定组合索引的前置列：例如，表中创建了一个组合索引，复合索引键是（cust_gender，cust_email），where 条件中不引用 cust_gender 列
在组合索引的前置列中存在很少的非重复值，但是在索引的非前置列中存在许多非重复值：例如，如果组合索引键是(cust_gender，cust_email)，那么 cust_gender 列只有两个不同的值，但是 cust_email 有数千个值

5.1、索引跳跃扫描（Index Skip Scans）的工作原理

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

索引前导列中不同值的数量决定了逻辑子索引的数量。该数字越小，优化器必须创建的逻辑子索引就越少，扫描效率就越高。扫描分别读取每个逻辑索引，并“跳过（skips）”非前导列上不满足筛选条件的索引块。

5.2、索引跳跃扫描（Index Skip Scans）案例讲解

下面示例中使用索引跳跃扫描来满足 customers 表的查询，customers 表中的 cust_gender 字段只包含有 M 或者 F 的数据，并且对列(cust_gender，cust_email)创建了一个复合索引：

###cust_gender列和cust_email列上的部分数据：
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

###创建索引：
CREATE INDEX cust_gender_email_ix
  ON sh.customers (cust_gender, cust_email);

将 cust_email 字段当做 where 条件进行查询：

SELECT *
FROM   sh.customers
WHERE  cust_email = 'Abbey@company.example.com';

即使 where 子句中没有指定 cust_ender，数据库也可以使用 CUST_GENDER_EMAIL_IX 索引的跳跃扫描。在示例索引中，前置列 cust_ender 有两个可能的值：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

通过 cust_email 字段搜索数据为 Abbey@company.example.com 时，数据库首先搜索以 F 开头的子索引，然后搜索以 M 开头的子指数。查询转换器对查询做如下处理，查询转换器觉得这种写法成本更低：

( SELECT *
FROM   sh.customers
WHERE  cust_gender = 'F'
AND    cust_email = 'Abbey@company.example.com' )
UNIONALL
( SELECT *
FROM   sh.customers
WHERE  cust_gender = 'M'
AND    cust_email = 'Abbey@company.example.com' )

执行计划如下：

SQL_ID  d7a6xurcnx2dj, child number 0
-------------------------------------
SELECT * FROM   sh.customers WHERE  cust_email = 'Abbey@company.example.com'

Plan hashvalue: 797907791

-----------------------------------------------------------------------------------------
|Id| Operation                          | Name               |Rows|Bytes|Cost(%CPU)|Time|
-----------------------------------------------------------------------------------------
| 0|SELECTSTATEMENT                    |                      |  |    |10(100)|        |
| 1| TABLEACCESSBYINDEXROWID BATCHED| CUSTOMERS            |33|6237|  10(0)|00:00:01|
|*2|  INDEXSKIPSCAN                   | CUST_GENDER_EMAIL_IX |33|    |   4(0)|00:00:01|
-----------------------------------------------------------------------------------------

Predicate Information (identifiedby operation id):
---------------------------------------------------

   2 - access("CUST_EMAIL"='Abbey@company.example.com')
       filter("CUST_EMAIL"='Abbey@company.example.com')

6、执行计划访问路径之索引连接扫描（Index Join Scans）

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

在某些情况下，避免表访问是最具成本效益的选择。在下列情况下，优化器会考虑进行索引连接扫描：

多个索引的散列连接检索查询请求的所有数据，而不需要访问表。
从表中检索行的成本高于读取索引而不从表中检索行的成本。索引连接通常开销很大。例如，当扫描两个索引并连接它们时，选择最具选择性的索引，然后探测表，通常成本更低。

6.1、索引连接扫描（Index Join Scans）的工作原理

索引连接包括扫描多个索引，然后对从这些扫描中获得的 rowids 使用散列连接来返回行。在索引连接扫描中，总是避免表访问。例如，联接单个表上的两个索引的过程如下：

扫描第一个索引以检索行 Rowid。
扫描第二个索引以检索行 Rowid。
通过 Rowid 执行哈希连接以获取行。

6.2、索引连接扫描（Index Join Scans）案例讲解

下面例子中使用了 Hint 干预让执行计划走索引连接扫描，查询 last*name 字段中以 A 开头的雇员的姓氏（last_name）和电子邮件（email）。索引连接扫描的 Hint 干预语法为：/*+ INDEX_JOIN(table_name) \*/

SELECT /*+ INDEX_JOIN(employees) */ last_name, email
FROM   employees
WHERE  last_name like 'A%';

（last_name，first_name）和 email 列上存在单独的索引。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

执行计划中检索 emp_email_uk 索引中的所有 Rowid，然后检索 emp_name_ix 中以 a 开头的姓氏的 rowid，并且使用哈希联接（HASH JOIN）来搜索这两组 rowid 以查找匹配项。例如，rowid AAAVgdAALAAAABSABD 出现在两个 rowid 集合中，因此数据库在 employees 表中查找与该 Rowid 对应的记录。

SQL_ID  d2djchyc9hmrz, child number 0
-------------------------------------
SELECT/*+ INDEX_JOIN(employees) */ last_name, email FROM   employees
WHERE  last_name like'A%'

Plan hashvalue: 3719800892
-------------------------------------------------------------------------------------------
| Id  | Operation              | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECTSTATEMENT       |                  |       |       |     3 (100)|          |
|*  1 |  VIEW                  | index$_join$_001 |     3 |    48 |     3  (34)| 00:00:01 |
|*  2 |   HASHJOIN            |                  |       |       |            |          |
|*  3 |    INDEXRANGESCAN    | EMP_NAME_IX      |     3 |    48 |     1   (0)| 00:00:01 |
|   4 |    INDEXFASTFULLSCAN| EMP_EMAIL_UK     |     3 |    48 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identifiedby operation id):
---------------------------------------------------
   1 - filter("LAST_NAME"LIKE'A%')
   2 - access(ROWID=ROWID)
   3 - access("LAST_NAME"LIKE'A%')

三、位图索引的访问路径

在介绍位图索引的访问路径之前首先让我们了解什么是位图索引，位图索引和 B 树索引的区别是什么。

位图索引的概念：

位图索引用位图来表示索引，Oracle 对于选择度低的列的每个键值建立一个位图。位图中的每一位可能对应多个列，位图中位等于 1 表示特定的行含有此位图表示的键值。

位图索引的优缺点：

优点：
- 减少即席查询的响应时间：位图索引能够高效处理大量数据的即席查询，提高查询速度。
- 节约索引数据空间：与其他类型索引相比，位图索引占用的空间较少。
- 高效的并行 DML 和 LOAD 操作：位图索引支持高效的并行数据处理操作。
- 创建索引时更高效：位图索引创建时不需要排序，且按位存储，所需空间少，创建速度快。
- 支持空值查询：位图索引允许键值为空，且对空值查询效率较高。
- 直接计数：可以通过位图索引直接获取统计数据，如行数等。
缺点：
- 不适合选择度高的列：位图索引在选择度高的列上可能表现不佳。
- 频繁更新可能导致性能下降：因为更新索引用的是行锁（可能锁定多行），而不是排它锁，所以如果有比较频繁的 insert、update 等操作，可能导致性能下降。
- 可能会溢出：当索引数据块难以放下整个索引值时，会发生溢出，导致查询效率降低。
- 受 Oracle 版本和特性限制：位图索引在 Trusted Oracle 中不支持，不能被规则优化器（RBO）使用，不能用于分区表的全局索引，不支持 build 或 rebuild 的 ONLINE 选项等。

位图索引的适用场合：

适用于有大量重复值的列查询：位图索引在处理大量重复值的列时表现优异。
适用于静态数据：位图索引不适合频繁更新的列，更适合静态数据。
适用于批量插入的数据：对于批量插入的数据，位图索引能够减少索引更新的次数，提高性能。
适用于 OLAP 应用：由于并发 DML 操作锁定的是整个位图段的大量数据行，位图索引主要用于 OLAP 应用，也可以用于 OLTP 中主要为读操作的表。

总结：

位图索引适合于数据仓库中，不适合 OLTP 中，在 Oracle 这种关系数据库中使用的非常少（博主目前还没有见过在 Oracle 项目上有用位图索引的）。

参考官方文档学习，可以看到位图索引的访问路径有四个：

四、簇表的访问路径

可能有些小伙伴第一次听到簇表，簇表是 oracle 中表类型（在 oracle 中默认表的类型为堆表）。既然要学习簇表的访问路径，那么先来学习一下簇表的结构，然后再开始访问路径的学习。

簇表介绍：

簇表是一组共享公共列并在相同的块中存储相关数据的表。当表被聚集时，单个数据块可以包含多个表中的行。例如，一个块可以存储 employees 表和 departments 表中的行，而不是只存储一个表中的行。

聚集键（cluster key）是聚集表共有的一列或多列。例如，employees 和 departments 表共享 department_id 列。在创建簇表和创建添加到簇表中的每个表时，需要指定簇键（也称聚集键，cluster key）。

簇键值是一组特定行的簇键列的值。包含相同分类键值(如 department_id=20)的所有数据在物理上存储在一起。每个簇键值在簇和簇索引中只存储一次，不管不同表中有多少行包含该值。

打个比方，假设一位人力资源经理有两个书架：一个装有员工文件夹，另一个装有部门文件夹。用户经常要求为特定部门的所有员工提供文件夹。为了便于检索，经理将所有箱子重新排列在一个书架上。她按部门 ID 划分这些框，因此，部门 20 的所有员工文件夹和部门 20 本身的文件夹都在一个框中；部门 100 中的员工文件夹和部门 100 的文件夹在另一个框中，依此类推。

当表主要被查询（但未被修改）并且表中的记录经常被一起查询或连接时，可以考虑对表进行聚类，也就是使用簇表这个表类型。由于表簇将不同表的相关行存储在同一数据块中，因此与非聚集表相比，正确使用簇表具有以下优点：

簇表的联接减少了磁盘 I/O。
簇表连接的访问时间缩短。
存储相关表和索引数据所需的存储空间更少，因为不会为每一行重复存储簇键值。

如果是一下情况那么就不适合使用簇表：

表经常需要更新（update）操作。
表经常需要全表扫描，例如，表经常需要 count(*)全表数据，或者需要全表查询数据。
表偶尔需要 truncate 清空全部数据的操作。

簇表之簇索引：簇索引是一种特殊的索引，说直白点就是为簇表创建簇索引，使用簇索引来定位簇表的数据，加快数据访问

簇索引是使用索引来定位簇表的数据。簇索引是簇键上的 B 树索引。必须先创建簇索引，然后才能将任何行插入到簇表中。

创建簇表和关联的簇索引：

假设使用聚集键（cluster key）department_id 创建了簇表 employees_departments_cluster，如下例所示：

CREATE CLUSTER employees_departments_cluster
(department_id NUMBER(4))
SIZE 512;

CREATE INDEX idx_emp_dept_cluster
ON CLUSTER employees_departments_cluster;

因为没有指定 HASHKEYS 子句，所以 employees_departments_cluster 是一个索引簇集。前面的示例在聚集键（cluster key）department_id 上创建了一个名为 idx_emp_dept_cluster 的索引。

索引簇中创建表：

创建 employees 和 departments 簇表，将 department_id 列指定为聚集键（cluster key），如下所示(省略号表示列说明的位置)：

CREATE TABLE employees ( ... )
CLUSTER employees_departments_cluster (department_id);

CREATE TABLE departments ( ... )
CLUSTER employees_departments_cluster (department_id);

假设向 employees 和 departments 表中添加了行。数据库将 employees 和 departments 表中每个部门的所有行物理存储在同一个数据块中。数据库将行存储在一个堆中，并使用索引来定位它们。

博主手绘下图显示了 employees_departments_cluster 表簇，其中包含雇员和部门。数据库将部门 20 和部门 110 的雇员的行存储在一起，依此类推。如果表没有聚集，则数据库不能确保相关的行存储在一起。

图的左边是一个标有“簇表（Clustered Tables）”的数据库圆柱图标。圆柱体的顶部是一个正方形。正方形指向一个看起来像一张纸的图标。该图标的标签为“聚集关键部门标识（Clustered Key department_id）”图标显示了部门 20 的一组行和部门 110 的另一组行。

在图的右边是一个标有“非集群表（Unclustered Tables）”的数据库圆柱图标。圆柱体的顶部是两个正方形。左边的方块指向一个看起来像一张纸的图标。该图标标记为“雇员（employees）”，包含雇员行。右边的方块标记为“部门（departments）”，显示了部门 20 的一行和部门 110 的另一行。

B 树簇索引将簇键值与包含数据的块的数据库块地址(DBA)相关联。例如，键 20 的索引条目显示了包含部门 20 中员工数据的块的地址：

20,AADAAAA9d

簇索引是单独管理的，就像非聚集表上的索引一样，并且可以存在于与表聚集不同的表空间中。

簇表之 hash 簇表：hash 簇类似于索引簇，只是索引键被散列函数所取代。不存在单独的聚集索引。在 hash 簇中，数据就是索引。

hash 簇类似于索引簇，只是索引键被散列函数所取代。不存在单独的聚集索引。在 hash 簇中，数据就是索引。

对于索引表或索引簇，Oracle 数据库使用存储在单独索引中的键值来定位表行。要在索引表或表簇中查找或存储一行，数据库必须至少执行两次 I/o：

在索引中查找或存储键值的一个或多个 I/o
另一个 I/O 读取或写入表或表簇中的行

为了在 hash 簇中查找或存储一行，Oracle 数据库将散列函数应用于该行的簇键值。产生的哈希值对应于集群中的一个数据块，数据库代表发出的语句读取或写入该数据块。

哈希是存储表数据的一种可选方式，可以提高数据检索的性能。当满足以下条件时，hash 簇可能是有益的：

表被查询的次数比被修改的次数多得多
哈希键列经常使用相等条件进行查询，例如，其中 department_id=20。对于这种查询，会对群集键值进行哈希处理。散列键值直接指向存储行的磁盘区域
可以合理地猜测散列键的数量以及每个键值存储的数据大小

创建 hash 簇表：

要创建 hash 簇表，可以使用与索引簇相同的 CREATE CLUSTER 语句，并添加一个散列键。群集的哈希值的数量取决于哈希键。

与索引簇的键一样，簇键是由簇中的表共享的单列或组合键。散列键值是插入到簇键列中的实际或可能的值。例如，如果集群键是 department_id，那么散列键值可以是 10、20、30 等等。

Oracle 数据库使用散列函数，该函数接受无限数量的散列键值作为输入，并将它们分类到有限数量的桶中。每个存储桶都有一个唯一的数字 ID，称为哈希值。每个哈希值映射到存储与哈希值对应的行的块的数据库块地址(部门 10、20、30 等)。

在下面的示例中，可能存在的部门数量是 100，因此 HASHKEYS 被设置为 100：

CREATE CLUSTER employees_departments_cluster
(department_id NUMBER(4))
SIZE 8192 HASHKEYS 100;

创建 employees_departments_cluster 后，可以在集群中创建 employees 和 departments 表。然后，您可以像在索引簇中一样将数据加载到散列簇中。

1、执行计划访问路径之簇扫描（Cluster Scans）

簇索引是使用索引来定位簇表的数据。簇索引是簇键上的 B 树索引，簇扫描（Cluster Scans）从存储在索引集群中的表中检索具有相同集群键值的所有行。

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

1.1、簇扫描（Cluster Scans）的工作原理

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

例如，如果 employees2 和 departments2 表在 emp_dept_cluster 簇索引中，并且聚集键是 department_id，则数据库将部门 10 中的所有雇员存储在同一块中，部门 20 中的所有雇员存储在同一块中，依此类推。

B 树簇索引将簇键值与包含数据的块的数据库块地址(DBA)相关联。例如，键 30 的索引条目显示了包含部门 30 中雇员行的块的地址：

30,AADAAAA9d

当用户请求集群中的行时，数据库扫描索引以获取包含这些行的块的 DBA。然后，Oracle 数据库根据这些 DBA 定位行。

1.2、簇扫描（Cluster Scans）案例讲解

此示例在 department_id 列上对员工表（employees）和部门表（departments）进行聚类，然后在聚类中查询单个部门。

创建一个簇表、簇索引和簇中的表，如下所示：

CREATE CLUSTER employees_departments_cluster
   (department_id NUMBER(4)) SIZE512;

CREATEINDEX idx_emp_dept_cluster
   ON CLUSTER employees_departments_cluster;

CREATETABLE employees2
   CLUSTER employees_departments_cluster (department_id)
   ASSELECT * FROM employees;

CREATETABLE departments2
   CLUSTER employees_departments_cluster (department_id)
   ASSELECT * FROM departments;

查询部门 30 中的员工：

SELECT *
FROM   employees2
WHERE  department_id = 30;

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

SQL_ID  b7xk1jzuwdc6t, child number 0
-------------------------------------
SELECT * FROM employees2 WHERE department_id = 30

Plan hashvalue: 49826199

---------------------------------------------------------------------------
|Id| Operation            | Name               |Rows|Bytes|Cost(%CPU)|Time|
---------------------------------------------------------------------------
| 0| SELECTSTATEMENT     |                    |   |    | 2 (100)|        |
| 1|  TABLEACCESS CLUSTER| EMPLOYEES2         | 6 |798 | 2   (0)|00:00:01|
|*2|   INDEXUNIQUESCAN  |IDX_EMP_DEPT_CLUSTER| 1 |    | 1   (0)|00:00:01|
---------------------------------------------------------------------------

Predicate Information (identifiedby operation id):
---------------------------------------------------

   2 - access("DEPARTMENT_ID"=30)

2、执行计划访问路径之哈希扫描（Hash Scans）

散列簇类似于索引簇，只是索引键被散列函数所取代。不存在单独的聚集索引。在散列簇中，数据就是索引。数据库使用哈希扫描来根据哈希值定位哈希簇中的行。

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

2.1、哈希扫描（Hash Scans）的工作原理

在散列簇中，具有相同散列值的所有行都存储在同一个数据块中。

为了对集群执行散列扫描，Oracle 数据库首先通过将散列函数应用于由语句指定的集群键值来获得散列值。然后，Oracle 数据库扫描包含具有该哈希值的行的数据块。

2.2、哈希扫描（Hash Scans）案例讲解

此示例对 department_id 列上的 employees 和 departments 表进行哈希运算，然后在群集中查询单个部门。

创建一个散列簇和簇中的表，如下所示：

CREATE CLUSTER employees_departments_cluster
   (department_id NUMBER(4)) SIZE8192HASHKEYS100;

CREATETABLE employees2
   CLUSTER employees_departments_cluster (department_id)
   ASSELECT * FROM employees;

CREATETABLE departments2
   CLUSTER employees_departments_cluster (department_id)
   ASSELECT * FROM departments;

查询部门 30 中的员工：

SELECT *
FROM   employees2
WHERE  department_id = 30

为了执行散列扫描，Oracle 数据库首先通过对键值 30 应用散列函数来获得散列值，然后使用该散列值来扫描数据块并检索行(步骤 1)。

SQL_ID  919x7hyyxr6p4, child number 0
-------------------------------------
SELECT * FROM employees2 WHERE department_id = 30

Plan hashvalue: 2399378016

----------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost  |
----------------------------------------------------------------
|   0 | SELECTSTATEMENT  |            |       |       |     1 |
|*  1 |  TABLEACCESSHASH| EMPLOYEES2 |    10 |  1330 |       |
----------------------------------------------------------------

Predicate Information (identifiedby operation id):
---------------------------------------------------

   1 - access("DEPARTMENT_ID"=30)

写在最后

这篇文章到这里就算结束了，前前后后整理了 7 天，撰写超过 10 小时，因为有些访问路径博主之前也不了解，是边看官方文档边学习，然后再整理到博客的，所以看在博主这么用心，这么“拼”的份上，不给个 三连 支持一下，良心真的不会痛吗 😁？，并且文章 3.5 万字了，需要各位小伙伴慢慢学习，细细品，保证你越读越有味，比追剧还上瘾！

参考资料

[1]Optimizer Access Paths: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/tgsql/optimizer-access-paths.html#GUID-00711237-35D3-4CFC-A234-59B3EC53DCD1

想了解更多干货，可通过下方扫码关注