BLOCKCOMPRESSION Option | ALTER TABLE | Teradata Vantage - BLOCKCOMPRESSION - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1184
lifecycle
latest
Product Category
Teradata Vantage™

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.