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

Reviewing Query Execution Plans
HP NonStop SQL/MX Query Guide—523728-003
4-12
Optimization Tips
attribute value is set to 1), SQL/MX might not push down the plan because of the
plan cost. The system-defined default setting (0) means that SQL/MX does not
attempt to push down.
You can verify if statements have been pushed down to DAM by reviewing the
EXPLAIN output for the plan. If the plan shows the PARTITION_ACCESS
operator, DAM access is being used, and the operators below the
PARTITION_ACCESS operator have been pushed down to DAM.
For a discussion of compound statements, see the SQL/MX Programming Manual
for C and COBOL.
•
REMOTE_ESP_ALLOCATION
Use the OFF setting to force SQL/MX to bring up all ESPs on the local system
only. Consider this setting in these cases:
•
If the network connection is slow and you want to reduce network traffic
(depending on the partition distribution of the tables in the query)
•
You want to reduce the consumption of memory on remote systems
•
If joins increase the number of rows produced
This setting also forces SQL/MX to try to achieve maximum parallelism without
consideration for communication across the network.
Use the SYSTEM setting when you want to use all active nodes. Consider this
setting in these cases:
•
Nodes where partitions exist on at least two tables
•
Nodes where partitions exist on a single table
Use the ON setting to use all nodes where partitions reside.
•
ZIG_ZAG_TREES
In a left linear tree, the right child is always a single table or subquery, and the left
child is a subtree of one or more tables. In a zig-zag tree, one child is always a
table, and the other child is a subtree of one or more tables, usually formed in zig-
zag fashion. When the default value is set to OFF (the default setting), the
optimizer examines predominately left linear trees and only a few promising zig-
zag trees. When the default is set to ON, the optimizer is directed to enumerate
larger numbers of zig-zag trees in the search for the optimal plan. Figure 4-1 on
page 4-13 shows left linear and zig-zag trees.