17.10 - All-AMP Tactical Queries and Partitioned Tables - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Database Design

Advanced SQL Engine
Teradata Database
Release Number
July 2021
English (United States)
Last Update

The major benefit that can be realized for all-AMP tactical queries running against partitioned tables is row and column partition elimination. Rather than scanning an entire table, it is possible to scan a smaller subset of rows if the query specifies the partitioning key value for the table or a subset of the columns when column partitioned.

Avoid adjusting the granularity of partitions more finely than is warranted by the majority of the queries that run against the table. For example, if users never access data with a granularity smaller than a monthly range, there is no additional performance advantage to be gained by defining partitions that have a smaller range than one month.

For all-AMP tactical queries, you should include the partitioning columns as a constraint in the WHERE clause of your queries. If a query joins row-partitioned tables that have identical row partitioning, you can enhance join performance still further by including an additional equality constraint on the partitioning columns of the two tables.

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 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 is32 minutes and 32 seconds.