DB2 10.5 for Linux, UNIX, and Windows

Types of optimization guidelines

The DB2® optimizer processes a statement in two phases: the query rewrite optimization phase and the plan optimization phase.

The optimized statement is determined by the query rewrite optimization phase, which transforms the original statement into a semantically equivalent statement that can be more easily optimized in the plan optimization phase. The plan optimization phase determines the optimal access methods, join methods, and join orders for the optimized statement by enumerating a number of alternatives and choosing the alternative that minimizes an execution cost estimate.

The query transformations, access methods, join methods, join orders, and other optimization alternatives that are considered during the two optimization phases are governed by various DB2 parameters, such as the CURRENT QUERY OPTIMIZATION special register, the REOPT bind option, and the DB2_REDUCED_OPTIMIZATION registry variable. The set of optimization alternatives is known as the search space.

The following types of statement optimization guidelines are supported:

General optimization guidelines, which can be used to affect the setting of general optimization parameters, are applied first, because they can affect the search space.
Query rewrite guidelines, which can be used to affect the transformations that are considered during the query rewrite optimization phase, are applied next, because they can affect the statement that is optimized during the plan optimization phase.
Plan optimization guidelines, which can be used to affect the access methods, join methods, and join orders that are considered during the plan optimization phase, are applied last.