SQL/MX 3.1 Reference Manual (H06.23+, J06.12+)

Metadata Tables
HP NonStop SQL/MX Release 3.1 Reference Manual—663850-001
10-71
Query Optimization and Performance
OPTIMIZATION_LEVEL Set to 0, 2, 3, or 5 to indicate increasing effort in
optimizing SQL queries. Values 1 and 4 are reserved
for future use.
For additional information about this setting, see the
SQL/MX Query Guide.
The default is 3.
OPTS_PUSH_DOWN_DAM Set to ON (1) or OFF (0). When set to ON, the
system considers pushing down a plan to DAM for
compound statements or nested joins. When set to
OFF, the system does not consider this option.
When pushing a plan down to DAM is possible (the
value is ON), NonStop SQL/MX might not select the
push-down plan because of its cost.
For additional information about this setting, see the
SQL/MX Query Guide.
The default is OFF.
PARALLEL_NUM_ESPS Set to the keyword SYSTEM or to the maximum
number of ESPs (an unsigned positive integer) that
should be used for a particular operator.
If set to SYSTEM, NonStop SQL/MX calculates the
value.
If set to a number, the value must be less than the
number of CPUs in the cluster.
For additional information about this setting, see the
SQL/MX Query Guide.
Allowable values: 1 through 2147483647.
The default is SYSTEM (no maximum).
PREFERRED_PROBING_
ORDER_FOR_NESTED_JOIN
Set to ON or OFF. If ON, the optimizer generates
and considers plans where the rows of the inner
table must be read in the key order of the access
path. If OFF, the optimizer does not generate plans
where the rows must be read in the key order of the
access path.
The default is OFF.
Attribute Setting