The Generate Indexes function analyzes the syntax of your original SQL statement and the database structure and then proposes new index candidates to help improve performance. The SQL statement can be executed using the index recommendations to identify which index yields the greatest performance gain.
Click to open a SQL Optimizer session.
On the SQL tab, enter the SQL statement for which you want to analyze for index alternatives.
Note: To copy a SQL statement from other windows such as Scanned SQL Viewer, SQL Formatter, Database Explorer, or SQL Comparer, click .
Click to view the current access plan for the SQL.
Click to generate alternative index sets.
All index-set alternatives that Index Expert generates are listed in the in the Run Time pane. Index sets identified by the Index Expert Artificial Intelligence engine are labeled Setx; those recommended by DB2 are labeled DB2 LUW.
To obtain actual run-time statistics when the SQL statement when it uses each index-set scenario, click , select your benchmarking options, and click OK.
The Batch Run window opens, enabling you to view the run-time results as each scenario executes.
Important Note: This benchmark process may impact the performance of SQL statements executing on your database server.
When Batch Run is finished, use the Run Time pane view the results for each index-set alternative.
To analyze the impact of the index-set alternatives on the access plans of other SQL statements, click to run Impact Analysis.
Tip: To analyze the impact of virtual index sets on other SQL in the database, click . See Analyze the Impact of New Indexes for more information.