Creating a table that uses compression

When you create a new table by issuing the CREATE TABLE statement, you have the option to compress the data contained in table rows.

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 create a table that uses compression, issue a CREATE 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 VALUE COMPRESSION clause. If you want to compress data that represents system default column values, also include the COMPRESS SYSTEM DEFAULT clause.

Results

After you create the table, all data that you add to the table from that point in time on is compressed. Any indexes that are associated with the table are also compressed, unless you specify otherwise by using the COMPRESS NO clause of the CREATE INDEX or ALTER INDEX statements.

Examples

Example 1: The following statement creates a table for customer information with adaptive compression enabled. In this example, the table is compressed by using both table-level and page-level compression dictionaries.

     CREATE TABLE CUSTOMER
          (CUSTOMERNUM      INTEGER,
           CUSTOMERNAME     VARCHAR(80),
           ADDRESS          VARCHAR(200),
           CITY             VARCHAR(50),
           COUNTRY          VARCHAR(50),
           CODE             VARCHAR(15),
           CUSTOMERNUMDIM   INTEGER)
           COMPRESS YES ADAPTIVE;

Example 2: The following statement creates a table for customer information with classic row compression enabled. In this example, the table is compressed by using only a table-level compression dictionary.

     CREATE TABLE CUSTOMER
          (CUSTOMERNUM      INTEGER,
           CUSTOMERNAME     VARCHAR(80),
           ADDRESS          VARCHAR(200),
           CITY             VARCHAR(50),
           COUNTRY          VARCHAR(50),
           CODE             VARCHAR(15),
           CUSTOMERNUMDIM   INTEGER)
           COMPRESS YES STATIC;

Example 3: The following statement creates a table for employee salaries. The SALARY column has a default value of 0, and row compression and system default compression are specified for the column.

     CREATE TABLE EMPLOYEE_SALARY
          (DEPTNO   CHAR(3)      NOT NULL,
           DEPTNAME VARCHAR(36)  NOT NULL,
           EMPNO    CHAR(6)      NOT NULL,
           SALARY   DECIMAL(9,2) NOT NULL WITH DEFAULT COMPRESS SYSTEM DEFAULT)
           COMPRESS YES ADAPTIVE;
Note that the VALUE COMPRESSION clause was omitted from this statement. This statement creates a table that is called EMPLOYEE_SALARY; however, a warning message is returned:
SQL20140W  COMPRESS column attribute ignored because VALUE COMPRESSION is 
deactivated for the table.  SQLSTATE=01648
In this case, the COMPRESS SYSTEM DEFAULT clause is not applied to the SALARY column.

Example 4: The following statement creates a table for employee salaries. The SALARY column has a default value of 0, and row compression and system default compression are enabled for the column.

     CREATE TABLE EMPLOYEE_SALARY
          (DEPTNO   CHAR(3)      NOT NULL,
           DEPTNAME VARCHAR(36)  NOT NULL,
           EMPNO    CHAR(6)      NOT NULL,
           SALARY   DECIMAL(9,2) NOT NULL WITH DEFAULT COMPRESS SYSTEM DEFAULT)
           VALUE COMPRESSION COMPRESS YES ADAPTIVE;

In this example, the VALUE COMPRESSION clause is included in the statement, which compresses the default value for the SALARY column.