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. 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.