All-AMP tactical queries running against partitioned tables benefit significantly from row and column partition elimination. If a query specifies the partitioning key value for the table, or for a subset of the columns when the table is column-partitioned, a subset of rows can be scanned, rather than the entire table.
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, 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 complex, there is 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 all AMPs. For other queries, the group may be a subset of the AMPs that is determined when the step runs.
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.