Use this option to set the temperature-based block compression state of a table.
The file system determines whether a table is using temperature-based block-level compression and applies selective compression or decompression based on the cylinder temperatures. If the cylinders of an AUTOTEMP table are in the defined COLD band, the cylinders are eligible for block-level compression. If temperature-based block-level compression is disabled but block-level compression is enabled, the database manages AUTOTEMP tables in the same way as MANUAL tables.
The file system cannot compress the cylinders of a MANUAL table using either ordinary block-level compression or temperature-based block-level compression. Use the BlockCompression query band to load rows into new MANUAL tables or subtables using the appropriate block-level compression.
You can activate temperature-based block-level compression for a new MANUAL table by using an ALTER TABLE statement to change its BLOCKCOMPRESSION definition from MANUAL to AUTOTEMP.
The best practice is not to use temperature-based block-level compression for a table determined to require compression consistency for the entire table.
- Primary and fallback base tables
- Primary and fallback CLOB data subtables
The following table lists the available options for BLOCKCOMPRESSION.
| Option | Description |
|---|---|
| AUTOTEMP | AUTOTEMP identifies tables whose block-level compression is to be automatically managed based on the temperature of their data. AUTOTEMP tables typically exist in a mixed compression state because temperature-based block-level compression does not directly apply compression to an entire table all at once. The cylinders in an AUTOTEMP table become eligible for temperature-based block-level compression or decompression only when reaching, falling below, or exceeding the defined threshold for the specified TempBLCThresh option. |
| DEFAULT | DEFAULT identifies tables whose temperature-based block-level compression is determined by the system. . |
| MANUAL | MANUAL identifies tables that are not managed automatically like AUTOTEMP tables are. Once a table is populated, the file system does not change the compressed state of the data in the table. You can use the non-temperature-related BlockCompression query band to load an empty table with non-temperature-related data to block-level compress.
|
| NEVER | NEVER identifies tables that must never be temperature-based block-level compressed or decompressed, even if a query band indicates otherwise. The file system does not compress or decompress table or subtable data. |
A table is fully functional in a mixed block-level compression state, but with inconsistent block compression. Best practice is not to use the AUTOTEMP option or any form of temperature-based block-level compression for a table that requires compression consistency for the entire table.
If you submit an ALTER TABLE statement to change the BLOCKCOMPRESSION option for a table to MANUAL, when you modify the block-level compressed blocks of the table, the newly created blocks are compressed. If the table was changed to NEVER, when you modify the compressed blocks of the table, the file system decompresses the newly created blocks. If you modify the noncompressed blocks of the table and have changed the BLOCKCOMPRESSION option to either MANUAL or NEVER, newly created blocks remain noncompressed.
For tables that are not defined with BLOCKCOMPRESSION=AUTOTEMP, you must control their block-level compression states yourself. If temperature-based block-level compression is disabled but block-level compression is enabled, the database treats AUTOTEMP tables the same as MANUAL tables.
For information about the actions necessary to make the block compression consistent, see BLOCKCOMPRESSION (ALTER TABLE).
You can combine multivalue compression, algorithmic compression, and block-level compression for the same table to achieve better compression. However, do not use algorithmic compression with block-level compression because of the possibility of a negative performance impact for other workloads.