Tactical Query Optimization with Sparse Maps in Teradata

Roland Wenzlofsky

October 22, 2017

minutes reading time

Sparse Maps are a new feature that comes with Teradata 16.10, which allows us to place all rows of small tables on one AMP or one AMP per Node. These new maps are defined within the hash maps (their parent maps) and used to distribute table rows by primary index value across all AMPs.

Teradata 16.10 automatically includes one single-AMP sparse map and one multi-AMP sparse map covering one AMP per Node in the system configuration. But of course, you can define additional ones. Each sparse map can contain an unlimited number of small tables.

Sp sparse maps for small tables limit work to one or a few AMPs, avoiding all-AMP steps.

Assume a 500-AMP system. Even if our small table only contains three rows, each AMP has to do the following tasks to check for existing rows:

  • 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!

While this doesn’t sound too dramatic, remember that the system could be congested, and an AMP not holding any row might be in flow control. This would delay the completion of the access step.

Using the new sparse maps feature of Teradata 16.10, all-AMP steps can easily be changed into single-AMP steps for small tables. In our 500 AMP system, 499 AMPs would now not need to get an AMP worker task.

Furthermore, we eliminate the physical IO for 499 AMPs and reduce congestion risk. If we access our small table distributed by the sparse map, it might even qualify to be handled as a priority (tactical) workload.

Typical candidates to be moved to the sparse map are tables with fewer rows than AMPS and are accessed frequently and stay small over time.

Moving only tables to the sparse map, less than 128KB in size is recommended. For a multi-AMP sparse map, we would multiply the 128KB by the number of Nodes (remember, multi-amp sparse maps contain one AMP per node). On a four-node system, this would be tables up to 4+128KB = 512KB.

Please be careful when looking for suitable candidates for the sparse map. Empty tables seem to be a great choice to move, but you have to ensure that they stay empty and are not just stage tables, which will be repopulated with millions or billions of rows in your following batch load process.

Sparse maps might be helpful to replace some existing techniques described here:

A great article by David Wellman compares the different ways of handling small tables in this article:

All Rows on one AMP by Design – Part 2

  • Avatar
    Boris Mogilevsky says:

    How the Sparse Map feature is being enabled or is it all internal based on the size of the table like you are mentioning.


  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    You might also like