All Forums Database
SAP 73 posts Joined 08/14
21 Sep 2014
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 !!!
 
 
 
 

SAP
SAP 73 posts Joined 08/14
21 Sep 2014

small correction in above post [ at third line ]
**NO PRIMARY INDEX gives us even distribution of data in amps
Sorry for the typo :(

SAP

Raja_KT 1246 posts Joined 07/09
21 Sep 2014

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. 

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

SAP 73 posts Joined 08/14
21 Sep 2014

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.
 

SAP

dnoeth 4628 posts Joined 11/04
21 Sep 2014

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.
 

Dieter

SAP 73 posts Joined 08/14
21 Sep 2014

Thanks Dieter !!!! :) :)
 

SAP

You must sign in to leave a comment.