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.
The historical small-row results are internally inconsistent: prose recorded 133 logical I/Os while the table recorded 113. Without the original operation, DDL and generator, neither figure nor a precise saving can be confirmed.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free bookWe 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.
The historical comparison cannot establish a general preference for ALTER TABLE. Its complete workload was not published or reproduced; measure the exact schema change, row width and target workload before choosing a method.
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 |
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.
INSERT SELECT requires source read locks and target write locks; ALTER locking and interruption behavior depend on the exact operation and release. Consider blocking, recovery and dependencies for both strategies.
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?
Need help with query performance or platform costs?
Diagnose plans, skew, spool pressure and expensive workloads. Agree a baseline and acceptance criteria before making changes.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free book
Can we do an alter/modify the primary index of the Teradata table with ALTER TABLE command?
No. The Primary Index can’t be changed with an ALTER TABLE.
Very Nice and precise !!!
Hi.
Another thing to keep in mind is the GRANTs on the table. If you issue a CREATE TABLE + INSERT…SELECT + RENAME TABLE you must be sure that all the GRANTs on the original table are replicated to the new one.
Cheers.
Carlos.
Thank you very much!
Wonderful article. learned a new concept today.