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 and 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 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 additional all-AMP steps needed in all-AMP steps to apply for the table lock/release at the beginning/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 that 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 estimate the number of involved AMPs. Without statistics, the probability that the Optimizer uses group-AMP is little.
Another usage criteria are that Teradata must drive the execution plan from a single-AMP step or the group-AMP step. As NUSI access is always an all-AMP operation, the first phase of the execution plan will never be NUSI access if group-AMP access is used. If any step in the execution plan is a product join with its data relocation based on table duplication, group-AMP neither is used.
Group-AMP, OLTP, and Tactical Workload
Group-AMP operations can benefit OLTP and tactical workload: By using locks on the rowhash level on fewer AMPs, read and update concurrency can be higher than all-AMP operations. The alert reader will recognize that group-AMP operations’ advantage increases with the number of AMPs in the system.