The Teradata MAPS Feature enables hardware configuration expansion with minimal downtime by postponing the redistribution of tables from old to new AMPs. This is achievable due to coexisting multiple hash maps that cover old and new configurations. The administrator can determine when to transfer tables to the new hash map.
Traditional INSERT…SELECT
After transitioning to a bigger hash map in Teradata, it is necessary to eventually distribute the rows of a table to the proper AMPs based on the new hash map. One effective method is utilizing a conventional INSERT-SELECT statement, which transfers the rows from the source table to a new table and redistributes them to the appropriate AMPs.
The retrieval step reads the source table, redistributes its rows, and constructs a spool file. The merge step then inserts the rows into the new table. Before dropping the initial table, removing any defined join index is mandatory.
The INSERT-SELECT statement is optimized and efficient for distributing rows after upgrading to a larger hash map in Teradata. However, an even superior approach exists.
MAPS INSERT…SELECT
To transfer a table from an outdated map to a new one in Teradata, you may implement an ALTER TABLE directive specifically designed for this purpose. Include a “MAPS = name” stipulation. This directive operates in a similar manner to the conventional INSERT-SELECT statement, albeit with some distinctions.
ALTER TABLE mytable
MAP = mynewmap;
Comparing both strategies
To transfer a table between hash maps, utilize an ALTER TABLE command with the “MAP = mynewmap” clause. This approach differs from an INSERT-SELECT statement.
The table rows persist while the owning AMP of each row alters in the new hash map due to a greater number of AMPs. Note that no tables are eliminated during the relocation of a table to a new map compared to the INSERT…SELECT process.
A new internal copy of the table is created, maintaining the original table entry and its Table ID in the TVM table. The rows are then read, redistributed, and inserted into a work table that replaces the original table.
The same applies to subtables. It is well-known that subtables contain Secondary indexes. When transferring a table to a different map, a task subtable must be generated for each current subtable. Every subtable is assigned a distinctive identifier that holds significance internally. Upon reaching the END TRANSACTION phase, the initial subtables are eradicated, and the task subtables are renamed with the original subtable identifier.
Why should you use the MAPS approach?
Moving tables between maps does not involve row-level transient journaling, nor does it require creating a spool file. Additionally, minimal administrative intervention is necessary. An advantage of this method is that join indexes do not need to be dropped as they would with the INSERT…SELECT approach.
In the event of an error during the MAPS approach, the original tables will remain accessible and will be reverted to.
Hi Roland,
Thanks for this article.
As a prerequiste, one must be granted the map through GRANT MAP myNewMap TO myUser command.
A small fix, alter table syntax should be ALTER TABLE myTable, MAP = myNewMap, right ?
Thanks. I fixed it.