DB2 Version 9.7 for Linux, UNIX, and Windows

Choosing an optimization class

Setting the optimization class can provide some of the advantages of explicitly specifying optimization techniques.

This is true, particularly when:

Managing very small databases or very simple dynamic queries
Accommodating memory limitations on your database server at compile time
Reducing query compilation time; for example, during statement preparation

Most statements can be adequately optimized with a reasonable amount of resource by using the default optimization class 5. Query compilation time and resource consumption are primarily influenced by the complexity of a query; in particular, by the number of joins and subqueries. However, compilation time and resource consumption are also affected by the amount of optimization that is performed.

Query optimization classes 1, 2, 3, 5, and 7 are all suitable for general use. Consider class 0 only if you require further reductions in query compilation time, and the SQL and XQuery statements are very simple.

Tip: To analyze a long-running query, run the query with db2batch to determine how much time is spent compiling and executing the query. If compilation time is excessive, reduce the optimization class. If execution time is a problem, consider a higher optimization class.

When you select an optimization class, consider the following general guidelines:

Start by using the default query optimization class 5.
When choosing a class other than the default, try class 1, 2, or 3 first. Classes 0, 1, and 2 use the greedy join enumeration algorithm.
Use optimization class 1 or 2 if you have many tables with many join predicates on the same column, and if compilation time is a concern.
Use a low optimization class (0 or 1) for queries that have very short run times of less than one second. Such queries tend to:
- Access a single table or only a few tables
- Fetch a single row or only a few rows
- Use fully qualified and unique indexes
- Be involved in online transaction processing (OLTP)
Use a higher optimization class (3, 5, or 7) for queries that have longer run times of more than 30 seconds.
Class 3 or higher uses the dynamic programming join enumeration algorithm, which considers many more alternative plans, and might incur significantly more compilation time than classes 0, 1, or 2, especially as the number of tables increases.
Use optimization class 9 only if you have extraordinary optimization requirements for a query.

Complex queries might require different amounts of optimization to select the best access plan. Consider using higher optimization classes for queries that have:

Access to large tables
A large number of views
A large number of predicates
Many subqueries
Many joins
Many set operators, such as UNION or INTERSECT
Many qualifying rows
GROUP BY and HAVING operations
Nested table expressions

Decision support queries or month-end reporting queries against fully normalized databases are good examples of complex queries for which at least the default query optimization class should be used.

Use higher query optimization classes for SQL and XQuery statements that were produced by a query generator. Many query generators create inefficient queries. Poorly written queries require additional optimization to select a good access plan. Using query optimization class 2 or higher can improve such queries.

For SAP applications, always use optimization class 5. This optimization class enables many DB2® features optimized for SAP, such as setting the DB2_REDUCED_OPTIMIZATION registry variable.

In a federated database, the optimization class does not apply to the remote optimizer.