Optimizer decision-making rules

The optimizer uses a general set of guidelines to choose the best method for accessing data. The optimizer:

• Determines the default filter factor for each predicate in the selection clause.
• Extracts attributes of the table from internally stored information.
• Performs a key range estimate to determine the true filter factor of the predicates when the selection predicates match the left-most keys of an index.
• Determines the cost of creating an index over a table if an index is required. This index is created by performing either a table scan or creating an index-from-index.
• Determines the cost of using a sort routine or hashing method if selection conditions apply and an index is required.
• Determines the cost of table scan processing if an index is not required.
• For each index available, generally in the order of most recently created to oldest, the optimizer does the following until its time limit is exceeded:
  • Extracts attributes of the index from internally stored statistics.
  • Determines if the index meets the selection criteria.
  • Determines the cost of using the index using the estimated page faults and the predicate filter factors to help determine the cost.
  • Compares the cost of using this index with the previous cost (current best).
  • Picks the cheaper one.
  • Continues to search for best index until time out or no more indexes.

  The time limit controls how much time the optimizer spends choosing an implementation. It is based on how much time was spent so far and the current best implementation cost found. Dynamic SQL queries are subject to the optimizer time restrictions. Static SQL queries optimization time is not limited. For OPNQRYF, if you specify OPTALLAP(*YES), the optimization time is not limited.

For small tables, the query optimizer spends little time in query optimization. For large tables, the query optimizer considers more indexes. Generally, the optimizer considers five or six indexes (for each table of a join) before running out of optimization time. Because of this, it is normal for the optimizer to spend longer lengths of time analyzing queries against larger tables.