DWHPro Insights

Teradata ALTER Table vs. INSERT INTO: Which Method is Efficient?

By Roland Wenzlofsky ·

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.

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.

Teradata Query Performance Tuning: The DWHPro Guide book cover

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

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.

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/OsPhysical I/Os
INSERT BIG ROW270 85312 992
ALTER BIG ROW53915
INSERT SMALL ROW32230
ALTER SMALL ROW11314
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.

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.

Explore Teradata performance tuning

Teradata Query Performance Tuning: The DWHPro Guide book cover

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

Follow DWHPro in Google to see our articles more often in Search.

6 thoughts on “Teradata ALTER Table vs. INSERT INTO: Which Method is Efficient?”

  1. 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.

    Reply

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

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

Protected by Security by CleanTalk and CleanTalk Anti-Spam