17.00 - BLOCKCOMPRESSION - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Release Date
September 2020
Content Type
Programming Reference
Publication ID
B035-1144-170K
Language
English (United States)

Block-compress the data in the join index, including compression that occurs based on the temperature of the cylinders on which the data is stored. The definitions of the various thresholds are determined by the DBS Control setting TempBLCThresh. For details, see Teradata Vantage™ - Database Utilities , B035-1102 . For details, see “CREATE JOIN INDEX” in Teradata Vantage™ - SQL Data Definition Language Detailed Topics , B035-1184 .

block_compression_option

AUTOTEMP
The file system determines the block-level compression setting for the join index based on its Teradata Virtual Storage temperature. If temperature-based block-level compression is disabled but block-level compression is enabled, Vantage treats AUTOTEMP join indexes the same as MANUAL join indexes. 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.
DEFAULT
The join index uses the compression option setting (MANUAL, AUTOTEMP, or NEVER) of the DBS Control parameter DefaultTableMode. For details, see Teradata Vantage™ - Database Utilities , B035-1102 . Note that value of DefaultTableMode is not saved in the join index definition as part of a CREATE JOIN INDEX request, so a join index set to DEFAULT is affected by any future change to the DefaultTableMode parameter.
MANUAL
Block level compression is applied based on the default for the join index at the time the join index is created. Join indexes can be compressed or uncompressed at any time after loading by using the Ferret COMPRESS and UNCOMPRESS commands. Data inserted into the existing join index inherits the current compression status of the join index at the time the data is inserted.
NEVER
The join index is not compressed even if the DBS Control block compression settings indicate otherwise. Vantage does not allow Ferret commands to manually compress the join index, but Ferret commands to decompress the index are valid.