Dropping PI from a table

General
Enthusiast

Dropping PI from a table

Hi,

Is it possible to drop PI from an empty table in Teradata. Then load data into it and then recreate the PI to make the data load faster ?

Please advise.

Thanks,

Raj 

Tags (1)
3 REPLIES
Enthusiast

Re: Dropping PI from a table

It is possible to drop PI on empty table in teradata, however you cannot alter a populated NoPI table to have a primary index. 

Instead you could create a NOPI staging table load the data from external source then select insert into the main table with PI.

Enthusiast

Re: Dropping PI from a table

If I drop PI from an empty table then does it automatically become a NoPI table ? I mean is that possible even without having to specify the "No Primary Index" clause ?

Thanks.

Teradata Employee

Re: Dropping PI from a table

In the original post it sounds as if there is an impression that adding the PI after the data is loaded would improve load performance or perhaps that the index build would be cheaper if done after the load was finished.  If something like this is the impression and therefore the goal is to build the index after the load - then the impression is incorrect. The PI is a fundamental structure of the table It cannot be built independently from loading the data into the table. It defines how the data is to be loaded into the table including both how the data is distributed to the AMPs and how the data is ordered in the table. Having the UPI, NUPI or NOPI defined on the table is fundamental to how the data is loaded. Because of this, it is not possible to change/add a PI because that would require completely reorganizing the data in the table. Instead, to change the organization of the data requires that a new table be defined with the desired PI and then perform an INSERT SELECT to put the data in the table with a very different organization. If the data has a good PI and it is desired to have the data stored in a PI table, then the PI should be defined before the data is loaded.