Deciding When to Create an Index- Indexes

There are usually two different situations in which DBAs and developers decide to create indexes:

• Proactively, when first deploying an application; the DBAs and developer make an educated guess as to which tables and columns to index.
• Reactively, when application performance bogs down, and users complain of poor performance; then the DBAs and developers attempt to identify slow-executing SQL queries and how indexes
  might be a solution.

Proactively Creating Indexes

When creating a new database application, part of the process involves identifying primary keys, unique keys, and foreign keys. The columns associated with those keys are usually candidates for indexes.

Here are some guidelines:
• 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 that 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.

In other words, some of the decision process on what tables and columns to index is automatically done for you when determining the table constraints. When creating primary and unique key constraints, Oracle automatically creates indexes for you. There is some debate about whether to create indexes on foreign key columns. There might even be a debate on table constraints in general. Later in this chapter we have further details about these indexes.

Constraint indexes and primary or foreign keys can be automated to run with table creation or as part of the object builds in the database. Also, if foreign keys are newly created, an index can be generated as part of the code to make sure that the indexes are created as changes are made, and instead of manually checking all of the constraints, indexes, and keys, DDL can be generated based on new objects and added as part of proactively creating indexes.

In addition to creating indexes related to constraints, if you have enough knowledge of the SQL contained within the application, you can create indexes related to tables and columns referenced in SELECT, FROM, and WHERE clauses. In my experience, DBAs and developers are not adept at proactively identifying such indexes. Rather, these indexing requirements are usually identified reactively.