Enabling compression in an existing table
By using the ALTER TABLE statement, you can modify an existing table to take advantage of the storage-saving benefits of compression.
Before you begin
You must decide which type of compression you want to use: adaptive compression, classic row compression, value compression, or a combination of value compression with either of the two types of row compression. Adaptive compression and classic row compression almost always save storage because they attempt to replace data patterns that span multiple columns with shorter symbol strings. Value compression can offer savings if you have many rows with columns that contain the same value, such as a city or country name, or if you have columns that contain the default value for the data type of the column.
Procedure
To enable compression in an existing table:
Examples
Example 1: The following statement applies adaptive compression to an existing table that is named CUSTOMER:
ALTER TABLE CUSTOMER COMPRESS YES ADAPTIVE
Example 2: The following statement applies classic row compression to an existing table that is named CUSTOMER:
ALTER TABLE CUSTOMER COMPRESS YES STATIC
Example 3: The following statements apply row, value, and system default compression to the SALARY column of an existing table that is named EMPLOYEE_SALARY. The table is then reorganized.
ALTER TABLE EMPLOYEE_SALARY
ALTER SALARY COMPRESS SYSTEM DEFAULT
COMPRESS YES ACTIVATE VALUE COMPRESSION;
REORG TABLE EMPLOYEE_SALARY