Expert Indexing in Oracle Database 11g: Maximum Performance for Your Database

Determining Which Columns to Index

Now we turn our attention to figuring out which columns should be indexed. For starters, we recommend for most applications you create indexes in the following situations:

• Define a primary key constraint for each table: This results in an index automatically being created on the columns specified in the primary key.
• Create unique key constraints on columns that are required to be unique and are different from the primary key columns: Each unique key constraint results in an index automatically being created on the columns specified in the constraint.
• Manually create indexes on foreign key columns: This is done for better performance to avoid certain locking issues (see Chapter 2 for complete details).

Each ...