15.00 - Group AMP Check in Final Query Step - Teradata Database

Teradata Database Design

Teradata Database
User Guide

Group AMP Check in Final Query Step

Whether a query is single-AMP or all-AMP, simple or very complex, there is always an opportunity in the final step of the plan to reduce an all-AMP operation to a Group AMP operation.

Group AMP logic restricts which AMPs participate in the BYNET merge activity that is part of response processing. This action eliminates unnecessary all‑AMP activities at the end of each query. While this feature is likely to have a greater impact on short, tactical queries, any time all-AMP activities can be eliminated anywhere in any query plan, the potential system throughput increases because resources are freed for other work.

To see how this works, examine the final step in a complex decision support query that returns millions of rows. In this case, because of the number of rows returned, each AMP is active in response processing and the Group AMP group includes them all. For other queries, the group might be a subset of the AMPs that is determined when the step executes.

     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.  Then we do a SORT to order SORT to order Spool 1 
        by the sort key in spool field1.  The result spool file will not 
        be cached in memory.  The size of Spool 1 is estimated with no 
        confidence to be 379,305,309 rows.  The estimated time for this step is 
        32 minutes and 32 seconds.