14 May 2018        Add to Favorites   Report

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;

Read great educational content like this and a lot more ! Create my free account now 🎁

Copied