Teradata ALTER PPI Performance

0
1237
Teradata Alter PPI

Teradata Alter PPIIn our earlier article “ALTER TABLE or INSERT INTO…SELECT?” we carved out the differences between two methods of adding or dropping columns:

  1. ALTER TABLE
  2. INSERT INTO…SELECT (into a copy of the changed table)

In this article, we want to shed light on the question how adding partitions to a row partitioned table impacts performance.

As usual, I will underline my claims with some tests. The test setup uses a table containing about 100 000 rows.

We will start with a situation where no rows are available in the “NO RANGE” partition because column “TheDate” (see the DDL below) has only dates between ‘1900-01-01’ and ‘2015-12-31’:

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’2015-12-31′ EACH INTERVAL ‘1’ DAY, NO RANGE));

At first, we use the ALTER TABLE approach to adding daily partitions for the year 2016:

ALTER TABLE PPI_Test MODIFY PRIMARY INDEX ADD RANGE BETWEEN DATE ‘2016-01-01’ AND DATE ‘2016-12-31’ EACH INTERVAL ‘1’ DAY;

Adding new partitions to a table is fast and inexpensive if no rows are moved from the “NO RANGE” partition. It only requires 110 logical IOs and no spool space.

Next, we are repeating the same task with the INSERT INTO…SELECT approach:

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’2016-12-31′ EACH INTERVAL ‘1’ DAY, NO RANGE));

INSERT INTO PPI_Test_Copy SELECT * FROM PPI_Test;

The INSERT INTO…SELECT approach consumes 225% more IOs, namely 248, and needs to spool the table PPI_Test.

These are no new insights for our readers who were reading our previous post on this topic. But now let’s add more complexity to our example:

What happens if the “NO RANGE” partition is populated and rows have to be moved from “NO RANGE” to the newly added partition(s)?

Here it becomes interesting. Let’s assume the “NO RANGE” partitions has already a lot of rows from the year 2016.

By adding the daily partitions to the table PPI_Test with an ALTER TABLE statement, the “NO RANGE” partition has to be scanned.

Rows from the year 2016 are moved to their matching daily partitions (similar, for a DROP RANGE, rows would be moved from the “DROPPED” to the “NO RANGE” partition).

In our test setup, logical IOs increased to 952, which is 865% more IOs compared with the test setup with an empty “NO RANGE” partition! Furthermore, this process consumes some spool space (but usually not as much as the INSERT INTO…SELECT approach).

Here again 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

Conclusion:

As in the case of adding or dropping columns to/from a table, ALTER table in most cases will be the cheaper method. Costs (IOs and spool space) shoot up drastically if many rows from the “NO RANGE” partition have to be moved to their target partitions.

That’s what you have to keep in mind before executing ALTER TABLE statements (don’t forget: as soon as ALTER TABLE is running it can’t be aborted and holds an exclusive table lock until it is finished).

Our Reader Score
[Total: 6    Average: 4.7/5]
Teradata ALTER PPI Performance written by Roland Wenzlofsky on September 27, 2015 average rating 4.7/5 - 6 user ratings

LEAVE A REPLY

Please enter your comment!
Please enter your name here