Adaptive compression
Adaptive compression improves upon the compression rates that can be achieved using classic row compression by itself. Adaptive compression incorporates classic row compression; however, it also works on a page-by-page basis to further compress data. Of the various data compression techniques in the Db2® product, adaptive compression offers the most dramatic possibilities for storage savings.
How adaptive compression works
Adaptive compression actually uses two compression approaches. The first employs the same table-level compression dictionary used in classic row compression to compress data based on repetition within a sampling of data from the table as a whole. The second approach uses a page-level dictionary-based compression algorithm to compress data based on data repetition within each page of data. The dictionaries map repeated byte patterns to much smaller symbols; these symbols then replace the longer byte patterns in the table. The table-level compression dictionary is stored within the table object for which it is created, and is used to compress data throughout the table. The page-level compression dictionary is stored with the data in the data page, and is used to compression only the data within that page. For more information about the role each of these dictionaries in compressing data, see Compression dictionaries.
Data that is eligible for compression
Data that is stored within data rows, including inlined LOB or XML values, can be compressed with both adaptive and classic row compression. XML storage objects can be compressed using static compression. However storage objects for long data objects that are stored outside table rows is not compressed. In addition, though log records themselves are not compressed, the amount of log data written as a result of insert, update or delete operations is reduced by virtue of the rows being compressed.
Turning adaptive compression on or off
COMPRESS
attribute of the table to
YES
. You can set this attribute for a new table when running a CREATE
TABLE statement, or for an existing table when running an ALTER TABLE
statement. After you enable compression, operations that add data to the table, such as an
INSERT, LOAD INSERT, or IMPORT INSERT
command operation, can use adaptive compression. In addition, index compression is enabled for new
indexes on the table. Indexes are created as compressed indexes unless you specify otherwise and if
they are the types of indexes that can be compressed.To disable compression for a table, use the ALTER TABLE statement with the COMPRESS NO option; rows that you later add are not compressed. Existing rows remain compressed. To extract the entire table after you turn off compression, you must perform a table reorganization with the REORG TABLE command.
Effects of update activity on logs and compressed tables
Depending upon update activity and the position of updates in a data row, log usage might increase. For information about the impact that the order of columns in a table has on update logging, see Ordering columns to minimize update logging.
If a row increases in size after adding new data to it, the new version of the row might not fit on the current data page. Rather, the new image of the row is stored on an overflow page. To minimize the creation of pointer-overflow records, increase the percentage of each page that is to be left as free space after a reorganization by using the ALTER TABLE statement with the PCTFREE option. For example, if you set the PCTFREE option to 5% before you enabled compression, you might change it to 10% when you enable compression. Increasing the percentage of each page to be left as free space is especially important for data that is heavily updated.
Compression for temporary tables
Compression for temporary tables is enabled automatically if the database manager deems it valuable. You cannot enable or disable compression for temporary tables. Only classic row compression is used for temporary tables.
- System temporary tables
- When executing queries, the Db2 optimizer considers the storage savings and the impact on query performance that compression of system-created temporary tables offers to determine whether it is worthwhile to use compression. If it is worthwhile, classic row compression is used automatically. The minimum size that a table must be before compression is used is larger for temporary tables than for regular tables.
- User-created temporary tables
- Created global temporary tables (CGTTs) and declared global temporary tables (DGTTs) are always compressed using classic row compression.
You can use the explain facility or the db2pd tool to see whether the optimizer used compression for system temporary tables.