SQL/MX 2.x Reference Manual (H06.10+, J06.03+)

Metadata Tables
HP NonStop SQL/MX Reference Manual—544517-008
10-63
Query Optimization and Performance
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, NonStop SQL/MX is forced 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 additional information about this setting, see the
SQL/MX Query Guide.
The default is SYSTEM.
SORT_MAX_HEAP_SIZE_MB The default value is used for allocating the heap
memory size for operations involving the sort
operator. The minimum and maximum values are 0
and 1024 respectively.
The default value is 20.
UNION_TRANSITIVE_PREDICATES Set to ON, OFF, RESET, or SYSTEM. When set to
ON, NonStop SQL/MX generates transitive
predicates for join on unions. When set to OFF,
NonStop SQL/MX does not generate transitive
predicates for join on unions. The default is ON.
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 additional information about this setting, see the
SQL/MX Query Guide.
The default is OFF.
Attribute Setting