Modifying Primary Index of a table in Teradata

You can alter a PI in Teradata only if the table is empty. 

• First take a backup of the table data,
  

SET QUERY_BAND='BLOCKCOMPRESSION=YES;' FOR SESSION;
CREATE TABLE DBNAME.TABLENAME_BKP AS DBNAME.TABLENAME WITH DATA AND STATS;

• Empty the table and alter the primary index

DELETE FROM DBNAME.TABLENAME;
alter table DBNAME.TABLENAME modify primary index Index_Name(col1, col2, ...);

• Copy the original data back to the table and drop the backup table
  

INSERT INTO DBNAME.TABLENAME SELECT * FROM DBNAME.TABLENAME_BKP;
DROP TABLE DBNAME.TABLENAME_BKP;

nVector

posted on 14 May 18