About Indexes

Indexes are optional structures associated with tables and clusters that allow SQL queries to execute more quickly against a table. Just as the index in this manual helps you locate information faster than if there were no index, an Oracle Database index provides a faster access path to table data. You can use indexes without rewriting any queries. Your results are the same, but you see them more quickly.

Oracle Database provides several indexing schemes that provide complementary performance functionality. These are:

  • B-tree indexes: the default and the most common

  • 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.

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.