Reactively Creating Indexes- Tables and Constraints

Rarely do DBAs and developers accurately create the right mix of indexes when first deploying an application. And that is not a bad thing or unexpected; it is hard to predict everything that occurs in a large database system, including data growth and other data uses. Furthermore, as the application matures, changes are introduced to the database (new tables, new columns, new constraints, and database upgrades that add new features, behaviors, and so on). The reality is that you will have to react to unforeseen situations in your database that warrant adding indexes to improve performance.

Index strategies also must be revisited for major database releases or system resource changes. For example, more memory on the server will allow for a table scan to perform better and maybe eliminate a need for an index. Or, an index that was beneficial because of how the optimizer was calculating cost or upgrades might validate a different query plan for better performance.

Index strategies are not just about creating indexes but also about cleaning up indexes that are no longer in use because of better statistics and optimizer query plans without the index.

Here is a typical process for reactively identifying poorly performing SQL statements and improving performance with indexes:

1. A poorly performing SQL statement is identified; a user complains about a specific statement, the DBA runs automatic database diagnostic monitor (ADDM), or automatic workload repository (AWR) reports to identify resource-consuming SQL, and so on.

2. The DBA checks the table and index statistics to ensure that out-of-date statistics are not causing the optimizer to make bad choices.

3. The DBA/developer determines that the query cannot be rewritten in a way that alleviates performance issues.

4. The DBA/developer examines the SQL statement and determines which tables and columns are being accessed, by inspecting the SELECT, FROM, and WHERE clauses.

5. The DBA/developer performs testing and recommends that an index be created, based on a table and one or more columns.

Once you have identified a poorly performing SQL query, consider creating indexes for the following situations:

•     Create indexes on columns used often as predicates in the WHERE clause; when multiple columns from a table are used in the WHERE clause, consider using a concatenated (multicolumn) index.

•   Create a covering index (i.e., an index on all columns) in the SELECT clause.

•     Create indexes on columns used in the ORDER BY and GROUP BY clauses.

•     Create function-based indexes on the function in the WHERE clauses.

Oracle allows you to create an index that contains more than one column. Multicolumn indexes are known as concatenated indexes (also called composite indexes). These indexes are especially effective when you often use multiple columns in the WHERE clause when accessing a table. Concatenated indexes are, in many instances, more efficient in this situation than creating separate, single-column indexes.

Columns included in the SELECT and WHERE clauses are also potential candidates for indexes. Sometimes, a covering index in a SELECT clause results in Oracle using the index structure itself (and not the table) to satisfy the results of the query. Also, if the column values are selective enough, Oracle can use an index on columns referenced in the WHERE clause to improve query performance.

Also consider creating indexes on columns used in the ORDER BY, GROUP BY, UNION, and DISTINCT clauses. This may result in greater efficiency for queries that frequently use these SQL constructs.

It is OK to have multiple indexes per table. However, the more indexes you place on a table, the slower the DML statements. Do not fall into the trap of randomly adding indexes to a table until you stumble upon the right combination of indexedcolumns. Rather, verify the performance of an index before you create it in a production environment. Oracle Database has improved how it reviews index usage, and these statistics can be taken into consideration when deciding to keep indexes or if a different index is needed. SQL plans and other reporting for the index usage will help determine which indexes are needed.

Also keep in mind that it is possible to add an index that increases the performance of one statement while hurting the performance of others. You must be sure that the statements that are improved warrant the penalty being applied to other statements. You should only an index add when you are certain it will improve performance.