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

Forcing Execution Plans
HP NonStop SQL/MX Query Guide—523728-003
5-11
Forcing Group By Operations to the Data Access
Manager
•
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.
Consider this query against an EMPLOYEE table that contains 50,000 rows
(employees) and uses 18 partitions:
SELECT COUNT(*) FROM EMPLOYEE;
When the compiler presents a query plan like the plan shown in Figure 5-2, the SCAN
operator passes messages in blocks to the EXCHANGE node. The EXCHANGE node
passes the messages to the GROUP BY operator.
Figure 5-2. Group By Operator Not Using DAM
ROOT
GROUPBY
SCAN
DAM
Executor
messages
EXCHANGE
VST067.vsd