Oracle Indexes: Best Practices

A database you manage contains hundreds of tables. Each table typically contains a dozen or more columns. Indexes are crucial from performance point-of-view. A question is often raised that which columns should be indexed? Also, are there any guidelines and best practices while implementing the indexes?

In this blog, I have tried to address the questions related to index related guidelines and best practices.

Some general considerations for deciding which columns to index are listed below:

• Define a primary key constraint for each table that results in an index automatically being created on the columns specified in the primary key.
• Create unique key constraints on non-null column values that are required to be unique (different from the primary key columns). This results in an index automatically being created on the columns specified in unique key constraints.
• Explicitly create indexes on foreign key columns.
• Create indexes on columns used often as predicates in the WHERE clause of frequently executed SQL queries.

After it is decided to create indexes, recommendation is to adhere to under mentioned index creation standards that facilitate the ease of maintenance.

• Use the default B-tree index unless you have a solid reason to use a different index type.
• Create a separate tablespace for the indexes. This allows you to more easily manage indexes separately from tables for tasks such as backup and recovery.
• Let the index inherit its storage properties from the tablespace. This allows you to specify the storage properties when you create the tablespace and not have to manage storage properties for individual indexes.
• If you have a variety of storage requirements for indexes, then consider creating separate tablespaces for each type of index—for example, INDEX_LARGE, INDEX_MEDIUM, and INDEX_SMALL tablespaces, each defined with storage characteristics appropriate for the size of the index.

Index Creation and Maintenance Guidelines

Guideline 1        : Add indexes judiciously. Test first to determine quantifiable performance gains.

Reasoning         : Indexes consume disk space and processing resources. Don’t add indexes unnecessarily.

Guideline 2        : Use the correct type of index.

Reasoning         : Correct index usage maximizes performance.

Guideline 3        : Use consistent naming standards.

Reasoning         : This makes maintenance and troubleshooting easier.

Guideline 4        : Monitor your indexes and drop indexes that aren’t used.

Reasoning         : Doing this frees up physical space and improves the performance of Data Manipulation Language (DML) statements.

Guideline 5        : Don’t rebuild indexes unless you have a solid reason to do so.

Reasoning         : Rebuilding an index is generally unnecessary unless the index is corrupt or you want to change a physical characteristic (such as the tablespace) without dropping the index.

Guideline 6        : Before dropping an index, consider marking it as unusable or invisible.

Reasoning         : This allows you to better determine if there are any performance issues before you drop the index. These options let you rebuild or re-enable the index without requiring the Data Definition Language (DDL) index creation statement.

Guideline 7        : Consider creating concatenated indexes that result in only the index structure being required to return the result set.

Reasoning         : Avoids having to scan any table blocks; when queries are able to use the index only, these results in very efficient execution plans.

Guideline 8        : Consider creating indexes on columns used in the ORDER BY, GROUP BY, UNION, or DISTINCT clauses.

Reasoning         : This may result in more efficient queries that frequently use these SQL constructs.

Refer to these guidelines as you create and manage indexes in your databases. These recommendations are intended to help you correctly use index technology.