Moving Tables to a New Map vs. Traditional INSERT-SELECT

The best minds from Teradata, our partners, and customers blog about whatever takes their fancy.
Teradata Employee

With Teradata MAPS Feature, you can expand the hardware configuration and choose to postpone the redistribution of tables from the old AMPs to the new AMPs. This delay in moving rows to the new AMPs can provide a significate reduction in down time during the expansion window. Postponing table redistributions is enabled by allowing multiple hash maps (the old hash map that covered the previous configuration and the new hash map for all of the AMPs in the new configuration) to co-exist at the same time. It will be up to the administrator to decide when to move tables into the new, larger map.


The move of a table from the old map to the new map is accomplished by a new type of ALTER TABLE command.   When an ALTER TABLE statement that includes a “MAPS = map_name” clause is issued, that ALTER TABLE will be processed similar to an INSERT-SELECT.


The INSERT-SELECT moves tables from one map to a different map is essentially doing the same thing as the standard INSERT-SELECT. However, there are a few differences, which will be highlighted in this posting.


For background on MAPS, go to YouTube and search on “Teradata MAPS”.



The standard INSERT-SELECT has many useful purposes, including the copy of a table’s rows into an empty table, followed by a drop of the original table. When the INSERT-SELECT is executed, there is a Retrieve step that reads the source table, redistributes rows to the correct AMPs, and builds up a spool file of all the redistributed rows.  The Retrieve step is followed by a Merge step that reads the spool file and inserts the rows into the new table.  All during the INSERT-SELECT rows from the source table can be read, allowing data to be available during most of the process. 


During the Merge step when the spool data is being inserted into the base table fallback is built. If there are any secondary indexes defined on the source table, they will be created during the Merge step as well.   


The INSERT-SELECT statement has been highly optimized over the years. It is easy to use, high performing, and all new Teradata Database features and enhancements have been architected to work well with the INSERT-SELECT.  


What’s Different with INSERT-SELECT and MAPS

When you move a table from one map to another map, the move is accomplished by the same Retrieve and Merge steps. But instead of moving the contents of one table into an entirely different table as you would do with the traditional INSERT-SELECT, with MAPS, only a single table is involved.  It simply undergoes a change in how its rows are redistributed.












When moving a table to a different map, all the values contained in a row, including identity column values, remain the same. The hash ID and row ID of each row in the table remains the same as well.  The only thing that changes when a table moves to a different map is the AMP that owns the row.  The new hash map will re-assign rows to AMPs differently, because the new hash map has a greater of number of AMPs to intersect with the system’s hash buckets.












Another difference with how an INSERT-SELECT behaves with maps is that no tables are dropped. It would be easy to assume that the rows that existed in table when it was in the old map will be dropped from the old version of the table and re-inserted into a new version of the table in the new map.  That is not the case.


When a table is dropped there must be an explicit DROP TABLE command. If the table has no dependent objects (such as a join index) the table is dropped and removed from the DBC.TVM table. If the table is recreated at a later time it will receive a new table ID and a new row in TVM.  


When tables are moved to a new map, another image of the table is created internally, but the original table entry, and its Table ID, remains in the TVM table. Only the version number and the map number are updated in TVM when moving a table to a new map.  The table itself is never dropped.  So if there is a join index defined on the table, the join index will not need to be dropped either, before moving a table to a new map.


Work Tables and Subtables

One major difference between moving a table to a different map and the traditional INSERT-SELECT is the absence of a spool file when moving a table to a new map. Rows are read and instead of being spooled, they are redistributed and inserted into a “work table” which is spread across all the AMPs in the new map.  This work table will become the new image of the table at the end of the process.


Primary data, as well as fallback copies of data and indexes, are held in what are referred to as subtables. These subtables exist before and after moving a table to a new map. Each base table will have a collection of one or more subtables. The more attributes to the base table, the more subtables will exist to support those attributes. 


Each subtable has an assigned number, which has a meaning internally. For example, primary data rows are always held in a subtable with the number 1024.  Fallback data is usually held in subtable 2048.  Secondary indexes use other subtable numbers. 


In the process of moving a table to a different map, there will be a second subtable created for each existing subtable. To differentiate the new subtable, the new subtable’s number will take the associated subtable and increment it by 1.  For example, the primary data for the data in the old map uses subtable 1024.  The new work table for primary data will be labeled subtable 1025.  The new subtable for fallback will be subtable 2049, incremented upwards from the original fallback subtable of 2048.


Each secondary index row will have its entries inserted into a work table by means of this subtable numbering scheme. Secondary indexes are not dropped in the traditional sense, but new entries are made into the work tables and the secondary index subtable is built up as the Merge step proceeds.


At the End Transaction step, the standard subtables (1024 for primary data, 2048 for fallback, etc.) are deleted and the new subtables are renamed to original subtable number. For example, the primary data work table for the new map is named subtable 1025 during the INSERT-SELECT process, but will be renamed 1024 after the old subtable with that name has been eliminated.  


In this manner, the work tables are immediately and simply converted into the base tables and index subtables. During the INSERT-SELECT process these work tables will show up as perm space in space accounting routines.  Spool files, used in the traditional INSERT-SELECT will not show up as perm space and do not support conversion into base tables rows.


In addition, there is no row-level transient journaling involved in the movement of tables to different maps. If the job fails, the original image of the data contained in the original subtables still remains and will be usable.


Minimal intervention is required by the administrator when moving a table to a new map.   Essentially, all the rows and indexes and fallback data is redistributed, by means of work tables, but values within each row do not change.