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

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

Index Options

General

 

Intelligence

ClosedView Generate Indexes tab – Intelligence button options

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.

 

Related Topics

Index Options

ClosedView Generate Indexes tab – Options button 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

Index Options

Description

Retrieve index selectivity by sampling a maximum number of records

(Default: 1000, Range = 10 to 99999)

Specify the number of records to be selected for determine the selectivity of the data.

Evaluate columns in SELECT list

Specify whether to consider SELECT list columns when generating indexes.

Maximum number of columns in a composite index

(Default 4, Range 1 to 30)

Specify the maximum number of columns in a composite index.

Maximum number of indexes in an Index Set

(Default 4, Range 1 to 99)

Specify the maximum number of indexes in an index set.

Quota

Index Generation Quotas

Description

Index Generation Quota

Specify the maximum number of indexes generated.

Index Set Generation Quota

Specify the maximum number of index sets generated.

 

Related Topic

General

ClosedView Index Expert tab – General button options

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 prefix of index name:

(Default: QIDX )

Enter the prefix that is placed on the index name when the Index Expert automatically generates index candidates.

Allow reverse scans

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

Retrieve access plan upon run time

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.

 

Related Topics

Related Documents