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.
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.
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, have a small size over time and possess fewer rows than AMPS are ideal candidates for relocation to the sparse map.
It is advisable to relocate exclusively tables to the sparse map that are below 128KB in size. 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.
David Wellman’s outstanding article explores the contrasting methods of managing small tables.
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.