关于 Oracle 索引

http://docs.oracle.com/cd/B28359_01/server.111/b28318/schema.htm#CNCPT811

本文内容

  • 唯一和不唯一索引（Unique and Nonunique Indexes）
  • 可见和不可见索引（Visible and Invisible Indexes）
  • 组合索引（Composite Indexes / concatenated index）
  • 索引和键（Indexes and Keys）
  • 索引和 Nulls（Indexes and Nulls）
  • 基于函数的索引（Function-Based Indexes）
  • 索引如何存储（How Indexes Are Stored）
  • 索引唯一扫描（Index Unique Scan）
  • 索引范围扫描（Index Range Scan）
  • 键压缩（Key Compression）
  • 反向键索引（Reverse Key Indexes）
  • Bitmap 索引（单表）
  • Bitmap 连接索引（多个表）
  • 参考资料

本文主要是描述关于索引的一些问题和概念。就我个人而言，自学 Oracle 的过程，就是不断为自己澄清某个概念，无论是看英文，还是看中文资料，当发现一个自己不熟悉，或不知道如何翻译的词时，就会试图去了解……然后思考如下几个问题：

1，为什么会出现这个概念/术语

2，这个概念/术语解决什么问题

3，这个概念/术语是如何实现的

4，这个概念/术语适用于什么场景

5，相似的概念/术语间有什么区别

无论何种技术，它们的存在都是为了解决我们实际中遇到的问题。知道概念/术语，往往比知道一个 SQL 语句如何写更重要。

索引是与表和聚类相关的可选结构。你可以创建，也可以不创建。可以在一个表的一个或多个列上创建索引，以加速 SQL 语句在该表上的执行。索引比没有索引能更快地定位信息，Oracle 数据库对表数据提供更快的访问路径。正确使用索引是减少磁盘 IO 的重要手段。

可以为表创建很多索引，只要列的组合对每个索引都不一样。如果你指定列的不重复地不同组合，那么你可以利用一些相同列创建很多索引。例如，下面语句是有效的组合：

CREATE INDEX employees_idx1 ON employees (last_name, job_id); 
 
CREATE INDEX employees_idx2 ON employees (job_id, last_name);

Oracle 数据库提供很多索引方案，可以提供互补的性能：

  • B-tree 索引（B-tree indexes）
  • B-tree 聚簇索引（B-tree cluster indexes）
  • 哈希聚簇索引（Hash cluster indexes）
  • 反向键索引（Reverse key indexes）
  • Bitmap 索引
  • Bitmap 连接索引

Oracle 数据库也为应用程序或磁盘提供基于函数的索引（function-based indexes）和域索引（domain indexes）。

有没有索引不会要求改变 SQL 语句。索引仅仅是加速访问数据的路径，它只是会影响执行的速度。给定一个已索引的数据值，那么索引点直接定位包含该值的行。

索引逻辑上和物理上独立于相关表中的数据。你可以在任何时间创建或删除索引，而不会影响基表或其他索引。如果你删除一个索引，那么所有应用程序会继续运行。但是，访问之前已索引的数据会变慢。索引具有独立的结构，要求存储空间。

Oracle 数据库在索引被创建后自动维护和使用。Oracle 数据库自动地在所有相关索引对数据的变化做出反应，如添加新行、更新行，或删除行，而无需用户额外的操作。

已索引数据的检索性能几乎保持不变，即使插入一个新行。但是，在一个表中存在很多索引会降低更新、删除和插入的性能。因为 Oracle 数据库也必须更新与表关联的那些索引。

优化器可以使用现存的索引来生成另一个索引。这会更快地生成索引。

唯一和不唯一索引

索引可以唯一，也可以不唯一。唯一索引保证一个表中没有两行数据在一个或多个列中具有重复的值。不唯一索引对列值不会有这个约束。

Oracle 建议，利用 CREATE UNIQUE INDEX 显式创建唯一索引。通过主键或唯一约束创建唯一索引不能保证生成一个新的索引，并且也不能保证一个唯一的索引。

可见和不可见索引

索引可以可见，也可以不可见。不可见的索引由 DML 操作维护，并且不能被优化器使用。

让一个索引不可见是使它不再可用或删除的一个方法。

组合索引

组合索引（composite index，也称 concatenated index）是在一个表的多个列上创建索引。一个组合索引中的列可以按任何顺序出现，并且不需要在表中相邻。

组合索引可以加快 SELECT 语句 WHERE 子句引用所有或组合索引中主导部分列的数据检索。因此，定义中使用的列的顺序很重要。一般地，最经常访问或选择的列在最前面。

下图说明 VENDOR_PARTS 表在 VENDOR_ID 和 PART_NO 列上有个组合索引。

cncpt046

图 1 组合索引示例

一个常规的组合索引不能超过 32 个列。Bitmap 索引不要超过 30 个。一个键值不能超过大约数据块中可用空间的约一半（减去一些开销）。

索引和键

尽管这两个术语经常互换使用，但索引（indexes）和键（keys）不同。索引是真正存储在数据库中的结构，由用户使用 SQL 语句创建、修改和删除。索引提供对表数据更快的访问路径。严格来说，键是一个逻辑概念。键对应 Oracle 数据库的另一个称为完整性约束（integrity constraints）的功能，它执行业务规则。

Oracle 数据库使用索引来执行一些完整性约束，因此，术语键和索引通常互换。但不要混淆它们。

索引和 Nulls

索引中的 NULL 值被认为是不同的，除非一个索引的两个或更多行中的非 null（non-NULL ）值是相同的，这种情况下，认为这些行相同的。因此，唯一（UNIQUE）索引防止包含 NULL 的数据行被认为是相同的。如果数据行完全是 NULL，则不能应用。

Oracle 数据库不能为所有键列为 NULL 的数据行创建索引，除了 Bitmap 索引，或当聚簇（cluster）键列值为 NULL 时。

基于函数的索引

用于创建索引的函数可以是数学表达式，或包含一个 PL/SQL 函数，包函数，C 标注，或 SQL函数的表达式。表达式不能包含任何聚合函数，它必须是确定性的。如在一个包含对象类型的列上创建索引，函数可以是对象的方法，例如 map 方法。但是，不能在 LOB 列，REF，或内置表列上创建基于函数的索引，也不能为包含 LOB，REF，嵌入表的对象创建基于函数的索引。

索引如何存储

当创建索引时，Oracle 数据库自动分配索引段以便在表空间保存索引数据。你可以控制为索引段的空间分配，并按下面方式使用这个已保留的空间：

  • 为索引段设置存储参数，以控制索引段的扩展。
  • 为索引段设置 PCTFREE 参数，以控制构成索引段的数据块中的空闲空间。

索引段的表空间或是拥有者的默认表空间，或是 CREATE INDEX 语句指定的一个表空间。你不必把索引放在与其相关表相同的表空间。因此，你可以通过把索引与其表存储在不同磁盘的不同表空间来提高查询性能，因为，Oracle 数据库可以并行检索索引和表数据。

索引唯一扫描

索引唯一扫描是访问数据最有效的方式之一。这个访问方法用于从 B-tree 索引返回数据。当一个唯一（B-tree）索引的所有列都被指定等值条件时，优化器选择唯一扫描。

索引范围扫描

索引范围扫描是一个访问选择性数据的常见方法。它可以是有界的（在两侧）或无界（在一侧或两侧）。数据按升序返回数据。多个具有相同值的行排序（升序）按 ROWIDs。

键压缩

键压缩可以压缩一个索引或索引组织表中主键列的值的一部分，从而降低重复值的存储开销。

一般来说，一个索引中的键有两个片（piece），一个分组片和一个唯一片。若键没有被定义为具有一个唯一片，则Oracle数据库提供一个以rowid形式，追加到一个分组片中。键压缩是分开分组片，并将其存储的一个方法，因此可以被多个唯一片共享。

反向键索引

与一个标准索引相比，创建一个反向键索引，反向每个已索引列的字节（rowid除外），同时保持列的顺序。这样安排可以帮助Oracle RAC（Oracle Real Application Clusters）避免性能退化，而是索引的修改集中在一个小的叶子快集合上。通过反向索引的键，插入就变成分布式访问索引中所有的叶子键。

使用反向键消除了在索引上执行范围扫描。因为，词法相邻的键在反向键索引不会被存储彼此指向下一个，只能执行用 fetch-by-key  或 full-index(table) 扫描。

有时，使用反向键索引可以使 OLTP Oracle RAC 应用程序更快。例如，在一个 e-mai 应用程序保存 mail 信息的索引：一些用户保存旧信息，索引必须维护指向旧的，以及最近的邮件信息。

REVERSE 关键字为创建反向键索引提供一个简单机制。可以在创建索引语句中指定该关键字：

CREATE INDEX i ON t (a,b,c) REVERSE;

指定 NOREVERSE 关键字把一个反向键索引重建为非反向索引：

ALTER INDEX i REBUILD NOREVERSE;

不用 NOREVERSE 关键字来重建反向索引会产生一个反向索引重建。

Bitmap 索引（单表）

索引的目的是，提供在表中指向包含一个给定键值的数据行。常规索引的实现是，为每个键存储一个对应这些键值数据航的 rowid 列表。Oracle 数据库反复地用已存储的 rowid 存储每个键值。在 Bitmap 索引中，使用每个键值的位图，而不是 rowid 列表。

位图中的每个比特对应一个可能的 rowid。若比特被设置，则意味着相应 rowid 的数据行包含该键值。映射函数可以把比特位转换成一个实际的 rowid，因此，bitmap 索引提供与常规索引相同的功能，即使它内部使用不同的表示。若不同的键值（不重复值）数量较小，则 bitmap 索引非常节省空间。

Bitmap 索引能有效地合并 WHERE 子句中符合很多条件的索引。在访问表本身之前，满足一些，而不是全部条件的行可以被过滤掉。通常会显著提高响应时间。

Bitmap 连接索引（多个表）

除了在单个表上创建 bitmap 索引外，你也可以创建 bitmap 连接索引，它是连接两个或多个表的 Bitmap 索引。一个 bitmap 连接索引是，减少必须被事先通过执行约束连接，减少数据列，一个空间的有效途径。对于表中一列的每个值，一个 bitmap 连接索引存储一个或多个表中对应数据行的 rowid。在数据仓库环境中，连接条件是主键列或纬度表的列与外键列或事实表之间的等值内连接。

bitmap 连接索引在存储方面比物化的连接视图更有效。因为物化连接视图不会压缩事实表的 rowid。

参考资料

posted @ 2012-11-17 16:34  船长&CAP  阅读(894)  评论(1)  编辑  收藏  举报
免费流量统计软件