Oracle 索引基础

Oracle 索引基础

如果一个表中包含有很多条记录，当对表执行查询时，必须将所有的记录一一取出，以便将每一条记录与查询条件进行比较，然后再返回满足条件的记录。这样进行操作的时间开销和I/O开销都是十分巨大的。这时就可以考虑通过建立索引来减少开销。

简单地说，如果将表看作一本书，索引的作用则类似于书中的目录。在没有目录的情况下，要在书中查找指定的内容必须阅读全书，而有了目录之后，只需要通过目录就可以快速找到包含所需内容的页。类似地，如果要在表中查询指定的记录，在没有索引的情况下，必须遍历整个表，而有了索引之后，只需要在索引中找到符合查询条件的索引字段值，就可以通过保存在索引中的ROWID（相当于页码）快速找到表中对应的记录。

因此，合理地使用索引可以大大降低磁盘的I/O次数，从而提高数据访问性能。假设EMP表中的数据占用了10000了数据块，如果EMPNO列上不存在索引，那么当执行查询操作时需要执行全表扫描，这种操作的I/O次数为10000次；如果EMPNO列上存在索引（假设索引层次为2），那么执行查询时将使用索引进行扫描（I/O次数为4次）。

索引与表一样，不仅需要在数据字典中保存索引的定义，还需要在表空间中为它分配实际的存储空间。当创建索引时，Oracle会自动在用户的默认表空间中或指定的表空间中创建一个索引段，为索引数据提供存储空间。与创建表的情况类以，在创建索引时也可以为它设置存储参数。

为某个表创建的索引不必和该表保存在同一个表空间中。如果将索引和对应的表分别存放在位于不同硬盘上的不同的表空间中，反而能够提高查询的速度。因为Oracle能够并行读取不同硬盘中的数据，这样的查询可以避免产生I/O冲突。

在创建索引时，Oracle首先对将要建立索引的字段进行排序，然后将排序后的字段值和对应记录的ROWID存储在索引段中。例如，假设使用下面的语句为EMP表中的ENAME 字段创建索引：

SQL> create index emp_ename on emp (ename);

索引已创建。

Oracle首先在EMP表中按照ENAME字段进行排序，默认为升序排序，然后按照排序后的顺序将ENAME字段值和对应的ROWID逐个保存在索引中。建立索引的字段被称为索此字段，例如ENAME字段即为索引字段。

在索引创建之后，如果执行一条在WHERE子句中引用了ENAME字段的查询。例如：SQL> select ename,hiredate,sal

2 from emp

3 where ename='SMITH';

ENAME HIREDA TE SAL

---------- -------------- ---------

SMITH 17-12月-80 960

Oracle将首先对索引中的ENAME字段进行一次快速搜索（因为索引中的ENAME字段已经排序，所以该搜索是非常快的），找到符合条件的ENAME字段值所对应的ROWID，然后再利用ROWID到EMP表中提取相应的记录。这个操作比逐条读取EMP表中未排序的记录要快得多。可以看出，在索引中只保存索引字段的值与相应的ROWID，这种组合称为

“索引条目”。

初学者常常会混淆索引与键，特别是对于惟一索引与主键。其实索引与键两个术语在许多地方都可以互换，尤其是Oracle 有时也会利用索引，特别是惟一索引实现的一些完整性约束。但是它们在本质上有着相当明显的区别。索引是存储在数据库中的一种实体结构，可以通过SQL 语句创建、修改和删除。而键只是一个逻辑上的概念，在Oracle 中，键是与完整性约束相对应的。

在Oracle 中可以创建多种类型的索引，以适应各种表的特点。按照索引数据的存储方式可以将索引分为：B 树索引、反向键索引、位图索引和基于函数的索引等。按照索引列的个数，索引又可以分为单列索引和复合索引。按照索引列的惟一性，索引又可以分为惟一索引和非惟一索引。

单列索引是基于单个列所建立的索引，而复合索引是基于两个列或多个列所建立的索引。需要注意，B 树索引列的个数不能超过32列，位图索引的个数不能超过30例。可以在同一个表上建立多个索引，但要求列的组合必须不同，使用以下语句建立的两个索引是合法的：

SQL> create index emp_name_job_index on emp(ename,job);

索引已创建。

SQL> create index emp_job_name_index on emp(job,ename);

索引已创建。

如上所示，尽管索引EMP_NAME_JOB_INDEX 和EMP_JOB_NAME_INDEX 用到了相同的列（ENAME 和JOB 列），但因为顺序不同，所以是合法的。如果顺序完全相同，则该复合索引是不合法的。

惟一索引是索引列的值不能有重复的索引；非惟一索引是索引列值允许重复值的索引。无论是惟一索引还是非惟一索此，索引列都允许NULL 值。需要注意，当定义主键约束时或惟一约束时，Oracle 会自动在相应的约束列上建立惟一索引。

建立和规划索引时，必须选择合适的表和列。如果选择的表和列不合适，不仅无法提高查询速度，反而会极大地降低DML 操作的速度。建立索引的策略如下：

● 索引应该建立在WHERE 子句经常引用的表列上。如果在大表上频繁使用某列或

某几列作为条件执行检索操作，并且检索行数低于总行数的15%，那么应该考虑在这些列上建立索引。

● 为了提高多表连接的性能，应该在连接列上建立索引。

● 不要在小表上建立索引。

● 如果经常需要基于某列或某几列执行排序操作，那么通过在这些列上建立索引，可

以加快数据排序的速度。

● 限制表的索引个数。索引主要用于加速查询速度，但会降低DML 操作的速度。索

引越多，DML 操作的速度越慢，尤其会极大地影响INSERT 操作和DELETE 操作

注 意 具有索引的表与不具有索引的表，在编写SQL 查询语句时没有任何区别。索引

只是提供一种快速访问指定记录的方法。可以说，表是否具有索引，表的查询

速度影响最大，而对其他方法的影响则非常微小。索引一旦创建，将由Oracle

自动管理和维护。例如，在向表中插入、更新或删除一条记录时，Oracle 会自

动在索引中作出相应的修改。