SQL/MX 3.2.1 Query Guide (H06.26+, J06.15+)

Forcing Execution Plans
HP NonStop SQL/MX Release 3.2.1 Query Guide—663851-003
5-11
Forcing Group By Operations to the Data Access
Manager
more control. Note that, while CONTROL TABLE enables you to force a single
operation, CONTROL QUERY SHAPE requires that you force the entire tree structure.
Forcing Group By Operations to the Data Access Manager
The SQL/MX compiler has two methods for performing grouping and aggregation.

Sort group by requires the child of the group by to be ordered on the group-by
column and thus preserves ordering. This method incurs additional sort operations
unless input is already sorted on the grouping columns.

Hash group by uses hashing operations to perform grouping and has no ordering
requirement on children. In general, hash group by is more cost effective than sort
group by.
The compiler uses algorithms based on cost to determine which group by to perform,
hash or sort. The aggregate functions are AVG, COUNT, MAX, MIN, STDDEV, SUM,
and VARIANCE. For further information about aggregate functions, see the individual
entries in the SQL/MX Reference Manual.
Single table operations can always be pushed down to the DAM level, as shown in
Figure 5-2.
Notice that messages are exchanged from the SCAN operator in DAM to the GROUP
BY operator in the executor through the EXCHANGE node. For tables with millions of
rows, this amount of messaging can be inefficient. The group by operation can be
pushed down to DAM to reduce the message traffic between DAM and the executor,
as demonstrated in the next example.
Note. GROUP BY operators are sometimes used even when no GROUP BY clause is
specified. The SQL/MX compiler inserts a GROUP BY operator into a SELECT DISTINCT
query to remove duplicate rows.
Figure 5-2. Group By Operator Not Using DAM
ROOT
GROUPBY
SCAN
DAM
Executor
messages
EXCHANGE
VST067.vsd
TP663851.fm Page 11 Wednesday, January 30, 2013 5:37 PM