UPI [Unique primary index] in TeraData

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

Case-N

Based on ’the condition’ we create partitions

Range-N

Based on Range it creates partitions

Note:

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

Creation and dropping

PI Created only at table create

UPI

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

NUPI

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

PPI

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

UPI

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);

PPI

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);

Note:

Works only with NUPI values

Teradata Interview Questions

Range-N

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)

Note:

• Works only with UPI Values
• MPPI

Note:

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

Advantages

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: