Thanks for the reply. However this will work only when the table is empty. But I do not have that liberty. Is there any way where we can add more compression values to a table which contains data, without dropping the table?
Alter does not support compression on existing tables.
In such a case, there is no other option but to add a new column with compress attribute.Update all the values in old column to new column then remove old column and rename new column name to old column name.
Workaround: When table needs to be altered, instead of altering the table create a new table and then perform an insert/select from the original table to a new table with the desired attributes in place of the ALTER TABLE operation.