Generate Virtual Indexes Tutorial

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.

Create index-set candidates

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 $image\B_CopyToIndexExpert.gif$ .

Click $image\B_ShowPlan.gif$ to view the current access plan for the SQL.
Click $image\B_GenerateIndexes.gif$ 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.

Tips:

In the Run Time pane, index-set alternatives are listed along with any SQL alternatives (which you generate by clicking ). In this way, you can easily compare the performance of the index-sets with each other and the SQL alternatives.
For the currently selected index-set alternative, view the its virtual DDL in the SQL Text pane. Use the SQL Information pane to view the index's virtual access plan and the optimized SQL that uses this plan.
Create you own virtual index sets by clicking $image\B_UserDefinedIndex.gif$ .

Test index sets

To obtain actual run-time statistics when the SQL statement when it uses each index-set scenario, click $image\B_BatchRun.gif$ , 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 $image\B_ImpactAnalysis.gif$ 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.

Please select your product:

To serve you better, please complete the Purpose of your Chat:

Recommended Solutions for Your Problem

SQL Optimizer for DB2 LUW 4.3.1 - Release Notes

Index Expert Tutorial

Generate Virtual Indexes Tutorial

Create index-set candidates

Test index sets