Page History
...
The main area lists index changes that you would like to make or that were recommended in the SQL tab. The following table describes the different index simulation parameters
Table 8- 1 Index simulation parameters
Parameter | Description |
---|---|
Change type (Simulation) | Indicates the type of the simulation, which can be one of the following: Index Creation, Index Modifications, or Index Deletion. |
Owner | DB2 owner of the index. |
Table | Name of the table that the index belongs to. |
Index | Name of the index. |
Unique | Indicates whether the index is unique or not. |
Allow Reverse Scan | Indicates the value of the reverse scan option (Yes/No). |
Columns | List of columns in the index. |
Include Columns | List of include columns in the index. |
Cluster Ratio | The estimated cluster ratio of the index. |
About the Summary area in the What-If tab
The following table describes the information that is shown in the Summary area after a simulation has been completed.
Table 8- 2 Simulation summary
Category | Item | Description |
---|---|---|
Improved statements | Statements whose cost has improved. | The number of statements that are positively impacted. |
Deteriorated statements | Statements whose cost has deteriorated. | The number statements that are negatively impacted. |
Unaffected statements | Statements whose cost has not been affected. | The number of statements that are not affected by the change. |
Affected statements by object type | Statements that were changed categorized by object. | The number of statements that were affected for each object category (a row for each index change). |
About the Association area in the What-If tab
...
The effect tab displays the following table columns
Table 8- 3 Effect tab
Column | Description |
---|---|
The Tune icon launches the SQL Tab on the Plan tab. | |
Statement | Name of the impacted statement. |
In DB2 | Resource consumption of the impacted statement during the selected time frame. The format is: hh:mm:ss.s. Use this column to assess the heavy resource consuming statements that are either positively or negatively impacted. |
Cost | Current estimated cost of the DB2 Optimizer, as taken from the latest explain plan. |
Predicted Cost | Predicted cost of the DB2 Optimizer for the new plan. |
Change Ratio | The ratio between the predicted cost and the current cost, expressed as a percentage. Therefore, 100% represents no change; a value greater than 100% is a degradation, and a value less than 100% is an improvement. If cost information is not available, the cost, predicted cost, and change ratio are all N/A. |
Executions | Number of times the statement was executed during the selected time frame. |
Comment | Precise for DB2 may include its own comment on the simulated change, or why, for example, costs are not available. |
Anchor | ||||
---|---|---|---|---|
|
...
The DB2 Optimizer is used to compare the cost of the original execution plan with the cost of the new execution plan. To simulate removing an index, you still need to add a proposed index change to the table in the Main area. This is either done for you on the Advice tab in the SQL tab, or you can click Add to open the What if... dialog box.
...
.
...