ORACLE索引使用规则

逻辑上：

Single column 单行索引

Concatenated 多行索引

Unique 唯一索引

NonUnique 非唯一索引

Function-based函数索引

Domain 域索引

物理上：

Partitioned 分区索引

NonPartitioned 非分区索引

B-tree：

Normal 正常型B树

Rever Key 反转型B树

Bitmap 位图索引

索引结构：

B-tree：

适合与大量的增、删、改（OLTP）；

不能用包含OR操作符的查询；

适合高基数的列（唯一值多）

典型的树状结构；

每个结点都是数据块；

大多都是物理上一层、两层或三层不定，逻辑上三层；

叶子块数据是排序的，从左向右递增；

在分支块和根块中放的是索引的范围；

Bitmap:

适合与决策支持系统；

做UPDATE代价非常高；

非常适合OR操作符的查询；

基数比较少的时候才能建位图索引；

树型结构：

索引头

开始ROWID，结束ROWID（先列出索引的最大范围）

BITMAP

每一个BIT对应着一个ROWID，它的值是1还是0，如果是1，表示着BIT对应的ROWID有值；

B*tree索引的话通常在访问小数据量的情况下比较适用，比如你访问不超过表中数据的5%，当然这只是个相对的比率，适用于一般的情况。bitmap的话在数据仓库中使用较多，用于低基数列，比如性别之类重复值很多的字段，基数越小越好。

B* 树索引

这些是我所说的 “ 传统 “ 索引。到目前为止，这是 Oracle 和大多数其他数据库中最常用的索引。 B* 树的构造类似于二叉树，能根据键提供一行或一个行集的快速访问，通常只需很少的读操作就能找到正确的行。不过，需要注意重要的一点， ” B* 树 “ 中的 ” B “ 不代表二叉（ binary ），而代表平衡（ b alanced ）。B* 树索引并不是一颗二叉树，这一点在介绍如何在磁盘上物理地存储 B* 树时就会了解到。 B* 树索引有以下子类型：

索引组织表（ index organized table ）：索引组织表以 B* 树结构存储。堆表的数据行是以一种无组织的方式存储的（只要有可用的空间，就可以放数据），而 IOT 与之不同， IOT 中的数据要按主键的顺序存储和排序。对应用来说， IOT 表现得与 “ 常规 “ 表并无二致；需要使用 SQL 来正确地访问 IOT 。 IOT 对信息获取、空间系统和 OLAP 应用最为有用。 IOT 在上一章已经详细地讨论过。

B*树聚簇索引（ B*tree cluster index ）这些是传统 B* 树索引的一个变体（只是稍有变化）。 B* 树聚簇索引用于对聚簇键建立索引（见第 11. 章中 “ 索引聚簇表 “ 一节），所以这一章不再讨论。在传统 B* 树中 ，键都指向一行；而 B* 树聚簇不同，一个聚簇键会指向一个块，其中包含与这个聚簇键相关的多行。

降序索引（ descending index ）：降序索引允许数据在索引结构中按 “ 从大到小 “ 的顺序（降序）排序，而不是按 ” 从小到大 “ 的顺序（升序）排序。我们会解释为什么降序索引很重要，并说明降序索引如何工作。

反向键索引（ reverse key index ）：这也是 B* 树索引，只不过键中的字节会 “ 反转 “ 。利用反向键索引，如果索引中填充的是递增的值，索引条目在索引中可以得到更均匀的分布。例如，如果使用一个序列来生成主键，这个序列将生成诸如 987500 、 987501 、 987502 等值。这些值是顺序的，所以倘若使用一 个传统的 B* 树索引，这些值就可能放在同一个右侧块上，这就加剧了对这一块的竞争。利用反向键， Oracl e则会逻辑地对 205789 、 105789 、 005789 等建立索引。 Oracle 将数据放在索引中之前，将先 把所存储数据的字节反转，这样原来可能在索引中相邻放置的值在字节反转之后就会相距很远。通过反转字节，对索引的插入就会分布到多个块上。

位图索引（ bitmap index ）

在一颗 B* 树中，通常索引条目和行之间存在一种一对一的关系：一个 索引条目就指向一行。而对于位图索引，一个索引条目则使用一个位图同时指向多行。位图索引适用于高度重复而且通常只读的数据（高度重复是指相对于表中的总行数，数据只有很少的几个不同值）。考虑在一 个有 100 万行的表中，每个列只有 3 个可取值： Y 、 N 和 NULL 。举例来说，如果你需要频繁地统计多少行有值Y ，这就很适合建立位图索引。不过并不是说如果这个表中某一列有 11.000 个不同的值就不能建立位图索引，这一列当然也可以建立 位图索引。在一个 OLTP 数据库中，由于存在并发性相关的问题，所以不能考虑使用位图索引（后面我们就会讨论这一点）。注意，位图索引要求使用 Oracle 企业版或个人版。

位图联结索引（ bitmap join index ）：这为索引结构（而不是表）中的数据提供了一种逆规范化的 方法。例如，请考虑简单的 EMP 和 DEPT 表。有人可能会问这样一个问题： “ 多少人在位于波士顿的部门工作 ？“ EMP 有一个指向 DEPT 的外键，要想统计 LOC 值为 Boston 的部门中的员工人数，通常必须完成表联结，将 LOC 列联结至 EMP 记录来回答这个问题。通过使用位图联结索引，则可以在 EMP 表上对 LOC 列建立索引 。

基于函数的索引（ function-based index ）

这些就是 B* 树索引或位图索引，它将一个函数计算得到的结果存储在行的列中，而不是存储列数据本身。可以把基于函数的索引看作一个虚拟列（或派生列）上的索引，换句话说，这个列并不物理存储在表中。基于函数的索引可以用于加快形如 SELECT * FROM T W HERE FUNCTION(DATABASE_COLUMN) = SAME_VALUE 这样的查询，因为值 FUNCTION(DATABASE_COLUMN) 已经提前计算并存储在索引中。

应用域索引（ application domain index ）

应用域索引是你自己构建和存储的索引，可能存储在Oracle 中，也可能在 Oracle 之外。你要告诉优化器索引的选择性如何，以及执行的开销有多大，优化器则会根据你提供的信息来决定是否使用你的索引。 Oracle 文本索引就是应用域索引的一个例子；你也可 以使用构建 Oracle 文本索引所用的工具来建立自己的索引。需要指出，这里创建的 “ 索引 “ 不需要使用传统的索引结构。例如， Oracle 文本索引就使用了一组表来实现其索引概念。

首先，我们要确定数据库运行在何种优化模式下，相应的参数是：optimizer_mode。可在svrmgrl中运行“show parameter optimizer_mode"来查看。ORACLE V7以来缺省的设置应是"choose"，即如果对已分析的表查询的话选择CBO，否则选择RBO。如果该参数设为“rule”，则不论表是否分析过，一概选用RBO，除非在语句中用hint强制。

查找原因的步骤

首先，我们要确定数据库运行在何种优化模式下，相应的参数是：optimizer_mode。可在svrmgrl中运行“show parameter optimizer_mode"来查看。ORACLE V7以来缺省的设置应是"choose"，即如果对已分析的表查询的话选择CBO，否则选择RBO。如果该参数设为“rule”，则不论表是否分析过，一概选用RBO，除非在语句中用hint强制。

其次，检查被索引的列或组合索引的首列是否出现在PL/SQL语句的WHERE子句中，这是“执行计划”能用到相关索引的必要条件。

第三，看采用了哪种类型的连接方式。ORACLE的共有Sort Merge Join（SMJ）、Hash Join（HJ）和Nested Loop Join（NL）。在两张表连接，且内表的目标列上建有索引时，只有Nested Loop才能有效地利用到该索引。SMJ即使相关列上建有索引，最多只能因索引的存在，避免数据排序过程。HJ由于须做HASH运算，索引的存在对数据查询速度几乎没有影响。

第四，看连接顺序是否允许使用相关索引。假设表emp的deptno列上有索引，表dept的列deptno上无索引，WHERE语句有emp.deptno=dept.deptno条件。在做NL连接时，emp做为外表，先被访问，由于连接机制原因，外表的数据访问方式是全表扫描，emp.deptno上的索引显然是用不上，最多在其上做索引全扫描或索引快速全扫描。

第五，是否用到系统数据字典表或视图。由于系统数据字典表都未被分析过，可能导致极差的“执行计划”。但是不要擅自对数据字典表做分析，否则可能导致死锁，或系统性能下降。

第六，索引列是否函数的参数。如是，索引在查询时用不上。

第七，是否存在潜在的数据类型转换。如将字符型数据与数值型数据比较，ORACLE会自动将字符型用to_number()函数进行转换，从而导致第六种现象的发生。

第八，是否为表和相关的索引搜集足够的统计数据。对数据经常有增、删、改的表最好定期对表和索引进行分析，可用SQL语句“analyze table xxxx compute statistics for all indexes;"。ORACLE掌握了充分反映实际的统计数据，才有可能做出正确的选择。

第九，索引列的选择性不高。

我们假设典型情况，有表emp，共有一百万行数据，但其中的emp.deptno列，数据只有4种不同的值，如10、20、30、40。虽然emp数据行有很多，ORACLE缺省认定表中列的值是在所有数据行均匀分布的，也就是说每种deptno值各有25万数据行与之对应。假设SQL搜索条件DEPTNO=10，利用deptno列上的索引进行数据搜索效率，往往不比全表扫描的高，ORACLE理所当然对索引“视而不见”，认为该索引的选择性不高。

但我们考虑另一种情况，如果一百万数据行实际不是在4种deptno值间平均分配，其中有99万行对应着值10，5000行对应值20，3000行对应值30，2000行对应值40。在这种数据分布图案中对除值为10外的其它deptno值搜索时，毫无疑问，如果索引能被应用，那么效率会高出很多。我们可以采用对该索引列进行单独分析，或用analyze语句对该列建立直方图，对该列搜集足够的统计数据，使ORACLE在搜索选择性较高的值能用上索引。

第十，索引列值是否可为空（NULL）。如果索引列值可以是空值，在SQL语句中那些需要返回NULL值的操作，将不会用到索引，如COUNT（*），而是用全表扫描。这是因为索引中存储值不能为全空。

第十一，看是否有用到并行查询（PQO）。并行查询将不会用到索引。

第十二，看PL/SQL语句中是否有用到bind变量。由于数据库不知道bind变量具体是什么值，在做非相等连接时，如“<”，“>”,“like”等。ORACLE将引用缺省值，在某些情况下会对执行计划造成影响。

关键词：ORACLE索引运用规则