BLOCKCOMPRESSION Option | ALTER TABLE | Teradata Vantage - 17.10 - BLOCKCOMPRESSION - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1184-171K
Language
English (United States)

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.

Typically for a table with block compression set to AUTOTEMP, some blocks in the table are compressed and some are not. If you change the block compression for a table from AUTOTEMP mode to MANUAL or NEVER, some blocks in the table remain compressed and some do not.

A table is fully functional when it is in this state, but it 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.

The following table explains what you must do to make the block-level compression for such a table consistent. For information about how to use the Ferret utility, see Teradata Vantage™ - Database Utilities, B035-1102.

IF the block-level compression for the table changes from AUTOTEMP to this state … Use the following Ferret utility commands to make its block-level compression consistent …
MANUAL
  • COMPRESS
  • UNCOMPRESS
NEVER UNCOMPRESS

A table is fully functional when it is in a mixed block-level compression state, but its block compression is inconsistent. 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 multi-value compression, algorithmic compression, and block-level compression for the same table to achieve better compression, but as a general rule you should not use algorithmic compression with block-level compression because of the possibility of a negative performance impact for other workloads.