SQL/MX 3.1 Query Guide (H06.23+, J06.12+)

Reviewing Query Execution Plans

HP NonStop SQL/MX Release 3.1 Query Guide—663851-001

4-13

Verifying DAM Access

Use the OFF setting to force NonStop 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 NonStop 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.

Verifying DAM Access

You can verify if operators are executing in DAM by reviewing the EXPLAIN output for

the plan. If the plan shows the PARTITION_ACCESS operator, DAM access is being

Figure 4-1. Left Linear and Zig-Zag Trees

Left Linear Tree

Zig-Zag Tree

VST041.vsd

TP663851.fm Page 13 Monday, October 17, 2011 11:48 AM