Databases Reference
In-Depth Information
•
In some scenarios, the query optimizer will choose not to use an index. In other
words, the query optimizer calculates that the cost of a full table scan is less than
the cost when using an index.
In some situations, Oracle can retrieve data for a query by only accessing the
index; the table doesn't have to be accessed.
An understanding of these index fundamentals provide a good foundation for the rest of the
concepts introduced in this chapter and book. We now turn our attention to determining which type of
index to use.
•
Determining Which Type of Index to Use
Oracle provides a wide range of index types and features. The correct use of indexes results in well
performing and scalable database application. Conversely, if you incorrectly or unwisely implement a
feature, there may be detrimental performance implications. Table 1-1 summarizes the various Oracle
index types available. At first glance, this is a long list and may be somewhat overwhelming to somebody
new to Oracle. Deciding which index type to use isn't as daunting as it might initially seem. For most
applications, you should simply use the default B-tree index type.
Note
Several of the index types listed in Table 1-1 are actually just variations on the basic, B-tree index. A
reverse-key index, for example, is merely a B-tree index optimized for evenly spreading I/O when the index value
is sequentially generated and inserted with similar values.
Table 1-1. Oracle Index Types and Feature Descriptions
Index Type
Usage
B-tree
Default, balanced tree index; good for high-cardinality (high degree of distinct
values) columns. Use a normal B-tree index unless you have a concrete reason
to use a different index type or feature.
Index organized table
Efficient when most of the column values are included in the primary key. You
access the index as if it were a table. The data is stored in a B-tree like
structure.
Unique
A form of B-tree index; used to enforce uniqueness in column values. Often
used with primary key and unique key constraints, but can be created
independently of constraints.
Reverse-key
A form of B-tree index; useful to balance I/O in an index that has many
sequential inserts.
