DWHPro Insights

Improve Performance with Teradata PPI Tables: Get a Grip on Clumpy Row Distribution

By Roland Wenzlofsky ·

Improve Performance with Teradata PPI Tables: Get a Grip on Clumpy Row Distribution

This example distinguishes repeated primary-index values from duplicate complete rows. Duplicate-row checking is relevant to SET semantics; a UNIQUE primary index adds a different uniqueness constraint. Measure load costs and partition probing together rather than treating the design as a universal performance fix.

Get a Grip on Clumpy Row Distribution – the Teradata PPI Table.

For this technique to work properly, the primary index in Teradata should not include the partition column(s).

We have all encountered the issue of slow table inserts, where the distribution of rows across all AMPs is even, but the merge step takes excessive time.

One possible reason for the issue is the presence of a SET table with numerous identical NUPI values. The AMPs are required to perform a duplicate row check for every newly inserted row, resulting in significant time and CPU cycle consumption. This is due to each row being fully compared against all the other rows that share the same Primary Index value.

Switching to a MULTISET design for nonpartitioned tables can solve the problem, but we also have a great opportunity to address it with Teradata partitioned tables (PPI tables).

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

Given the table design below:

CREATE TABLE MultiLevel_Example
(  the_code INTEGER NOT NULL,
  the_date DATE  NOT NULL,

  the_value DECIMAL(18,0)
) PRIMARY INDEX (the_code)
PARTITION BY (
RANGE_N(the_date BETWEEN DATE '2015-01-01' AND DATE '2015-12-31' EACH INTERVAL '1' MONTH), RANGE_N(the_code BETWEEN 1 AND 5 EACH 1)
);

In the first example, the_date is absent from the primary index, while the_code appears in both the PI and a partition expression. A nonunique PI allows repeated code values across date partitions; a UPI on the_code alone cannot enforce uniqueness for this partitioned definition.

Here is the internal table schematic:

teradata ppi

The column labeled “C” is the combined partition, “L1” is the first level of partitioning, and “L2” is the second tier. The column labeled “H” represents the ROWHASH of “The_Code.”

Creating a PPI table without all partitioning columns in the Primary Index results in the distribution of equal NUPI values across different partitions. This reduces clumping and enhances performance as the number of rows for each duplicate row check is significantly reduced.

However, it is important to note that placing partition columns outside of the Primary Index results in a penalty. It is necessary to assess the pros and cons before making a decision.

Without a usable partition predicate, primary-index access may need to probe multiple partitions. Partition elimination can avoid that work. Evaluate join and aggregation costs from the actual plan rather than assuming every partition is always read.

Let us briefly examine why this technique is ineffective for a Teradata PPI table if all partition columns are included in the Primary Index definition:

CREATE TABLE MultiLevel_Example
( 
the_code
INTEGER NOT NULL,
the_date DATE  NOT NULL,

  the_value DECIMAL(18,0)
) UNIQUE PRIMARY INDEX (the_code,the_date)
PARTITION BY (
RANGE_N(the_date BETWEEN DATE '2015-01-01' AND DATE '2015-12-31' EACH INTERVAL '1' MONTH), RANGE_N(the_code BETWEEN 1 AND 5 EACH 1)
);

Including all partitioning columns in the PI changes how repeated PI values can occur across partitions. Distinct PI values can still hash-collide; matching row hashes do not prove identical keys.

teradata ppi

Click here for the Partitioned Primary Index Guide.

https://www.dwhpro.com/teradata-partitioned-primary-index/

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 “Improve Performance with Teradata PPI Tables: Get a Grip on Clumpy Row Distribution”

  1. While performing a huge java transaction with insertion queries in batches.Getting AMP lock table has been overflowed SQL Exception .how to solve it? Do any settings need to be changed in DBS control ?

    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