Maximizing Efficiency: How Teradata MAPS Feature Minimizes Downtime and Simplifies Table Distribution
Teradata's MAPS Feature allows for hardware expansion with minimal downtime by delaying table redistribution. Learn how to use MAPS INSERT. SELECT here.
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.
Want more practical data engineering analysis like this?
Join DWHPro Letters and get field-tested notes on Teradata, Snowflake, AI, migrations, performance, and enterprise data work. DWHPro Letters is free. Subscribe to get new issues by email.
The INSERT-SELECT statement is optimized and efficient for distributing rows after upgrading to a larger hash map in Teradata. However, an even better approach exists.
Get the next issue by email.
MAPS INSERT...SELECT
To transfer a table from an outdated map to a new one in Teradata, you can use an ALTER TABLE statement specifically designed for this purpose. Include a MAP = name clause. This statement operates similarly to the conventional INSERT-SELECT statement, but with some distinctions.
ALTER TABLE mytable
MAP = mynewmap;Comparing both strategies
To transfer a table between hash maps, use 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 changes 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, unlike with 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 deleted, 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.
Planning or surviving an enterprise data platform migration?
I write regularly about the performance, cost, architecture, and project mistakes that show up in real Teradata, Snowflake, Databricks, and enterprise data work.
Subscribe for free and keep launch access.
Written by Roland Wenzlofsky, founder of DWHPro and author of Teradata Query Performance Tuning. DWHPro has helped data warehouse practitioners for 15+ years.