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.
Part of the Teradata Physical Design and Data Modelling guide.
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:
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 bookALTER 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 table | Logical IOs |
| ALTER TABLE without NO RANGE rows | 110 |
| ALTER TABLE with NO RANGE rows | 952 |
| INSERT INTO … SELECT into table copy | 248 |
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.
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
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?