When you compile an SQL or XQuery statement, you can specify
an optimization class that determines how the optimizer chooses the
most efficient access plan for that statement.
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.