DWHPro Insights

Optimizing Teradata Requests with Group-AMP Operations for Improved Performance

By Roland Wenzlofsky ·

Optimizing Teradata Requests with Group-AMP Operations for Improved Performance

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.

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.

Teradata Query Performance Tuning: The DWHPro Guide book cover

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

Using 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.

Explore Teradata performance tuning

Teradata Query Performance Tuning: The DWHPro Guide book cover

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

Follow DWHPro in Google to see our articles more often in Search.

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

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

Protected by Security by CleanTalk and CleanTalk Anti-Spam