UPI [Unique primary index] in TeraData

Blog Author


Published Date

13th October, 2020




  • Share this blog :


UPI[Unique primary index]

  • The unique primary index takes only unique values and one null value
  • It distributes data across all Amp’s uniformly(or) randomly(or) evenly, so this distribution is called uniform distribution(or) event distribution
  • *One Amp operation operates only one row.

Nupi[Non- unique primary index]

  • It takes duplicates and nulls
  • It’s distribution data across Amp’s proportional to the of uniqueness that means, it distributes less even distribution (or) unable distribution[duplicate row going to the sam Amp]
  • This distribution can also be called as Skewed distortion and the less uniform less is “Skewness”
  • *One Amp operation and affects many rows

PPI[Portioned primary Index]


Inclined to build a profession as Teradata Developer? Then here is the blog post on, explore Teradata Training

NPPI[Non- partitioned primary Index]


Partitioned Primary Index on P code[PPI]


  • Data partitioned based on pcode first
  • Within the partitioned data stored on row hash sequence.
  • We go for PPI generally when we using other then PI Columns use in the where clause along with PI frequency
  • If other column range based column query than also it’s recommended
  • In the above select query, the system goes to the corresponding partitioned and skips the other partition so improves the performance.
  • Generally, it is single Amp for some time, multi Amp operations also


Based on ’the condition’ we create partitions


Based on Range it creates partitions


no case:- Condition Unmatched data. Move into the no case partition

Creation and dropping

PI Created only at table create


Create table<table name>(<columns>) Unique primary index(column names);


Create table<Table name>(< columns >) primary index(column names);


Create table<table name>(<columns>)primary index(columns) partition by column name;


Create set table test1


Party Id integer;

Party name varchar(30))

Unique primary index(party id);

Create a set table test


Party id integer,

Party name varchar(30))

Primary index(Party id);


Create set table test1


Party id integer,

Party name varchar(30),

Party code integer)

Primary index(Party id) partition by case- N

Party code=10, party code=20, no case, unknown);


Works only with NUPI values

Teradata Interview Questions


Based on the range of data as part of partitions

We go for it

Create set table test1


Party id integer

Party name varchar(30),

Party code integer,

Jdate date) Unique primary index

(Party ID, J date)partition by range – N(J date between

‘2010-01-01’And’2010-10-01’ Each interval’1’ month)


  • Works only with UPI Values
  • MPPI


  • Each level must have 2 partitions
  • Max 15 Levels supports
  • Total partitions 65,535
  • NOPI(only in Teradata 13)


We can take as stage tables to load data from the fast load(or) TPUMP Array insert

Create multiset table let(party id integer, party name, varchar(30))

No Primary index:

Primary Index

For In-depth knowledge on Teradata click on:

About Author


Author Bio

TekSlate is the best online training provider in delivering world-class IT skills to individuals and corporates from all parts of the globe. We are proven experts in accumulating every need of an IT skills upgrade aspirant and have delivered excellent services. We aim to bring you all the essentials to learn and master new technologies in the market with our articles, blogs, and videos. Build your career success with us, enhancing most in-demand skills .

Related Blogs

Write For Us