How to decide which column to index

The database you manage contains hundreds of tables. Each table typically contains a dozen or more columns. You wonder which columns should be indexed.

Below are the general guidelines for deciding which columns to index:

• 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 you have decided to create indexes, I would recommend that you adhere to index creation standards that facilitate the ease of maintenance. Specifically, follow these guidelines when creating an index:

• 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.

You should add an index only when you’re certain it will improve performance. Misusing indexes can have serious negative performance effects. Indexes created of the wrong type or on the wrong columns  do nothing but consume space and processing resources. As a DBA, you must have a strategy to ensure that indexes enhance performance and don’t negatively impact applications.

                         Guideline                                                                            Reason