How Teradata Group AMP Operations improve Performance
The meaning of all-AMP and single-AMP operations is widely known. We associate all-AMP data retrieval mainly with full table scans (including NUSI sub-table scans) and single-AMP data retrieval with indexed access (such as UPI, NUPI).
Group-AMP operations are an additional way of optimizing requests, by reducing the number of active AMPs. From this point of view, the group-AMP operation can be placed somewhere between the single-AMP and the all-AMP operation. The Optimizer can use group-AMP for data retrieval and joins.
Several different factors achieve the performance improvement over the all-AMP operation. Group-AMP doesn't require table locks; it allows the Optimizer to use row hash locks for the affected rows.
The likelihood of simultaneously executed insert and update statements blocking each other decreases substantially if row hash locking is used.
Group-AMP operations don't require the two additionally all-AMP steps which are needed in all-AMP steps to apply for the table lock/release at the begin/end of the request.
When will the Optimizer use Teradata Group AMP Operations?
Group-AMP operations depend on the Optimizer's ability to detect request steps which require less than 50% of the available AMPs. If your system consists of 100 AMPs, group-AMP is only considered if not more than 50 AMPs need to be active to retrieve the data. Collected statistics play a significant role as they help the Optimizer to estimate the number of involved AMPs. Without statistics, the probability that the Optimizer uses group-AMP is little.
Another usage criteria are that the execution plan must be driven from a single-AMP step or the group-AMP step. As NUSI access is always an all-AMP operation, which means that the first phase of the execution plan never will be an NUSI access if group-AMP access is used. If any step in the execution plan is a product join with its data relocation being based on table duplication, group-AMP neither is used.
Group-AMP, OLTP, and Tactical Workload
Group-AMP operations can be very useful in OLTP and for tactical workload: By using locks on the rowhash level on fewer AMPs, read and update concurrency can be higher than in the case of all-AMP operations. The alert reader will have recognized that the advantage of group-AMP operations increases with the number of AMPs in the system.