BLOCKCOMPRESSION - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
wgr1555383704548.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

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.