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.
Part of the Teradata Physical Design and Data Modelling guide.
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).
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 bookGiven 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:

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.

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