16.20 - BLOCKCOMPRESSION - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Syntax and Examples

Teradata Database
Teradata Vantage NewSQL Engine
Release Number
March 2019
English (United States)
Last Update

Specifies whether the data in the hash index should be block-compressed based on the temperature of the cylinders on which it is stored.

You can specify the following settings for block_compression_option.

The compressed state of the data in the hash index can be changed by Teradata Database at any time based on its temperature.
You can still issue query band options or Ferret commands, but if the compressed state of the data does not match its temperature, such changes might be undone by the system over time.
The hash index uses the compression option (MANUAL, AUTOTEMP or NEVER) set in the DBS Control parameter DefaultTableMode. For details, see Teradata Vantage™ - Database Utilities , B035-1102 . Note that value of DefaultTableMode is not saved in the hash index definition as part of a CREATE HASH INDEX request, so a hash index set to DEFAULT is affected by any future change to the DefaultTableMode parameter.
That block level compression is applied based on the default for the hash index at the time the hash index is created. Hash indexes can be compressed or uncompressed at any time after loading by using the Ferret COMPRESS and UNCOMPRESS commands. Data inserted into the existing hash index inherits the current compression status of the hash index at the time the data is inserted.
The hash index is not compressed even if the DBS Control block compression settings indicate otherwise. Teradata Database rejects Ferret commands to manually compress the hash index, but Ferret commands to decompress the index are valid.