ORACLE用索引提高效率

索引是表的一个概念部分,用来提高检索数据的效率. 实际上,oracle使用了一个复杂的自平衡b-tree结构. 通常,通过索引查询数据比全表扫描要快. 当oracle找出执行查询和update语句的最佳路径时, oracle优化器将使用索引. 同样在联结多个表时使用索引也可以提高效率. 另一个使用索引的好处是,它提供了主键(primary key)的唯一性验证.

除了那些long或long raw数据类型, 你可以索引几乎所有的列. 通常, 在大型表中使用索引特别有效. 当然,你也会发现, 在扫描小表时,使用索引同样能提高效率.

虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价. 索引需要空间来

存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时, 索引本身也会被修改. 这意味着每条记录的insert , delete , update将为此多付出4 , 5 次的磁盘i/o . 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.

译者按:

定期的重构索引是有必要的.

alter index <indexname> rebuild <tablespacename>

26. 索引的操作

oracle对索引有两种访问模式.

索引唯一扫描 ( index unique scan)

大多数情况下, 优化器通过where子句访问index.

例如:

表lodging有两个索引 : 建立在lodging列上的唯一性索引lodging_pk和建立在manager列上的非唯一性索引lodging$manager.

select *

from lodging

where lodging = ‘rose hill';

在内部 , 上述sql将被分成两步执行, 首先 , lodging_pk 索引将通过索引唯一扫描的方式被访问 , 获得相对应的rowid, 通过rowid访问表的方式 执行下一步检索.

如果被检索返回的列包括在index列中,oracle将不执行第二步的处理(通过rowid访问表). 因为检索数据保存在索引中, 单单访问索引就可以完全满足查询结果.

下面sql只需要index unique scan 操作.

select lodging

索引范围查询(index range scan)

适用于两种情况:

1. 基于一个范围的检索

2. 基于非唯一性索引的检索

例1:

where lodging like ‘m%';

where子句条件包括一系列值, oracle将通过索引范围查询的方式查询lodging_pk . 由于索引范围查询将返回一组值, 它的效率就要比索引唯一扫描

低一些.

例2:

where manager = ‘bill gates';

这个sql的执行分两步, lodging$manager的索引范围查询(得到所有符合条件记录的rowid) 和下一步同过rowid访问表得到lodging列的值. 由于lodging$manager是一个非唯一性的索引,数据库不能对它执行索引唯一扫描.

由于sql返回lodging列,而它并不存在于lodging$manager索引中, 所以在索引范围查询后会执行一个通过rowid访问表的操作.

where子句中, 如果索引列所对应的值的第一个字符由通配符(wildcard)开始, 索引将不被采用.

where manager like ‘％hanman';

在这种情况下，oracle将使用全表扫描.