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:

  1. Issue the ALTER TABLE statement.
    • If you want to use adaptive compression, include the COMPRESS YES ADAPTIVE clause.
    • If you want to use classic row compression, include the COMPRESS YES STATIC clause.
    • If you want to use value compression, include the ACTIVATE VALUE COMPRESSION clause for each column that contains a value you want compressed. If you want to compress data in columns that contain system default values, also include the COMPRESS SYSTEM DEFAULT clause.
    All rows that you subsequently append, insert, load, or update use the new compressed format.
  2. Optional: To immediately apply compression to all the existing rows of a table, perform a table reorganization by using the REORG TABLE command. If you do not apply compression to all rows at this point, uncompressed rows will not be stored in the new compressed format until the next time that you update them, or the next time the REORG TABLE command runs.

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