• USA : +1 973 910 5725
  • INDIA: +91 905 291 3388
  • info@tekslate.com
  • Login

UPI [Unique primary index] in TeraData

UPI[Unique primary index]

Unique primary index takes only unique values and one null value

It distribute a data across all Amp’s uniformly(or) randomly(or) evenly, so this distribution is called uniform distribution(or) event distribution

*One Amp operation operate 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 effects many rows

PPI[Portioned primary Index]

Screenshot_61

Desired to gain proficiency on Teradata? Explore the blog post on Online Teradata Training 
to become a pro in Teradata.

NPPI[Non- partitioned primary Index]

Screenshot_62

Partitioned primary Index on P code[PPI]

Screenshot_63

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 Un- matched data. Move in to 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 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

Learn more about Teradata Interview Questions in this blog post.

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 fast load(or) TPUMP Array insert

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

No Primary index:

Screenshot_64

For Indepth knowledge on Teradata click on:

Summary
Review Date
Reviewed Item
UPI [Unique primary index] in TeraData
Author Rating
5

“At TekSlate, we are trying to create high quality tutorials and articles, if you think any information is incorrect or want to add anything to the article, please feel free to get in touch with us at info@tekslate.com, we will update the article in 24 hours.”

0 Responses on UPI [Unique primary index] in TeraData"

    Leave a Message

    Your email address will not be published. Required fields are marked *

    Site Disclaimer, Copyright © 2016 - All Rights Reserved.

    Support


    Please leave a message and we'll get back to you soon.

    I agree to be contacted via e-mail.