Generate Index Sets

Generate Index-Set Alternatives

The Generate Indexes function creates virtual indexes that can potentially improve performance of your original SQL statement.

This function is part of Index Expert, an internal component that facilitates the management and testing of generated and user-defined virtual indexes in your SQL Optimizer session. When you run Generate Indexes, Index Expert retrieves DB2-recommended virtual indexes, as well as generates its own virtual indexes. To generate index candidates, Index Expert analyzes the SQL syntax, relationships between tables, and data selectivity processes. It then combines the index candidates into one or more index sets, each set with a unique virtual access plan.

When the Generate Indexes process is complete, the resulting virtual index sets display as alternatives in the SQL Optimizer window. These alternatives are listed along with any SQL alternatives that were generated using the SQL Rewrite function. You can then test run and compare all alternatives to determine the best-performing version of your SQL.

To generate index-set alternatives for an SQL statement

1. Click to open a SQL Optimizer session.

2. On the SQL tab, enter the SQL statement for which you want to analyze for index alternatives.

   Note: To move an SQL statement from anther tool—such as Scanned SQL Viewer, SQL Formatter, Database Explorer, or SQL Comparer—into SQL Optimizer, select the statement within the tool, and click .

3. Click to generate new index-set alternatives for the original SQL statement.

To terminate the index generation process

Click .

Allow a few seconds to terminate all processes.

Include Index Recommendations from DB2

Index Expert uses DB2's SET CURRENT EXPLAIN MODE RECOMMENDED INDEXES command to retrieve DB2-recommended indexes for the SQL statement. These indexes are combined into a single index-set alternative labeled as DB2 LUW in the Run Time pane. To include DB2 index recommendations in the Generate Indexes process, select Generate Index Sets with DB2 LUW recommendations in the Index Expert Options settings.

Note: Consider setting the DB2 LUW MEM_UDF heap size to 1024 or higher if the index generation process does not produce DB2-recommended indexes.

Automatically Start the Batch Run

If you have selected the Automatically start Batch Run after generating index sets option, the Batch Run Criteria window automatically opens before the Generate Indexes process begins, allowing you to customize the Batch Run process.

View Index Generation Details

Once index generation is complete, the Index Generation Details window shows counts for generated, eliminated, and accepted index-set candidates. The final virtual index sets display as alternatives in the Run Time pane. These alternatives have the label Setx.

Tips:

• To create your own virtual index-sets, click . See Add Your Own Virtual Index Sets for more information.
• 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.