概述

Indexes are optional structures associated with tables and clusters that allow SQL queries to execute more quickly against a table. 添加索引可以加快SQL在表上查询数据的速度。

Indexes are logically and physically independent of the data in the associated table. Being independent structures, they require storage space. You can create or drop an index without affecting the base tables, database applications, or other indexes. The database automatically maintains indexes when you insert, update, and delete rows of the associated table. If you drop an index, all applications continue to work. However, access to previously indexed data might be slower. 索引的增删不会影响其关联表、数据应用和其它索引等。删除索引带来的主要影响是访问数据的速度可能变慢。

以下是Oracle提供的几种索引方案及其应用场景：

B-tree indexes: ***最常用的且是默认的***

B-tree cluster indexes: defined specifically for cluster

Hash cluster indexes: defined specifically for a hash cluster

Global and local indexes: relate to partitioned tables and indexes

Reverse key indexes: most useful for Oracle Real Application Clusters applications

Bitmap indexes: compact; work best for columns with a small set of values

Function-based indexes: contain the precomputed value of a function/expression

Domain indexes: specific to an application or cartridge.