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

Forcing Execution Plans
HP NonStop SQL/MX Query Guide—523728-003
5-13
Forcing Parallel Plans
Special Case for Sorted Group By Operations
For single partition sorted group by operations, only one GROUP BY operator is
required. Sort group by operations can be performed only if the input to the group by is
already ordered on the group by columns. In this case, the compiler can perform an
index scan or file scan for the group, and the additional cost of sorting is avoided. The
input into the sort group by must already be ordered, because DAM cannot perform the
sort.
Forcing Parallel Plans
The optimizer tries to present you with the most cost-efficient plan available. You might
find, however, that the optimizer does not always present the parallel plan that you
want. If you choose to use CONTROL QUERY SHAPE to force a parallel plan, note
that overriding the optimizer’s standard cost estimates can cause negative
performance.
Forcing ESP Parallelism
To get ESP parallelism, you can put an ESP_EXCHANGE operator in front of the
operator you want to run in parallel.
You might also get ESP parallelism by entering this command:
CONTROL QUERY SHAPE ESP_EXCHANGE(CUT);
You can force the number of ESPs used. For joins, specify the number of ESPs when
you specify the join. For nonjoin operations, specify the EXCHANGE logical operator
with the number of ESPs. Do not use the CUT keyword for the child. Otherwise, you
might not get the intended result.
Forcing DAM Parallelism
To get DAM parallelism, force SPLIT_TOP_PA above the item you want to run in DAM:
CONTROL QUERY SHAPE SPLIT_TOP_PA (ANYTHING);
Use this query:
SELECT JOBCODE, AVG(SALARY) FROM EMPLOYEE
WHERE JOBCODE > 55 AND SALARY <= 3000
GROUP BY JOBCODE
Note. To use ESP parallelism, you must have ATTEMPT_ESP_PARALLELISM set to ON or
SYSTEM. In addition, you must have more than one CPU in your system.
Caution. When you force the number of ESPs, you completely override the internal settings of
the optimizer. Use this option carefully and only if the optimizer does not choose the number of
ESPs that you think you need.