SQL Optimizer for DB2 LUW 4.4 - Installation Guide

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