ALL AMPs SUM

Tools & Utilities
Enthusiast

ALL AMPs SUM

hi ppl,

Would like to know what exactly happens in 'ALL AMPs SUM' step ? This operation is performed in while using group by clause in the query.

Refer to the 3rd step in the explain statement of the query...

3)We do an all-AMPs SUM step to aggregate from database.tablename
by way of an all-rows scan with no residual conditions, and the grouping identifier in field 1025.
Aggregate Intermediate Results are computed locally, then placed in Spool 3. The size of Spool 3 is estimated with low confidence to be 3,930 rows. The estimated time for this step is 0.01 seconds.

4)We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of
an all-rows scan into Spool 1 (group_amps), which is built locally
on the AMPs. The size of Spool 1 is estimated with low confidence
to be 3,930 rows. The estimated time for this step is 0.01 seconds.

Regards,
Rock
2 REPLIES
Enthusiast

Re: ALL AMPs SUM

The SUM step performs summarization (aggregation). It is used whenever a query contains an aggregate function (e.g. SUM, MAX, MIN, AVERAGE) or GROUP BY. The general method is to generate partial summaries locally on each AMP, and then redistribute them according to the grouping key. The partial results are then combined to produce a spool containing the final result.
Enthusiast

Re: ALL AMPs SUM

Thanks Jim :)

Rgrds,
Rock