Every table uses a map that specifies which AMPs store the rows of the table. For tables with fallback, the primary and fallback copies of the row are stored on different AMPs in the map.
You can use maps to optimize the placement of tables on the AMPs in your system or to redistribute table rows after a system expansion. Before doing this, it is important to consider some general concepts.
- Contiguous maps include all AMPs within a specified range. By default, every system has one contiguous map that includes every AMP in the system. Teradata Database creates contiguous maps during a system initialization, configuration, or reconfiguration.
- Sparse maps are smaller maps that contain only a limited number of AMPs. By default, each system that enables Teradata Database MAPS Architecture has a 1-AMP sparse map and an n-AMP sparse map, where n is the number of nodes in the system.
Secondary index tables use the same map as their base (indexed) table. Tables and join indexes are assigned a map either explicitly or by default.
Sparse maps are efficient for small tables. For example, consider a table with just one row on a 1,000 AMP system. A request requiring a full-table scan would require all 1,000 AMPs in a contiguous map to read their rows. Because the table has only one row, 999 AMPs use resources to determine and report that they have no rows. However, if the one-row table uses a one-AMP sparse map, Teradata knows which AMP to read and can respond faster to a request on that table.
To determine which maps are currently defined on the system, query the DBC.MapsV or DBC.MapsVX views. For more information on Data Dictionary views, see Data Dictionary.
Sparse Maps and Table Colocation
Rows from tables that have the same PI or PA and that share the same contiguous map are distributed to the same AMPs. This is called table colocation. Colocation provides a performance advantage when tables are joined on their PI or PA columns, because the join processing for corresponding rows happens within the same AMP.
For tables using sparse maps, there is an additional requirement for joins to take advantage of colocation.
Because tables using the same sparse map may not be stored on the same subset of AMPs, even if these tables have the same PI or PA, their corresponding rows would not benefit from colocation during joins involving the PI or PA columns. You can force rows of frequently joined tables to be distributed to the same subset of AMPs by specifying a colocation name when you associate the tables with the sparse map. The colocation name forces tables that use the same sparse map to be stored on the same subset of AMPs.