NO PRIMARY INDEX table in Teradata

Database
SAP
Enthusiast

NO PRIMARY INDEX table in Teradata

Hi ,

I have a list of 200 stage tables [ truncate and load ] with no primary key defined in SQL Server . Now i need to migrate them to teradata . In that case , shall i go with NO PRIMARY INDEX option or do i need to find its Target table UPI / PI and define the same to Stage as well ? 

Because i remember , NO PRIMARY INDEX gives us even distribution of data in table and but i am not sure about retrieval ?

Please suggest best way to find Primary index for stage tables .

Thanks !!!

5 REPLIES
SAP
Enthusiast

Re: NO PRIMARY INDEX table in Teradata

small correction in above post [ at third line ]

**NO PRIMARY INDEX gives us even distribution of data in amps

Sorry for the typo :(

Enthusiast

Re: NO PRIMARY INDEX table in Teradata

You can use NOPI  for fastload and tpump or corresponding tpt. It will improve performance. you can do dml operations on NOPI table, excepting merge.

You can refer to this link :

http://developer.teradata.com/database/articles/say-yes-to-no-primary-index-no-pi-tables

see the rules and limitation. 

SAP
Enthusiast

Re: NO PRIMARY INDEX table in Teradata

Thanks Raja !!!!

In the link yu provided , some of the queries are unanswered . Can u help me on those like :-

1 . Which is faster :- TPT Update/LOAD loading into Table with PI or TPT Update/LOAD loading into Table with NoPI ?

2.  And how about the Selecting records from NoPI tables into Tables with PI vs Selecting records from PI tables into Tables with PI.

Senior Apprentice

Re: NO PRIMARY INDEX table in Teradata

LOAD in a NoPI table is approx. 30-40% faster.

Insert/Select from NoPI to PI is slower than tables with matching PIs because there must be a redistribution/sort instead of a direct merge.

Rule of thumb:

If you can't create the staging table with the same PI as the target table (e.g. VarChar in staging but INT in target) you should use NoPI as you need to redistribute/sort anyway.

If target and staging might have the same PI there's hardly any difference. NoPi will simply move the redistribution/sort from LOAD to the Insert/Select.

SAP
Enthusiast

Re: NO PRIMARY INDEX table in Teradata

Thanks Dieter !!!! :) :)