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 .
small correction in above post [ at third line ]
**NO PRIMARY INDEX gives us even distribution of data in amps
Sorry for the typo :(
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 :
see the rules and limitation.
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.
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.