Generate Indexes Settings
The Generate Indexes tab on the Options window allows users to define the optimization level and other settings used to generate index-set alternatives.
The Settings include:
Intelligence
The Intelligence page under the Generate Indexes tab on the Options window allows you to select the optimization level for generating index-set alternatives.
Intelligence Level
|
Option |
Description |
|---|---|
|
Custom |
Use the Index Options page settings to determine the optimization level as needed to generate the index-set alternatives. |
|
Predefine |
Set a specific optimization intelligence level here. Values on the Index Options page adjust to this level. Levels range from 0 to 10. The higher the setting, the higher the number of Index sets generated. Note: The General page settings can be adjusted independent of which intelligence level is chosen. |
Index Options
The Index Options page under the Generate Indexes tab on the Options window defines how to generates index-set alternatives. If you selected Customize on the Intelligence page, these values determine the optimization level for index generation.
Options
Quota
| Index Generation Quotas |
Description |
|---|---|
|
Index Generation Quota |
Specify the maximum number of indexes generated. |
|
Specify the maximum number of index sets generated. |
General
The General page under the Index Expert tab on the Options window allows you to control specific actions used by the Index Expert.
Index generation options
|
Index Generation Settings |
Description |
|---|---|
|
(Default: QIDX ) |
Enter the prefix that is placed on the index name when the Index Expert automatically generates index candidates. |
|
Specify to create an index that can support both forward and reverse scans; that is, the index will be scanned in the order defined in INDEX CREATE statement and also in the opposite (or reverse) order. | |
|
Generate Index Sets with DB2 LUW recommendations |
Specify to retrieve the indexes that are recommended by DB2 LUW for a SQL statement using DB2's SET CURRENT EXPLAIN MODE RECOMMEND INDEXES command. These indexes are combined with the indexes generated by the Index Expert’s Artificial Intelligence engine into the index-set alternatives that you can evaluate. |
Access Plan
|
Access Plan Retrieval |
Description |
|---|---|
|
Specify whether to retrieve the access plan while retrieving the run time. |
Batch Run
|
Batch Run |
Description |
|---|---|
|
Automatically start Batch Run after generating index sets |
Specify to start the Batch Run as soon as the index sets have been generated. Batch Run creates each set of indexes, runs the SQL statement, and then drop the indexes. |
Eliminate Index Set with
After Index Expert generates the initial index-set candidates, it eliminates candidates based on one of two conditions. Either an index-set access plan is identical to the original access plan or to another candidate's plan or the access plan has the same DB2 cost as the original plan or to another candidate's plan. Eliminating the candidates based on the plan comparison rather than cost comparison is more accurate. However, plan comparison can take longer because Index Expert compares each operation in the plans instead of only costs.
|
Eliminate Index Set With |
Description |
|---|---|
|
Identical Access Plan |
Specify whether to eliminate the index-set candidates that produce the same access plan for the SQL statement. |
|
Identical DB2 LUW cost |
Specify whether to eliminate the index-set candidates when the access plan has same DB2 cost. |