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

Forcing Execution Plans
HP NonStop SQL/MX Query Guide—523728-003
5-12
Forcing Group By Operations to the Data Access
Manager
To reduce this message traffic, move the GROUP BY operator down to DAM, as
shown in Figure 5-3.
The message traffic is reduced as follows. The SCAN operation still scans all 50,000
rows. However, the group by operation yields one result for each table partition (18).
This result is passed on to the EXCHANGE node. You should include another GROUP
BY operator above the EXCHANGE node to combine the results of the lower group by
operation, as shown in Figure 5-4.
General Case for Group By Operations
Pushing down group by operations for partitioned tables (more than one partition) and
for any hash group by operations requires an additional group by operation. The lower
group by performs the grouping and aggregation per partition, and the upper group by
performs the final aggregation across all groups. For hash group by situations, the
upper group by is required to handle any overflow that occurs in DAM.
Figure 5-3. GROUP BY Operator at the DAM Level
Figure 5-4. Two GROUP BY Operations
VST060.vsd
Executor
DAM
Groupby
Scan
Exchange
VST061.vsd
Root
Merges results from the partition
grouping
Partition grouping and aggregate
operations
Groupby
Scan
Groupby
Exchange