Using the Index Advisor to Recommend Indexes

The right set of indexes can make a difference in query performance. Use the Index Advisor to recommend indexes for improving the performance of a specific SQL workload.

The Index Advisor is intended for read-intensive complex queries. The use of the Index Advisor is not recommended for a write-intensive workload.

The Index Advisor evaluates a SQL workload and recommends indexes that can improve the performance for the following: joins, single table scans, and ORDER BY or GROUP BY operations. The Index Advisor does not differentiate tables that are used for specific intentions, such as the base table for a materialized view or as a table within a cache group. As long as the table is used in queries in the SQL workload, the Index Advisor may recommend indexes on that table.

The Index Advisor generates the CREATE statement for each recommended index, which you can choose to issue. A database administrator should review each CREATE statement recommended for new indexes before they are applied since the Index Advisor may recommend the following:

• Indexes that are duplicates of existing indexes.

• Indexes for tables or columns of tables that are created and dropped during a SQL workload. However, you could add the CREATE statement for the recommended index in the SQL workload after the DDL that creates the tables or columns of tables and before they are dropped.

• Indexes that cannot be created, such as a unique index for a data set where the data is not unique. In this case, you should ignore this recommendation.

• Index creation options where you can create an index as either a UNIQUE or non-unique index. The Index Advisor suggests both index types. You can only create one of the indexes as both suggested indexes have the same index name. While the optimizer thinks that the UNIQUE index is better for the specified workload, you can choose to create the non-unique index. Consider creating the UNIQUE index if the column only contains unique values. Consider creating the non-unique index if the column contains non-unique value.

The Index Advisor does not cover the following:

• It does not optimize for memory use.

• It does not consider maintenance costs.

• It does not recommend that existing indexes be dropped if they are not useful.

• It does not recommend indexes for global temporary tables.

The recommended steps to use the Index Advisor are as follows:

• Prepare for Running the Index Advisor

• Capture the Data Used for Generating Index Recommendations

• Retrieve Index Recommendations and Data Collection Information

• Drop Data Collected for the Index Advisor and Finalize Results

• Example Using Index Advisor Built-In Procedures