Teradata ALTER Table vs. INSERT INTO: Which Method is Efficient?
Teradata ALTER Table versus INSERT INTO
Altering the structure of a substantial Teradata table can consume significant resources. Essentially, there are two approaches: altering the DDL through Teradata ALTER TABLE or generating an empty table with the desired DDL statement and transferring data via the Teradata INSERT INTO statement. Each method carries its own set of pros and cons.
We devised a test scenario to compare the methods using a table with an average data block size of 127KB. The table accommodated several hundred rows per data block, as we filled it with very short rows.
We performed an ALTER TABLE and an INSERT INTO query and found that the ALTER TABLE approach was more effective, resulting in 133 I/Os compared to the 322 I/Os of the INSERT INTO method. In Teradata, the ALTER TABLE query utilized 59% fewer I/Os than the INSERT INTO statement, with comparable CPU seconds.
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. Launch access is open. The single paid plan will be EUR 49 per year.
We altered the data demographics of the table and expanded the row width significantly, allowing for only 1-2 rows per data block. Subsequently, we executed an ALTER TABLE statement and an INSERT INTO statement.
The ALTER TABLE method demonstrated a notable advantage, as it only required 539 I/Os, in contrast to the INSERT INTO approach, which demanded 270853 I/Os. Thus, the ALTER TABLE method resulted in a remarkable 99.8% reduction of I/Os.
If you work with enterprise data platforms, migrations, performance tuning, or AI-driven delivery teams, DWHPro Letters is written for you. Get the next issue by email.
Through various comparisons with varied data blocks and row sizes, we have observed a clear trend. When there are fewer rows per data block, the ALTER TABLE method results in greater I/O savings.
Below we can see a table showing the results (logical and physical I/Os):
| Logical I/Os | Physical I/Os | |
| INSERT BIG ROW | 270 853 | 12 992 |
| ALTER BIG ROW | 539 | 15 |
| INSERT SMALL ROW | 322 | 30 |
| ALTER SMALL ROW | 113 | 14 |
Teradata INSERT INTO versus ALTER TABLE
Nonetheless, utilizing ALTER TABLE should not be a default practice since its drawbacks require thorough consideration.
The execution of ALTER TABLE cannot be halted, which can result in unforeseen circumstances. For instance, if a workload management rule is set to terminate all sessions that consume over 100,000 CPU seconds and the ALTER TABLE surpasses this threshold, the session will fail only after the ALTER TABLE operation has concluded.
The Teradata INSERT INTO statement requires only a read lock, while ALTER TABLE results in an exclusive lock on the table. An extended ALTER TABLE process can significantly impede other workloads. Additionally, modifying the original table poses potential risks. On the other hand, the INSERT INTO statement operates on a duplicate table, mitigating the level of risk involved.
ALTER TABLE adds new columns at the end of the table. If we need it in a specific position, we have to use the INSERT INTO statement.
Should we use Teradata INSERT INTO or ALTER TABLE?
From a performance standpoint, ALTER TABLE is superior and should be prioritized when the aforementioned restrictions are acceptable. However, if these limitations are not feasible, exploring alternative, optimized forms of the Teradata INSERT INTO statement is advisable.
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 before the paid plan launches 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.