关于ORACLE数据库索引

许娟萍 1999.03.22

一、索引

索引不是运行Oracle所必需的，但它能够加快查询速度。当执行同样的查询时，若有利用索引，Oracle首先在索引中查找，由于索引是按大小排列的，因此能很快找到查询结果。

一个表的单个索引最多可以包含16个列。索引太多的代价是降低插入新行的速度：每做一次insert操作，每个索引也都要增加一个新项目。如果表主要只用于查询，那么索引多列的唯一消耗就是磁盘空间。但这时索引较之空间的浪费更有价值。

二、在数据库中放置索引

通过将索引赋予一个指定的表空间，用户可以指定一个表的索引放置的位置。一个表空间就是磁盘上用于存储表和索引的一段空间，一个数据库可以有多个表空间，每个表空间都有各自的名称。一个表的索引应该放置在与数据表空间上下不同的物理磁盘上，这样可以减少表空间文件之间的磁盘争用。 三、使用索引操作

在Oracle中，有两大类索引：唯一索引对于被索引列，索引表中每一行 包含一个唯一值，和非唯一索引行中的索引值可重复。从索引中读取数据的操作取决于使用的索引类型和查询中访问索引的方法。

请浏览一下表charge: Create Table charge

( recnum number(9) not null, boxnum char(12), callernum char(16), callednum char(16), begintime char(14), ywtime number(5), funcnum number(2) );

缺省情况下，表charge上没有索引生成。若我们假定列recnum是表charge中的主关键字—也就是说，它唯一的区别了每一行，这时就可以在表charge上定义主关键字约束以保证列recnum的唯一性。

无论何时一个主关键字或唯一约束被生成，Oracle将生成一个唯一索引以保证列值的唯一性。以下所示的目录Alter Table包含了Using Index子句来强迫索引被定位于表空间INDEXES中： Alter table charge add constraint recnum_pk primary key (recnum) using index tablespace INDEXES;

1

用户也可以手工地在表charge中的其他列上记录索引。例如，如果列begintime的值为非唯一的话，用户可以通过命令Create Index在列上建立一个非唯一索引。

Create Index charge$begintime On charge(begintime) Tablespace INDEXES;

表charge上现有两个索引：列recnum上的唯一索引和列begintime上的非唯一索引。在处理查询的过程中，可以使用一个或多个索引，这取决于查询是如何编写和执行的。

四、何时使用索引

1. 当设定一个索引列为一特定值时在表charge中，列begintime具有一个非唯一索引charge$begintime。一个设定列begintime值为一定值的查询将能够使用索引charge$begintime。

例： select * from charge where begintime=’19990322103000’;

2. 当对索引列指定一特定区间值时索引的使用并不需要指定明确的值。索引区间扫描操作可以扫描索引中的一段值。在下面的查询中，表charge中的列begintime被用于执行一段区间值的查询(查询1999/3/22日的计费话单)： 例： select * from charge where begintime like ‘19990322%’;

当对一列指定了一段值区间后，如果指定的第一个字符是一个通配符的话，索引在查询中将不被使用。 例： select * from charge where begintime like ‘%0322103000’;

由于用作值比较的字符串的首字母为通配符，索引不能被用于快速地寻找 相关数据，所以一个全表扫描将被执行。

3. 当在where子句中无任何函数在列上执行时 例： select * from charge where substr(begintime,1,6)=’19990322’;

上例中的查询将对表charge执行全表扫描操作，因为有函数substr在列begintime上执行。

4. 当NO IS NULL或IS NOT NULL选项用于索引列时NULL值(空值)并未在索引中存贮。所以，下列查询将并不使用索引，索引对查询的处理也没有如何帮助。 例： select * from charge where begintime is null;

由于begintime是查询中的唯一的有限定条件的列，且此限定条件为

2

NULL(空)检查，所以索引charge$begintime将不被使用，而执行全表访问操作来处理查询。

当IS NOT NULL选项作用在一个列时，列中所有的非空值被存贮在索引中，但是索引搜索并不是有效率的。为了处理查询，优化器需要从索引中读取每个列值并通过访问表来获得索引返回的每一行内容。在多数情况下，对于从索引中返回的所有值，执行全表扫描操作比执行索引扫描效率更高。 所以，下面的查询不应使用索引。 例： select * from charge where begintime is not null;

5. 当等值条件被使用时 例： select * from charge where begintime!=’19980322103000’;

当处理以上的查询时，优化器将不能够利用索引。索引被用于当值被设 定为另一个值的情况—即当限定条件为等式时，而非不等式。

另一个不等式的例子not in子句，当用于字查询时： 例： select * from charge where recnum not in (select recnum from charge where begintime=’19990322103000’);

上例中的查询将不能够使用表charge中的列recnum上的索引，因为它并未设定为任何定值。

下例查询中使用了in子句，因而可以使用在列recnum上的索引。 例： select * from charge where recnum in (select recnum from charge where begintime=’19990322103000’);

6. 当多列索引中的首列被设为定值时

索引可在单列或多列上建立。如果索引是生成在多列上，则只有在查询中索引首列被用于限制条件时，索引才能够被使用。如果查询中仅对索引的非首列指定值限定条件，索引将不被用于查询中。

7. 当使用MAX或MIN函数时

当在索引列上选择MAX或MIN值时，优化器可以使用索引来快速地检索列中的最大值或最小值。

例： select MIN(begintime) from charge;

8. 当索引是选择性时

当使用成本优化器CBO选项时，优化器将对索引作出选择以确定索引的使用是否会降低查询执行的成本。

当索引是高度选择性时，仅有一小部分记录与每个不同的列值相关联。例如，如果表中有100个记录而对表中一个项有80个不同值，则此列上的

3

索引选择性为80/100=0.8。选择性越高，对于列中每个不同值，查询返回的行数据个数越少。

在区间扫描中，每个不同值返回的行数是非常重要的。如果一个索引的 选择性较低，则用于检索数据的多次索引区间扫描和ROWID索引访问操作可能较一次表的全表访问操作需要更多工作。

4