15.00 - BLOCKCOMPRESSION - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1184-015K

BLOCKCOMPRESSION

Use this option to change the current temperature‑based block compression state of a table to a new state.

See “BLOCKCOMPRESSION” on page 526 for information about how the BLOCKCOMPRESSION options work for tables.

One of the implications of changing the block compression for a table from AUTOTEMP mode to MANUAL or NEVER mode is that the file system does not make the block compression state of the table consistent. If some blocks in the table were compressed and some were not, which is the typical condition for a table whose block compression had been AUTOTEMP, that remains true after the table is altered to either MANUAL or NEVER block compression mode.

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 uncompressed 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 uncompressed.

The following table explains what you must do to make the block-level compression for such a table consistent. See Utilities: Volume 1 (A-K) for information about how to use the Ferret utility.

 

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 uncompressed blocks of the table and have changed the BLOCKCOMPRESSION option to either MANUAL or NEVER, newly created blocks remain uncompressed.

    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.