COMPRESSED option for tables

Use the COMPRESSED option of the CREATE TABLE statement to enable the automatic compression of large amounts of row data when the data is loaded into the table or table fragment.

After you create a table with the COMPRESSED option, the database server automatically creates a compression dictionary and compresses the in-row data after 2000 or more rows of data are loaded into a table or fragment. If the data is loaded by a light append, the first 2000 rows and all subsequent rows are compressed. If the data is loaded by another method, all subsequent rows after the first 2000 rows are compressed. To compress the original 2000 rows, run the SQL administration API task() or admin() function with the table compress or fragment compress argument.

The COMPRESSED option enables only the compression of in-row data. The COMPRESSED option does not enable the automatic compression of simple large objects in dbspaces or indexes. (You can use the COMPRESSED keyword of the CREATE INDEX statement to create a compressed B-tree index.)

The following example creates a table that is set up for the automatic compression:

CREATE TABLE cust5 (  ...) COMPRESSED;

The following example also creates a table named t, which defines first and next extent sizes and is set up for the automatic compression:

CREATE TABLE t(c int, d int) EXTENT SIZE 32 NEXT SIZE 32 COMPRESSED;

To disable the automatic compression of data in new rows, run an SQL administration API task() or admin() function with the table uncompress argument. To re-enable automatic compression, run the SQL administration API task() or admin() function with the table compress argument on the table. You can control compression for a table fragment with the fragment uncompress and fragment compress arguments.