SQL Optimizer for DB2 LUW 4.4.1 - User Guide

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.

Index Generation Details

The Index Generation Details dialog is optional and can be displayed after index generation. This dialog displays the following:

• Number of virtual indexes investigated

• Number of virtual index-sets initially created

• Number of virtual index sets that produced unique access plans

• Number of virtual index sets eliminated due to identical access plan

• A warning message if the Index Generation Quota or Index Set Generation Quota is reached.

  Note: Consider increasing the quota values on the Quota page for SQL Rewrite in Options to increase the likelihood of finding index sets that can give you better performance for the original SQL statement.

Unless disabled, the Index Generation Details dialog displays each time an index-generation process completes. To disable this window, clear the Show details on next index generation option in the Index Generation Details dialog.

If no index-set alternatives are available, the Optimize button is enabled to let you start an automatic rewriting of the original SQL statement for optimization.

To open the Index Generation Details dialog anytime

Select View | Show Index Generation Details.

Add Your Own Virtual Index Sets

Use the Simulate Indexes function to create your own virtual index sets.

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.

Creating Your Own Virtual Indexes or Index Sets

1. Click to open a SQL Optimizer session.

2. Enter the original SQL statement for which you feel performance improvement can be achieved with the addition of new indexes.

   Tip: 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 . This features automatically run the Generate Indexes function described in the next step.

3. Click to generate virtual index-set alternatives that Index Expert recommends. Use these alternatives as a basis for determining which additional virtual indexes you want to define.

4. Click to open the Simulate Indexes window.

   Note: If you have generated index sets (see step 3), these index sets are already listed in the Simulate Indexes window.

5. If no user-defined index exists, the Add Index window appears automatically. Otherwise, click on the right side of the Create Index here box.

6. Enter the index properties.

7. Select one of the three options:

   • Create a new Index Set for this indexto create the index and the index set to which the index belongs

   • Add to existing index set to create the index and add it to an existing user-defined index set

   • Not assign to any index set to add the index without adding it to any index set

8. Click OK to create the index and add it to the specified index set (in the top right pane).

9. Continue to create indexes and add them to index sets as needed.

Deleting User-Defined Indexes

1. Select the index you want to delete from the list in the top left pane (Create Index here).

2. Click .

   Note: You can delete only those indexes not belonging to an index set.

Adding Index Sets

1. Click in the top right pane (Create Index Set here).

2. Enter a name for the index set.

3. Select the indexes you wish to add to the set.

4. Click OK.

Deleting user-defined index set

1. Select the index set from the list in the top right pane (Create Index Set here).

2. Click .

Adding and Removing Index from Index Set

In the bottom pane of the Simulate Indexes window, add or remove indexes from the index set by double-clicking the appropriate cell.

Adding Index Sets as Alternatives

To add the finalized virtual index sets to the list of alternatives in SQL Optimizer, click OK.

The index sets display as alternatives in the Run Time pane on the SQL Optimizer window. From here, you can compare the index sets with other index sets and SQL alternatives to determine the best-performing alternative.

Analyze the Impact of New Indexes

Analyzing the impact of new indexes on other SQL statements before physically creating them on your database is essential. As new indexes may improve the performance of one SQL but downgrade the performance of others.

To analyze the impact of new indexes

1. From the SQL Optimizer window, generate or create the virtual index sets for your original SQL statement:

   • Click to use the Index Expert function to generate index-set alternatives. See Generate Index-Set Alternatives for more information.
   • Click to create your own virtual index-set alternatives. See Add Your Own Virtual Index Sets for more information.

2. Before performing the Impact Analysis function, check that you have a good sample of SQL statements stored in the SQL Repository. Analysis is based on the SQL statements stored the repository. Therefore, the SQL repository should contain a good sample of SQL statements that maybe affected by the new indexes if they were to be created.

3. In Run Time pane, select the index- set alternative that offers the best performance for your SQL statement.

4. Click . The Index Impact Analyzer window will be opened followed by the New Analysis wizard.

5. Follow the New Analysis wizard using the following pages to select the options for creating the analysis.

Analyzer

Selected SQL

Index