Indexes and the optimizer

 

Since the iSeries™ optimizer uses cost based optimization, the more information that the optimizer is given about the rows and columns in the database, the better able the optimizer is to create the best possible (least costly/fastest) access plan for the query. With the information from the indexes, the optimizer can make better choices about how to process the request (local selection, joins, grouping, and ordering).

The CQE optimizer attempts to examine most, if not all, indexes built over a table unless or until it times out. However, the SQE optimizer only considers those indexes that are returned by the Statistics Manager. These include only indexes that the Statistics Manager decides are useful in performing local selection based on the "where" clause predicates. Consequently, the SQE optimizer does not time out.

The primary goal of the optimizer is to choose an implementation that quickly and efficiently eliminates the rows that are not interesting or required to satisfy the request. Normally, query optimization is thought of as trying to find the rows of interest. A proper indexing strategy will assist the optimizer and database engine with this task.

• Instances where an index is not used
  DB2 Universal Database for iSeries does not use indexes in the following instances:

• Determining unnecessary indexes
  You can easily determine which indexes are being used for query optimization.

• Show index for a table
  You can display indexes that are created on a table using iSeries Navigator.

• Manage index rebuilds
  You can manage the rebuild of your indexes using iSeries Navigator. You can view a list of access paths that are rebuilding and either hold the access path rebuild or change the priority of a rebuild.

 

Parent topic:

Creating an index strategy