In Oracle, an INDEX is a database object intended to improve the performance of SELECT queries. Indexes are created on table columns, and the index stores all the values of the column under index segments. If the SELECT query uses an indexed column in any of the WHERE conditions, the query uses the index segment instead of performing a full table scan. This results in enhanced query performance. Much of the performance increase comes from reducing the overall amount of disk I/O needed to be done (by traversing the indexed path instead of a full table scan), resulting in less data being processed to complete the query.
Oracle provides several different types of indexes:
- B-tree indexes: these are the default, and by far the most common type of index.
- B-tree cluster indexes: specifically defined for cluster application use.
- Hash cluster indexes: intended for use with a hash cluster.
- Global and local indexes: these relate to partitioned tables and indexes.
- Reverse key indexes: most often used in Oracle RAC applications.
- Bitmap indexes: very compact indexes that generally work best for columns with a limited set of values (low cardinality)
- Function-based indexes: these contain the precomputed value of a specific function or expression
Indexes are a broad topic; understanding which, when and where to use them takes some research. For general information on indexes, please view the following pages:
- Oracle Indexes
- B*Tree Indexes
- Bitmap Indexes
- Descending Indexes
Oracle treats descending indexes as if they were function-based indexes.
- Reverse Key Indexes
- Function Based Indexes
To create a function-based index (FBI) in your own schema on your own table you must have the QUERY REWRITE system privilege.
- Invisible Indexes
- Virtual / No Segement Indexes
These are not officially supported by Oracle but are used extensively by the OEM Grid Control.
- Compressed Indexes
Related Code Snippets:
- Tab Indexes - Returns all indexes and the position of index.