A sparse map distributes a table over a subset of system AMPs. A single-AMP map selects one AMP total; a one-AMP-per-node map selects multiple AMPs when there are multiple nodes.
Part of the Teradata Internals and Operations guide.
Single-AMP and multi-AMP sparse maps are distinct. Many small tables may share a map, but practical metadata, capacity and workload limits still apply; monitor hot spots and growth.
Sparse maps enable work on small tables to be limited to one or a few AMPs, thus avoiding the need for all-AMP steps.
Given a 500-amp system, each AMP must perform the following tasks to verify the existence of rows, despite the table being limited to only three.
- Get an AMP worker task.
- Read the table header.
- Reserve memory
- Avoided AMP work is not necessarily one avoided physical disk read per AMP: caching and empty-table metadata can change physical I/O. Measure the actual workload benefit.
Remember that the system may be congested, and an AMP without any available row may experience flow control, causing a delay in the access step.
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 bookTeradata 16.10’s sparse maps feature enables swift conversion of all-AMP steps to single-AMP steps for small tables. In a 500 AMP system, only one AMP would require an AMP worker task.
Avoided AMP work is not necessarily one avoided physical disk read per AMP: caching and empty-table metadata can change physical I/O. Measure the actual workload benefit.
Tables that are frequently accessed, remain small over time, and possess fewer rows than AMPs are ideal candidates for relocation to the sparse map.
128 KiB per selected AMP is a sizing heuristic, not a sparse-map engine capacity limit. Choose maps from table size/growth, access patterns and resource balance.
Exercise caution when searching for appropriate candidates for the sparse map. Though empty tables may appear ideal for relocation, it is imperative to confirm that they remain vacant and do not serve as temporary repositories that will subsequently be filled with countless rows during the next batch load.
Sparse maps could potentially supplant certain established methods outlined in this article.
David Wellman’s outstanding article explores the contrasting methods of managing small tables.
All Rows on one AMP by Design – Part 2
Deciding what to change in your data platform?
Compare optimisation, modernisation and migration against your workload and business requirements.
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
How the Sparse Map feature is being enabled or is it all internal based on the size of the table like you are mentioning.
Thanks.