- The Data Model
- Indexing and Clustering
- Application Design
- Conclusion
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Indexing and Clustering
An index is an object with its own unique storage that provides a fast access path into a table. A cluster is a means of organizing table data so as to optimize certain access paths.
Oracle provides a wide variety of indexing and clustering mechanisms, and each option has merit in specific circumstances, but the three most widely applicable options are the default B*-Tree index, the hash cluster, and the bitmap index.
In general, the B*-Tree index is the most flexible type and provides good performance across a wide range of application types. However, you might wish to consider alternatives in the following circumstances:
- Hash clusters can improve access for exact key lookups, though cannot enhance range queries and require careful sizing to prevent degradation as they grow. Hash clusters are also resistant to the latch contention that can be common for busy B*-Tree indexes.
- Bitmap indexes are useful to optimize queries in which multiple columns of low cardinality are queried in combination. Unlike B*-tree indexes, multiple bitmap indexes can be efficiently merged but can also increase lock contention.
Index Design
Index design often involves constructing the best set of concatenated indexes. A concatenated index is simply an index comprising more than one column. Concatenated indexes are more effective merging multiple single column indexes, and a concatenated index that contains all of the columns in the WHERE clause will typically be the best way to optimize that WHERE clause.
If a concatenated index could only be used when all of its keys appeared in the WHERE clause, then concatenated indexes would probably be of pretty limited use. Luckily, a concatenated index can be used effectively, providing any of the initial or leading columns are used.
The more columns in the concatenated index, the more effective it is likely to be. You can even add columns from the SELECT list so that the query can be resolved by the index alone. Figure 2 shows how the IO is reduced as columns are added to the concatenated index for a query like this:
SELECT cust_id FROM sh.customers c WHERE cust_first_name = 'Connor' AND cust_last_name = 'Bishop' AND cust_year_of_birth = 1976;
)
Figure 2 The effect of adding relevant columns to a concatenated index.