A query optimization class is a set of query rewrite rules and
optimization techniques for compiling queries.
The primary query optimization classes are:
- 1
- Restricted optimization. Useful when memory and processing
resources are severely restrained. Roughly equivalent to the
optimization provided by Version 1.
- 2
- Slight optimization. Specifies a level of optimization higher than
that of Version 1, but at significantly less optimization cost than levels 3
and above, especially for very complex queries.
- 3
- Moderate optimization. Comes closest to matching the query
optimization characteristics of DB2 for MVS/ESA.
- 5
- Normal optimization. Recommended for a mixed environment using both
simple transactions and complex queries.
- 7
- Normal optimization. The same as query optimization 5 except that
it does not reduce the amount of query optimization for complex dynamic SQL
queries.
Other query optimization classes, to be used only under special
circumstances, are:
- 0
- Minimal optimization. Use only when little or no optimization is
required (that is, for very simple queries on well-indexed tables).
- 9
- Maximum optimization. Uses substantial memory and processing
resources. Use only if class 5 is insufficient (that is, for very
complex and long-running queries that do not perform well at class 5).
In general, use a higher optimization class for static queries and for
queries that you anticipate will take a long time to execute, and a lower
optimization class for simple queries that are submitted dynamically or that
are run only a few times.
To set the query optimization for dynamic SQL statements, enter the
following command in the Command Line Processor:
SET CURRENT QUERY OPTIMIZATION = n;
where 'n' is the desired query optimization class.
To set the query optimization for static SQL statements, use the QUERYOPT
option on the BIND or PREP commands.
For more information,
see
the section on adjusting the optimization class in the Administration
Guide.
[ Top of Page |
Table of Contents |
Glossary |
Index ]
[ DB2 List of Books |
Search the DB2 Books ]