SQL/MX 2.x Query Guide (G06.24+, H06.03+)

Reviewing Query Execution Plans
HP NonStop SQL/MX Query Guide—523728-003
4-10
Optimization Tips
•
OPTIMIZATION_LEVEL
The settings for this attribute indicate increasing effort in optimizing SQL queries:
Optimization
Level Description
0 The compiler optimization effort at this level is minimal (it uses
heuristics to perform one-pass optimization for the shortest possible
compilation time). This level is most suitable for small tables or when
plan quality is not important.
2 The compiler uses a combination of heuristics and limited search
space enumeration. In general, this level produces good quality plans,
although it might miss a globally optimal plan. The compilation time is
significantly shorter in comparison with the higher optimization levels
(3 and 5).
3 (default) This default optimization level is recommended for general use.
The compiler performs a thorough two-pass optimization. For more
complex queries (above 5-way joins), it uses a combination of
heuristics, search space enumeration and compile time controlling
algorithms to find the best possible plan within a reasonable compile
time. For simple queries (5-way joins and below), the compiler does
an exhaustive search.
The compiler compiles plans for up to 40-way joins. The generated
plans are significantly better than the plans produced with optimization
level 0 and, in general, better than the plans generated at level 2.
To get faster query compilation time, you might consider optimization
2.
To perform a complete exhaustive search, which might lead to a
better plan but requires longer compilation time, you might consider
optimization 5.
5 The compiler performs a full optimization of the query using
exhaustive search algorithms.
The resulting plan is a globally optimal one, but the trade off is that it
might take a long compilation to find it. For complex queries (11-way
joins and higher), the compilation time can be prohibitively long. This
compilation time threshold is even longer (9-way joins) for queries that
involve semi-joins, outer joins, or subqueries.
Note. Optimization level values must be either 0, 2, 3 or 5. Values outside this range
return an error 2055 (invalid value for DEFAULTS attribute OPTIMIZATION_LEVEL.
Values 1 and 4 are currently reserved and should not be specified. If you specify these
values, the compiler replaces the value with the next lowest optimization level. For
example, 4 is replaced by 3, and 1 is replaced by 0.