14 May 18 · npack · #teradata ·   Bookmark   ×

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;

npack

posted on 14 May 18

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds

Copied