Index advisor

The query optimizer analyzes the row selection in the query and determines, based on default values, if creation of a permanent index improves performance. If the optimizer determines that a permanent index might be beneficial, it returns the key columns necessary to create the suggested index.

The optimizer is able to perform radix index probe over any combination of the primary key columns, plus one additional secondary key column. Therefore it is important that the first secondary key column is the most selective secondary key column. The optimizer uses radix index scan with any of the remaining secondary key columns. While radix index scan is not as fast as radix index probe, it can still reduce the number of keys selected. It is recommended that secondary key columns that are fairly selective are included.

Determine the true selectivity of any secondary key columns and whether you include those key columns in the index. When building the index, make the primary key columns the left-most key columns, followed by any of the secondary key columns chosen, prioritized by selectivity.

After creating the suggested index and executing the query again, it is possible that the query optimizer will choose not to use the suggested index. It does not include join, ordering, and grouping criteria. The SQE optimizer includes selection, join, ordering, and grouping criteria when suggesting indexes. Local selection advice can now factor in both AND and OR predicates with the qualifications mentioned below.

You can access index advisor information in many different ways. These ways include:

  • The index advisor interface in System i® Navigator
  • SQL performance monitor Show statements
  • Visual Explain interface
  • Querying the Database monitor view 3020 - Index advised.