SQL/MX 2.x Reference Manual (G06.24+, H06.03+)

Metadata Tables
HP NonStop SQL/MX Reference Manual—523725-004
10-57
Query Optimization and Performance
For more information about query optimization, see the SQL/MX Query Guide.
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 more information about this setting, see the
SQL/MX Query Guide.
Allowable values: 1 through 4294967040.
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.
REMOTE_ESP_ALLOCATION Set to ON, OFF, or SYSTEM. If ON, NonStop
SQL/MX is forced to bring up ESPs on all the
systems that are in the scope of the specific query
and all target systems become active systems. If
OFF, forces NonStop SQL/MX to bring up all ESPs
on the local system only. If SYSTEM, NonStop
SQL/MX decides which target systems should be
used for ESP placement. In this case, systems
chosen as active are a subset of the target systems.
The SYSTEM setting is the preferred setting for
REMOTE_ESP_ALLOCATION.
For more information about this setting, see the
SQL/MX Query Guide.
The default is SYSTEM.
UPD_ORDERED Set to ON or OFF. If ON, the optimizer generates
and considers plans where the rows must be
inserted, updated, or deleted in clustering key order.
If OFF, the optimizer does not generate plans where
the rows must be inserted, updated, or deleted in
clustering key order.
The default is ON.
ZIG_ZAG_TREES Set to ON or OFF. Enables (ON) or disables (OFF)
the optimizer to consider zig-zag trees in addition to
linear trees.
For more information about this setting, see the
SQL/MX Query Guide.
The default is OFF.
Attribute Setting