All-AMP and single-AMP operations are well-understood. All-AMP retrieval typically involves full table scans, including NUSI sub-table scans. On the other hand, single-AMP retrieval typically involves indexed access, such as UPI and NUPI.
Group-AMP operations optimize requests by minimizing the number of active AMPs. These operations fall between single-AMP and all-AMP operations. The Optimizer employs group-AMP for data retrieval and joins.
Various factors contribute to enhanced performance in the Group-AMP operation. Unlike the all-AMP operation, Group-AMP eliminates the need for table locks and enables the Optimizer to utilize row hash locks for the relevant rows instead.
Using row hash locking substantially reduces the likelihood of simultaneous execution of insert and update statements resulting in blockage.
Group-AMP operations eliminate the need for the table lock/release steps required at the start and end of a request in all-AMP operations.
When will the Optimizer use Teradata Group AMP Operations?
Group-AMP operations rely on the Optimizer’s capacity to identify request steps that consume less than half of the available AMPs. For instance, group-AMP is only utilized in a system with 100 AMPs if no more than 50 AMPs are necessary to retrieve data. The collection of statistics significantly aids the Optimizer in approximating the number of implicated AMPs. In the absence of statistics, the probability of the Optimizer utilizing group-AMP is minimal.
Teradata must adhere to the following usage criteria: the execution plan must be driven by either a single-AMP step or a group-AMP step. Since NUSI access is always an all-AMP operation, it cannot be the first phase of the execution plan when group-AMP access is utilized. Additionally, group-AMP access cannot be used when a product join step with data relocation is based on table duplication.
Group-AMP, OLTP, and Tactical Workload
Group-AMP operations can improve OLTP and tactical workloads by employing rowhash-level locks on fewer AMPs, increasing read and update concurrency compared to all-AMP operations. It is worth noting that the benefits of group-AMP operations become more significant as the number of AMPs in the system increases.
