Chat now with support
Chat with Support

SQL Optimizer for DB2 LUW 4.4.1 - User Guide

SQL Optimizer for IBM® DB2® LUW
UsageStatistics Getting Started Options SQL Scanner SQL Optimizer SQL Formatter SQL Inspector Database Explorer SQL Repository Index Impact Analyzer Index Usage Analyzer User-Defined Temp Table Editor Functions SQL Functions SQL Information and Functions Activity Log
Tutorials About us Copyright

Selected Index Set

ClosedView the Batch Run Criteria Window (Selected Index Set)

The Selected Index Set tab of the Batch Run Criteria window is used to select or deselect which Index Sets are applied before retrieving the run time for the original SQL statement. Selected Index Sets are prefixed with a blue checkmark image\I_BlueCheckmark.gif. All Index Sets are selected by default.

Selecting Index Sets to test

To select or deselect one Index Set

Double-click the Index Set row.

To select or deselect all Index Sets

Right-click and select Select All or Unselect All.

To select Index Sets according to the cost values using the selection filter

  1. Select the Apply Index selection filter checkbox.

  2. Click Index selection filter image\B_SQLSelectionFilter.gif.

  3. Review the DB2 LUW cost retrieved from the virtual plans.

  4. Check the Exclude SQL with DB2 LUW cost greater than checkbox and enter a DB2 LUW cost.

Note: The Used Index can be de-selected only if the Used Index option is not selected in the SQL Termination tab of the Batch Run Criteria window and the Always run Used Index first option is not selected.

Changing the order the Index Sets are executed

The Index Sets are ranked by DB2 LUW Cost by default, with the exception for the Used Index which is placed at the top.

To change the execution order using the DB2 LUW cost

Click the column heading for either the DB2 LUW column or the SQL column to use the execution based on the corresponding column.

Note: High DB2 LUW cost does not necessary mean slower performance. If possible, it is recommended that all alternative indexes be tested.

To change the execution order for individual Index Sets

Click the row and then click Move Up image\B_MoveUp.gif or Move Down image\B_MoveDown.gif.

Always run Used Index first

The Used Index can always be run first even if it is not the index option that has the lowest DB2 LUW cost.

To always run Used Index first

Select the Always run Used Index first checkbox.

This checkbox is dimmed if Used Index is selected as a termination criterion on the SQL Termination tab or the Batch Termination tab since the run time from the existing indexes is going to be used to terminate the SQL statement using the other index options or to termination the Batch Run itself. In this case, you must run the SQL statement with the currently used indexes first to obtain the termination time.

 

Related Documents