7.2 Types of Indexes

---

There are a number of general types of indexes.

• No Index.   A table without an index is referred to as heap structured. It is quite literally in a heap. If this method of table access is efficient, which it can be, then do not create indexes for that table. Sometimes even Referential Integrity indexes can be detrimental to performance.

• Static Data Index.   This is an index placed on a small static data table. Sometimes these tables do not require indexes for anything other than Referential Integrity because the Optimizer may always choose a full table scan for better performance. In some cases Referential Integrity indexes can be removed for better performance, especially where the static table contains no foreign key entries. Static implies no changes, ever! If data in a table never changes why include added processing checking Referential Integrity?

• Dynamic Data Index.   These indexes expect changes to data and are thus subject to overflow. They may require frequent rebuilding. Never ever use anything but BTree type indexing for dynamic data. Clustering, hashing, and bitmaps are only appropriate for read-only tables with the exception of index-organized tables in rare circumstances. Index-organized tables effectively sort index and data values for an entire table into a BTree and can perform well in OLTP type databases.

• Read-Only Reporting Index.   Read-only tables are much more flexible with respect to indexing. Oracle Database allows numerous types of indexes for read-only tables where each index type has a specific application.

• Unique and Non-unique Indexes.   A unique index is an index allowing only a single value in a table. Be careful creating unique indexes because every insertion or update to a uniquely indexed column or columns requires a scan of at least the entire index space.

• Single-Column and Composite Indexes.   Single-column indexes are more efficient than composite multiple-column indexes.

Indexes can be created on columns of most datatypes and any combination thereof. Just remember this one thing. By far the most efficient and easy to manage type of index is an index created on a unique integer identifier. In Oracle Database these indexes are best incremented using Oracle Database sequence generators.

---