Index Advisor

Run the Index Advisor to get recommendations for improving the performance of a query by creating indexes. This advisor can also help you to investigate whether indexes that you propose might improve the performance of a query.
Note: Before following recommendations from the Index Advisor, run the Statistics Advisor and resolve high and medium-priority recommendations. The Index Advisor depends on valid statistics when it makes recommendations.

Indexes are important for query performance because a good set of indexes can provide more efficient access to tables and speed up query processing. Indexes can improve performance by providing local filtering, making join processing more efficient, avoiding the need for sort processing, providing index-only access, enforcing referential integrity, and more. Consequently, indexing is a fundamental part of query tuning. However, the growing complexity of SQL statements means that determining the correct indexes to create can be a challenging and difficult task. The Index Advisor provides detailed recommendations for indexes to create, and enables you to create those indexes.

The Index Advisor recommends indexes that you might create to improve the performance of the query, and enables you to create the indexes directly from the workflow assistant, if you have the appropriate authorities on the data server. Otherwise you can copy the CREATE INDEX statements and send them to someone who has the authority to create the indexes.

For SQL statements that run on DB2® for z/OS® data servers, you can use the Test Candidate Indexes page in the Invoke section of the workflow assistant to suggest indexes and run them virtually. On the Review Results of Testing Candidate Indexes page in the Review section, you can find out whether the proposed indexes improve query performance, and then deploy them to your data server environment.


Feedback