15.10 - BLOCKCOMPRESSION - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

Teradata Database
Release Number
December 2015
Content Type
Programming Reference
Publication ID
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 Utilities, B035-1102. For details, see “CREATE JOIN INDEX” in SQL Data Definition Language - Detailed Topics, B035-1184.

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, Teradata Database 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.
The join index uses the compression option setting (MANUAL, AUTOTEMP, or NEVER) of the DBS Control parameter DefaultTableMode. For details, see 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.
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.
The join index is not compressed even if the DBS Control block compression settings indicate otherwise. Teradata Database does not allow Ferret commands to manually compress the join index, but Ferret commands to decompress the index are valid.