Creating appropriate indexes

Creating appropriate indexes allows the optimizer to choose an index scan for those cases where it would be more efficient than a table scan.

Some guidelines for creating indexes include:

• Define primary keys and unique indexes wherever they apply.

• Create an index on any column that the query uses to join tables (join predicates).

• Create an index on any column from which you search for particular values on a regular basis.

• Create an index on columns that are commonly used in ORDER BY clauses.

• Ensure that you have used predicates that retrieve only the data you need. For example, ensure that the selectivity value for the predicates represents the portion of the table that you want returned.

• When creating a multicolumn index, the first columns of the index should be the ones that are used most often by the predicates in your query.

• Ensure that the disk and update maintenance overhead an index introduces will not be too high. For more information, see the section on index management in the Administration Guide.