Optimization classes

The optimization classes differ in the number and type of optimization strategies that are considered during the compilation of a query. Although you can specify optimization techniques individually to improve runtime performance for the query, the more optimization techniques that you specify, the more time and system resources query compilation will require.

You can specify one of the following optimization classes when you compile an SQL or XQuery statement.

0

This class directs the optimizer to use minimal optimization when generating an access plan, and has the following characteristics:

• Frequent-value statistics are not considered by the optimizer.
• Only basic query rewrite rules are applied.
• Greedy join enumeration is used.
• Only nested loop join and index scan access methods are enabled.
• List prefetch is not used in generated access methods.
• The star-join strategy is not considered.

This class should only be used in circumstances that require the lowest possible query compilation overhead. Query optimization class 0 is appropriate for an application that consists entirely of very simple dynamic SQL or XQuery statements that access well-indexed tables.

1

This optimization class has the following characteristics:

• Frequent-value statistics are not considered by the optimizer.
• Only a subset of query rewrite rules are applied.
• Greedy join enumeration is used.
• List prefetch is not used in generated access methods.

Optimization class 1 is similar to class 0, except that merge scan joins and table scans are also available.

2

This class directs the optimizer to use a degree of optimization that is significantly higher than class 1, while keeping compilation costs for complex queries significantly lower than class 3 or higher. This optimization class has the following characteristics:

• All available statistics, including frequent-value and quantile statistics, are used.
• All query rewrite rules (including materialized query table routing) are applied, except computationally intensive rules that are applicable only in very rare cases.
• Greedy join enumeration is used.
• A wide range of access methods is considered, including list prefetch and materialized query table routing.
• The star-join strategy is considered, if applicable.

Optimization class 2 is similar to class 5, except that it uses greedy join enumeration instead of dynamic programming join enumeration. This class has the most optimization of all classes that use the greedy join enumeration algorithm, which considers fewer alternatives for complex queries, and therefore consumes less compilation time than class 3 or higher. Class 2 is recommended for very complex queries in a decision support or online analytic processing (OLAP) environment. In such environments, a specific query is not likely to be repeated in exactly the same way, so that an access plan is unlikely to remain in the cache until the next occurrence of the query.

3

This class represents a moderate amount of optimization, and comes closest to matching the query optimization characteristics of Db2® for z/OS®. This optimization class has the following characteristics:

• Frequent-value statistics are used, if available.
• Most query rewrite rules are applied, including subquery-to-join transformations.
• Dynamic programming join enumeration is used, with:
  • Limited use of composite inner tables
  • Limited use of Cartesian products for star schemas involving lookup tables
• A wide range of access methods is considered, including list prefetch, index ANDing, and star joins.

This class is suitable for a broad range of applications, and improves access plans for queries with four or more joins.

5

This class directs the optimizer to use a significant amount of optimization to generate an access plan, and has the following characteristics:

• All available statistics, including frequent-value and quantile statistics, are used.
• All query rewrite rules (including materialized query table routing) are applied, except computationally intensive rules that are applicable only in very rare cases.
• Dynamic programming join enumeration is used, with:
  • Limited use of composite inner tables
  • Limited use of Cartesian products for star schemas involving lookup tables
• A wide range of access methods is considered, including list prefetch, index ANDing, and materialized query table routing.

Optimization class 5 (the default) is an excellent choice for a mixed environment with both transaction processing and complex queries. This optimization class is designed to apply the most valuable query transformations and other query optimization techniques in an efficient manner.

If the optimizer detects that additional resources and processing time for complex dynamic SQL or XQuery statements are not warranted, optimization is reduced. The extent of the reduction depends on the machine size and the number of predicates. When the optimizer reduces the amount of query optimization, it continues to apply all of the query rewrite rules that would normally be applied. However, it uses greedy join enumeration and it reduces the number of access plan combinations that are considered.

7

This class directs the optimizer to use a significant amount of optimization to generate an access plan. It is similar to optimization class 5, except that in this case, the optimizer never considers reducing the amount of query optimization for complex dynamic SQL or XQuery statements.

9

This class directs the optimizer to use all available optimization techniques. These include:

• All available statistics
• All query rewrite rules
• All possibilities for join enumeration, including Cartesian products and unlimited composite inners
• All access methods

This class increases the number of possible access plans that are considered by the optimizer. You might use this class to determine whether more comprehensive optimization would generate a better access plan for very complex or very long-running queries that use large tables. Use explain and performance measurements to verify that a better plan has actually been found.