Using Sparse Maps in Teradata 16.10 for Efficient Distribution of Small Tables Across AMPs

Teradata 16.10 introduces Sparse Maps, a feature that enables the consolidation of small table rows onto one AMP or one AMP per Node. These maps are defined as a subset of the hash maps and utilized to distribute table rows based on their primary index value across all AMPs.

Teradata 16.10 has one single-AMP and one multi-AMP sparse map, each covering one AMP per Node in the system configuration. However, users have the option to create additional sparse maps. These maps have the capacity to hold an unlimited number of small tables.

Sparse maps enable work on small tables to be limited to one or a few AMPs, thus avoiding the need for all-AMP steps.

Want more practical data engineering analysis like this?

Join DWHPro Letters and get field-tested notes on Teradata, Snowflake, AI, migrations, performance, and enterprise data work. Early subscribers keep launch access before the paid plan launches.

Get the next issue

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
• One physical IO must be done, even if there is no row on this AMP!

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 next issue by email.

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

By eliminating physical IO for 499 AMPs, we can decrease congestion risk. Accessing our sparse map-distributed small table might qualify as a priority or tactical workload.

Tables that are frequently accessed, remain small over time, and possess fewer rows than AMPs are ideal candidates for relocation to the sparse map.

It is advisable to relocate only tables below 128KB in size to the sparse map. In the case of a multi-AMP sparse map, the 128KB must be multiplied by the number of Nodes, considering that multi-amp sparse maps comprise one AMP per node. A system containing four nodes would entail tables of up to 512KB.

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.

https://letters.dwhpro.com/content/files/2026/05/teradata-sql-tuning-method.html

David Wellman's outstanding article explores the contrasting methods of managing small tables.

All Rows on one AMP by Design - Part 2

Planning or surviving an enterprise data platform migration?

I write regularly about the performance, cost, architecture, and project mistakes that show up in real Teradata, Snowflake, Databricks, and enterprise data work.

Subscribe before the paid plan launches and keep launch access.

Written by Roland Wenzlofsky, founder of DWHPro and author of Teradata Query Performance Tuning. DWHPro has helped data warehouse practitioners for 15+ years.