PART1-Oracle关系数据结构-索引和索引组织表

3. 索引组织表

3.1. 索引概述

索引是与表或表簇关联的可选结构，有时可以加快数据访问速度。通过在表的一个或多个列上创建索引，在某些情况下，您可以从表中检索一小部分随机分布的行。索引是减少磁盘I/O的众多方法之一。
如果堆组织表没有索引，那么数据库必须执行全表扫描才能找到一个值。例如，没有索引的情况下，查询 hr.departments 表中 location=2700 的记录需要数据库搜索每个表块中的每一行。这种方法随着数据量的增加，扩展性很差。
打个比方，假设一位人力资源经理有一排纸箱。员工信息的文件夹随机插入这些箱子中。Whalen（ID 200）的文件夹在箱子1从底部数起第10个位置，而King（ID 100）的文件夹在箱子3的底部。为了找到一个文件夹，经理需要从箱子1的底部逐一查看每一个文件夹，然后逐个箱子查找，直到找到目标文件夹。为了加快查找速度，经理可以创建一个索引，按顺序列出每个员工ID及其文件夹位置：

ID 100: Box 3, position 1 (bottom) 
ID 101: Box 7, position 8 
ID 200: Box 1, position 10 . . .

类似地，经理可以为员工的姓氏、部门ID等分别创建独立的索引。这样，无论是按员工ID、姓氏还是部门ID查找，经理都可以通过相应的索引快速定位到所需的文件夹位置，而无需逐一查找每个文件夹。
一般来说，在以下任何情况下，都可以考虑在列上创建索引：

• 索引列经常被查询 并且返回表中总行数的一小部分。
• 索引列存在参照完整性约束。索引可以避免在更新父表主键、合并到父表或从父表删除时，所需的全表锁。
• 在表上放置唯一键约束，并且您希望手动指定索引及所有索引选项。

3.1.1. 索引特征

索引是独立于其关联对象中的数据的逻辑和物理独立的模式对象。因此，可以在不实际影响索引表的情况下删除或创建索引。

注意：如果删除索引，应用程序仍然可以正常工作。然而，访问之前已索引的数据可能会变慢。

索引的存在与否不需要更改任何SQL语句的措辞。索引是一条快速访问单行数据的路径。它只影响执行速度。给定已索引的数据值，索引会直接指向包含该值的行的位置。

数据库在索引创建后会自动维护和使用这些索引。数据库还会自动将数据的更改（例如添加、更新和删除行）反映在所有相关索引中，无需用户执行额外操作。即使插入行，索引数据的检索性能仍保持几乎恒定。然而，表上存在过多索引会降低DML（数据操作语言）性能，因为数据库还必须更新这些索引。

索引具有以下属性：

• 可用性：索引可以是可用的（默认）或不可用的。不可用的索引不会被DML操作维护，并且会被优化器忽略。不可用的索引可以提高批量加载的性能。与其删除索引后再重新创建，不如将索引设为不可用，然后重建它。不可用的索引和索引分区不占用空间。当您将一个可用的索引设为不可用时，数据库会删除其索引段。

• 可见性：索引可以是可见的（默认）或不可见的。不可见的索引会被DML操作维护，但默认情况下不会被优化器使用。将索引设为不可见是替代将其设为不可用或删除它的一种方法。不可见的索引在删除前进行测试或临时使用索引而不影响整个应用程序时特别有用。

3.1.1.1. 索引键和列

键是可以用于构建索引的一组列或表达式。尽管术语经常互换使用，但索引和键是不同的。索引是存储在数据库中的结构，用户可以使用SQL语句管理它们。键严格来说是一个逻辑概念。

以下语句在示例表 oe.orders 的 customer_id 列上创建一个索引：

CREATE INDEX ord_customer_ix ON orders (customer_id);

在上述语句中，customer_id 列是索引键。索引本身被命名为 ord_customer_ix。

注意：主键和唯一键会自动创建索引，但您可能需要在外键上创建索引。

3.1.1.2. 复合索引

复合索引，也称为连接索引，是在表的多个列上创建的索引。复合索引中的列应按照最适合查询检索数据的顺序排列，而不必在表中相邻。

复合索引可以加速 SELECT 语句中 WHERE 子句引用了所有或前导部分复合索引列的数据检索。因此，定义中列的顺序非常重要。一般来说，最常访问的列应放在前面。

例如，假设一个应用程序经常查询employees表中的last_name、job_id和salary列。并且假设last_name具有高基数，这意味着相对于表的行数，不同值的数量很大。您可以按以下列顺序创建索引：

CREATE INDEX employees_ix ON employees (last_name, job_id, salary);

访问所有三列、仅 last_name 列或仅 last_name 和 job_id 列的查询会使用此索引。在此示例中，不访问 last_name 列的查询不会使用该索引。

注意：在某些情况下，例如当前导列的基数非常低时，数据库可能会使用跳跃扫描（skip scan）来使用该索引。

如果每个索引的列排列方式不同，则同一张表可以有多个索引。如果指定不同的列排列顺序，可以使用相同的列创建多个索引。例如，以下SQL语句指定了有效的排列方式：

CREATE INDEX employee_idx1 ON employees (last_name, job_id);
CREATE INDEX employee_idx2 ON employees (job_id, last_name);

3.1.1.3. 唯一和非唯一索引

索引可以是唯一索引或非唯一索引。唯一索引保证表中的任意两行在键列或键列组合中没有重复的值。例如，没有两个员工可以有相同的员工ID。因此，在唯一索引中，每个数据值只有一个行号（rowid）。叶块中的数据仅按键排序。

非唯一索引允许索引列或列组合中存在重复值。例如，employees 表中的 first_name 列可能包含多个 "Mike" 值。对于非唯一索引，行号（rowid）被包含在键中并按排序顺序排列，因此非唯一索引按索引键和行号（升序）排序。

Oracle数据库不会对所有键列均为NULL的表行进行索引，除非是位图索引或当簇键列值为NULL时。

3.1.1.4. 索引类型

Oracle 数据库提供了几种索引方案，提供了互补的性能功能。索引可以分类如下：

• B-树索引
  这些索引是标准的索引类型。它们非常适合主键和高度选择性的索引。作为串联索引使用时，B-树索引可以按索引列排序检索数据。B-树索引有以下子类型：

  • 索引组织表（Index-organized tables）
    索引组织表与堆组织表不同，因为数据本身就是索引。
  • 反向键索引（Reverse key indexes）
    在这种类型的索引中，索引键的字节被反转，例如，103 存储为 301。字节的反转将插入分散到多个块中。
  • 降序索引（Descending indexes）
    这种类型的索引按降序存储特定列或列的值。
  • B-树簇索引（B-tree cluster indexes）
    这种类型的索引用于索引表簇键。键指向包含与簇键相关的行的块，而不是行。

• 位图和位图连接索引
  在位图索引中，索引条目使用位图指向多个行。相反，B树索引条目指向单个行。位图连接索引是用于两个或多个表连接的位图索引。

• 函数索引
  这种类型的索引包括通过函数（如UPPER函数）转换的列，或包含在表达式中的列。B树索引或位图索引都可以是函数索引。

• 应用域索引
  这种类型的索引由用户为特定应用领域的数据创建。物理索引不需要使用传统的索引结构，可以存储在Oracle数据库中作为表，也可以作为文件存储在外部。

3.1.2. B-Tree索引

B树索引（平衡树的简称）是最常见的数据库索引类型。B树索引是一个按值排序的列表，这些值被分为不同的范围。通过将一个键与一行或一行范围相关联，B树在广泛的查询中（包括精确匹配和范围搜索）提供了优异的检索性能。
图3-1展示了B树索引的结构。该示例显示了在department_id列上的索引，该列是employees表中的一个外键列。

3.1.2.1. 分支块和叶子块

B树索引有两种类型的块：用于搜索的分支块和存储值的叶子块。B树索引的上层分支块包含指向下层索引块的索引数据。在图3-1中，根分支块有一个条目0-40，指向下一个分支级别中的最左侧块。这个分支块包含诸如0-10和11-19的条目。每个条目指向一个包含在该范围内的键值的叶子块。

B树索引是平衡的，因为所有叶子块自动保持在相同的深度。因此，从索引的任何位置检索任何记录所需的时间大致相同。索引的高度是从根块到叶子块所需的块数。分支级别是高度减去1。在图3-1中，索引的高度为3，分支级别为2。

分支块存储在两个键之间做出分支决策所需的最小键前缀。这种技术使数据库能够在每个分支块上容纳尽可能多的数据。分支块包含指向包含该键的子块的指针。键和指针的数量受到块大小的限制。

叶子块包含每个索引数据值和用于定位实际行的相应rowid。每个条目按（键，rowid）排序。在叶子块内，键和rowid与其左右的兄弟条目相链接。叶子块本身也双向链接。在图3-1中，最左侧的叶子块（0-10）链接到第二个叶子块（11-19）。

注意：带有字符数据的列中的索引基于数据库字符集中的字符的二进制值。

3.1.2.2. 索引扫描

在索引扫描中，数据库通过遍历索引并使用语句中指定的索引列值来检索一行。如果数据库扫描索引以查找一个值，那么它将在n次I/O操作中找到这个值，其中n是B树索引的高度。这是Oracle数据库索引背后的基本原理。

如果SQL语句仅访问索引列，那么数据库直接从索引中读取值，而不是从表中读取值。如果语句访问索引列之外的列，那么数据库使用rowid在表中找到这些行。通常，数据库通过交替读取索引块和表块来检索表数据。

1. 全索引扫描

在全索引扫描中，数据库按顺序读取整个索引。如果SQL语句中的谓词（WHERE子句）引用了索引中的列，并且在某些情况下未指定谓词，全索引扫描是可行的。全索引扫描可以消除排序，因为数据按索引键排序。

假设一个应用程序运行以下查询：

SELECT department_id, last_name, salary FROM employees WHERE salary > 5000 ORDER BY department_id, last_name;

还假设department_id、last_name和salary是索引中的复合键。Oracle数据库执行索引的全扫描，按排序顺序读取（按部门ID和姓氏排序），并在salary属性上进行筛选。这样，数据库扫描的数据集比employees表更小，因为employees表包含的列比查询中包含的列更多，并且避免了对数据进行排序。

例如，全扫描可以按以下方式读取索引条目：

50,Atkinson,2800,rowid 
60,Austin,4800,rowid 
70,Baer,10000,rowid
80,Abel,11000,rowid 
80,Ande,6400,rowid 
110,Austin,7200,rowid 
. . .

2. 快速全索引扫描

快速全索引扫描是指数据库在不访问表的情况下直接访问索引中的数据，并且数据库读取索引块时没有特定的顺序。
快速全索引扫描在满足以下两个条件时是全表扫描的替代方法：

■ 索引必须包含查询所需的所有列。
■ 查询结果集中不得出现包含全部空值的行。为了保证这一结果，索引中的至少一列必须满足以下条件之一：
– 具有 NOT NULL 约束
– 应用了防止空值在查询结果集中被考虑的谓词

例如，一个应用程序发出了以下查询，该查询不包含 ORDER BY 子句：

SELECT last_name, salary FROM employees;

last_name 列具有 not null 约束。如果 last_name 和 salary 作为索引中的复合键，那么快速全索引扫描可以读取索引条目以获取所需信息：

Baida,2900,rowid 
Zlotkey,10500,rowid 
Austin,7200,rowid 
Baer,10000,rowid 
Atkinson,2800,rowid 
Austin,4800,rowid 
. . .