DWHPro Insights

Efficiently Adding New Partitions to a Row Partitioned Teradata Table: A Comparison of Alter Table vs Insert Select

By Roland Wenzlofsky ·

Efficiently Adding New Partitions to a Row Partitioned Teradata Table: A Comparison of Alter Table vs Insert Select

This article explores the various methods for adding partitions to a Row Partitioned Table and determines the most efficient approach. Previously, we conducted a similar study to analyze the resource impact of modifying the structure of an unpartitioned table. During that study, we evaluated the effectiveness of INSERT…SELECT versus ALTER TABLE. For more information, please refer to our previous report.

Partitioning can be accomplished in two ways: by duplicating data into a new table or by utilizing an ALTER TABLE statement. Before proceeding, assessing the necessary resources for both methods is important. To do this, we will generate a test table with row partitions and populate it with 100,000 records.

As is customary, I will substantiate my assertions with tests. The test configuration employs a table comprising approximately 100,000 rows.

The date column in our test table spans from 1900-01-01 to 2022-12-31, resulting in an empty NO RANGE partition.

CREATE MULTISET TABLE PPI_Test
(
PK INTEGER NOT NULL,
COL_A DECIMAL(18,2),
TheDate DATE FORMAT 'YYYY-MM-DD'
) PRIMARY INDEX (PK) PARTITION BY (RANGE_N(TheDate BETWEEN DATE'1900-01-01' AND DATE'2022-12-31' EACH INTERVAL '1' DAY, NO RANGE));

We add daily partitions for the year 2023 using an ALTER TABLE statement:

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
ALTER TABLE PPI_Test MODIFY PRIMARY INDEX ADD RANGE BETWEEN DATE '2023-01-01' AND DATE '2023-12-31' EACH INTERVAL '1' DAY;

Creating new partitions is a streamlined process as they do not require any row redistribution. The sole exception would be if there were an abundance of rows in the NO RANGE partition that would need to be redistributed to the newly created day partitions. However, this is not applicable in this scenario. As a result, the ALTER TABLE command incurs only 110 IOs and necessitates no spool space.

Now, let’s tackle the identical task with INSERT…SELECT statements:

CREATE MULTISET TABLE PPI_Test_Copy
(
PK INTEGER NOT NULL,
COL_A DECIMAL(18,2),
TheDate DATE FORMAT 'YYYY-MM-DD'
) PRIMARY INDEX (PK) PARTITION BY (RANGE_N(TheDate BETWEEN DATE'1900-01-01' AND DATE'2023-12-31' EACH INTERVAL '1' DAY, NO RANGE));
INSERT INTO PPI_Test_Copy 
SELECT * FROM PPI_Test;

The historical INSERT SELECT figure conflicts between prose (228 logical I/Os) and the table (248). The original benchmark is not reproducible from the available evidence; retain these as conflicting historical observations, not a measured current comparison.

What happens if rows need to be redistributed from the NO RANGE partition to new partitions? Before adding new partitions for 2023, we inserted numerous rows into our table with a date between January 1st and December 31st of 2023 for testing purposes. The same issue arises when a DROP RANGE is performed, requiring rows to be moved to the NO RANGE partition.

A rise from 110 to 952 is a 765.45% increase, or 865.45% of the baseline. These are historical logical-I/O observations, not results from the current fixture.

Here are the summarized results of our 3 test setups:

 Method of adding partitions to PPI tableLogical IOs
ALTER TABLE without NO RANGE rows110
ALTER TABLE with NO RANGE rows952
INSERT INTO … SELECT into table copy248

Summary

The ALTER TABLE method is generally more efficient. Still, it’s important to note that the number of IOs can significantly increase if there is a pre-populated NO RANGE partition and many rows need to be moved.

ALTER can take an exclusive lock on the affected table and block dependent work; it does not necessarily stop the whole system. Moving NO RANGE rows into new partitions normally reorganizes rows within their AMPs when the PI/map is unchanged.

It is not recommended to rely solely on ALTER TABLE as the most efficient method without considering various factors affecting resource usage. Prior to repartitioning a significant table, it is advisable to conduct comprehensive testing.

The table definition and ADD RANGE executed on a small fixture containing 2022 and 2023 rows. This validates syntax and row handling, not the historical I/O comparison.

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.

1 thought on “Efficiently Adding New Partitions to a Row Partitioned Teradata Table: A Comparison of Alter Table vs Insert Select”

  1. Thanks Roland.
    Is there anything like automatic partition roll out in Teradata/latest versions of Teradata?
    I mean, the partitioning extends by itself for upcoming Years/months/dates etc?

    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