The index candidates are either generated with the Advise Index function or manually created by the User-Defined Index function. To evaluate the impact of the index candidates on the SQL statement, the query plan is needed. You can obtain the query plans for all the index candidates by using the Batch Show Plan.
Note: The indexes are physically created on the database, the query plan is retrieved, and then the indexes are dropped. This process may affect other SQL statements executing on the database during this period.
Once the index candidates have been reviewed, you can create an index on the database.
To create an index
In the bottom right pane of the Index Advisor window, select the Index Set you want to create.
Select Index | Create Index(es).
The Create Indexes window displays with the CREATE INDEX statement for the selected Index Set. Click Create.
Analyzing the impact of new indexes on other SQL statements before permanently creating them on your database is essential. While improving the performance of one SQL statement, new indexes may degrade the performance of others.
First, generate or create the Index candidates for your original SQL statement in the Index Advisor window and determined which Index Set(s) give you the most performance for your SQL statement. Before performing the Index Impact Analysis function, you must save the SQL statements that may be impacted by the creation of new indexes in the SQL Repository or SQL Scanner.
To analyze the impact of creating the new indexes
In the Index Advisor window, select the bottom-left pane of the Index Set tab for the indexes that you want to analyze.
You can clear all the Index Sets that have been generated in the Index Advisor window.
To clear all the Index Sets