Use this option to change the current temperature-based block compression state of a table to a new state.
For information about how the BLOCKCOMPRESSION options work for tables, see BLOCKCOMPRESSION (CREATE TABLE).
AUTOTEMP may not compress all blocks in the table. If you change the block compression from AUTOTEMP to MANUAL or NEVER, there may be blocks in the table that remain compressed.
A table in this state is functional, but has inconsistent block compression. If you change the block compression option for a table to MANUAL, when you modify the 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 from AUTOTEMP to either MANUAL or NEVER, the cylinders of newly created data blocks for a table remain noncompressed.
A table is fully functional when in a mixed block-level compression state with inconsistent block compression. If you submit an ALTER TABLE request 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.
The best practice is not to use temperature-based block-level compression for a table that requires block-level compression consistency for the entire 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.