SQL/MX 3.2.1 Query Guide (H06.26+, J06.15+)

Reviewing Query Execution Plans
HP NonStop SQL/MX Release 3.2.1 Query Guide—663851-003
4-11
Optimization Tips
To maintain compatibility, the compiler accepts the previous optimization settings of
MINIMUM, MEDIUM and MAXIMUM. The values are mapped as follows:

OPTS_PUSH_DOWN_DAM
For compound statements, the predicates for each statement must identify the
DAM process so that the single DAM process is identified by the compiler.
Remember that NonStop SQL/MX requires more than one statement within a
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.
SQL/MX Release 1.x SQL/MX Release 3.x
MINIMUM 0
MEDIUM 3
MAXIMUM 5
TP663851.fm Page 11 Wednesday, January 30, 2013 5:37 PM