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

Parallelism
HP NonStop SQL/MX Query Guide—523728-003
8-12
Sort
partial group by must be combined in a serial finalization step before the query result is
used. A partial scalar aggregate can be executed in parallel without regard to the
partitioning scheme, but the partial aggregate must be combined in a serial finalization
step.
Sort
SQL/MX can sort in parallel by using multiple ESPs or serially in the master executor.
A parallel sort requires a merge of sorted streams by the master executor or by other
ESPs.
Insert and Select
For both parallel and serial inserts and selects, the optimizer tries to insert rows
sequentially (rather than randomly) as much as possible. For performance reasons, the
optimizer generates a plan so that rows selected are ordered and partitioned the same
way as the target table. This feature can involve a sort prior to the insert. You can
disable this feature (to ensure that the optimizer does not perform a sort prior to
inserting rows) by setting the default setting UPD_ORDERED to OFF. For more
information, see System Default Settings That Affect Parallelism on page 8-25 and the
SYSTEM_DEFAULTS Table entry of the SQL/MX Reference Manual.
Combining Different Types of Parallelism
Multilevel parallelism describes the ability of each operator to have a different level of
partitioned parallelism, including none. An example of multilevel parallelism is that
some of the operators closest to the root are not running in parallel, so they are
executing in the master, while lower level operators are running in parallel. It is also
possible for different operators to run in parallel but use a different number of ESPs.
SQL/MX supports the concept of some operators using the natural level of parallelism,
while other operators maximize the number of partitions based on the number of
available CPUs. At the DAM level, partitioning is fixed.
Explaining Parallel Plans
To review the optimized parallel plan, use the EXPLAIN function. For details on the
EXPLAIN function and reading the output, see Section 4, Reviewing Query Execution
Plans.
How to Determine if You Have a Parallel Plan
Make sure that the default settings that enable parallelism are on. For more
information, see System Default Settings That Affect Parallelism on page 8-25.
Compile the query and use the EXPLAIN function to review the query plan.