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, consider the following concepts.
- Contiguous map
- This type of map includes all AMPs within a specified range. By default, every system has one contiguous map that includes every AMP in the system. Vantage creates contiguous maps during a system initialization, configuration, or reconfiguration.
- Sparse map
- This type of map includes a subset of AMPs from a contiguous map. By default, each system that enables MAPS Architecture has a 1-AMP sparse map and an n-AMP sparse map, where n is the number of nodes in the system. You can also create new sparse maps, if you have appropriate database privileges.
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 only one row on a 1,000 AMP system. A request requiring a full-table scan requires 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 defined on the system, query the DBC.MapsV or DBC.MapsVX views. See MapsV[X].
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 do 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.