All-AMP describes participating AMPs, not a mandatory full-table or subtable scan. NUSI access can be narrowed by other predicates and maps; inspect the actual access steps.
Part of the Teradata Internals and Operations guide.
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.
A group-AMP plan may use narrower locks, but group-AMP participation does not guarantee that no table lock appears elsewhere in the request. Read the lock steps explicitly.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free bookUsing row hash locking substantially reduces the likelihood of simultaneous execution of insert and update statements resulting in blockage.
When will the Optimizer use Teradata Group AMP Operations?
Group-AMP eligibility is optimizer-, release- and plan-dependent. The historical half-AMP example is not a universal cutoff. Statistics can help estimate participating AMPs; a group_amps spool label alone does not prove a group-AMP request.
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.
Need help with query performance or platform costs?
Diagnose plans, skew, spool pressure and expensive workloads. Agree a baseline and acceptance criteria before making changes.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free book