BLOCKCOMPRESSION Option | CREATE TABLE (Table Options Clause) | Teradata Vantage - BLOCKCOMPRESSION - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
imq1591724555718.ditamap
dita:ditavalPath
imq1591724555718.ditaval
dita:id
B035-1184
lifecycle
previous
Product Category
Teradata Vantage™

Use this option to set the temperature-based block compression state of a table.

Teradata Virtual Storage tracks data temperatures at the level of cylinders, not tables, and the file system obtains its temperature information from Teradata Virtual Storage, managing temperature-related compression at the cylinder level. See Teradata Vantage™ - Teradata® Virtual Storage, B035-1179.

Teradata Virtual Storage tracks the cylinder temperature metrics for all tables when temperature-based block-level compression is enabled. Temperatures are in effect system-wide, and Teradata Virtual Storage takes all temperatures into account when determining which data on the system is associated with a specific temperature.

The file system determines whether a table is using temperature-based block-level compression and applies selective compression or decompression based on the cylinder temperatures. For example, assuming that the DBS Control parameter TempBLCThresh is set to COLD, the threshold defined for COLD applies to all tables in the system. If the cylinders of an AUTOTEMP table are in the defined COLD band, the cylinders are eligible for block-level compression. If temperature-based block-level compression is disabled but block-level compression is enabled, the database manages AUTOTEMP tables in the same way as MANUAL tables.

The file system cannot compress the cylinders of a MANUAL table using either ordinary block-level compression or temperature-based block-level compression. Instead, you must use the Ferret commands COMPRESS or UNCOMPRESS to change the compression status of an existing MANUAL table or subtable manually. You can also use the BlockCompression query band to load rows into new MANUAL tables or subtables using the appropriate block-level compression.

You can activate temperature-based block-level compression for a new MANUAL table by using an ALTER TABLE statement to change its BLOCKCOMPRESSION definition from MANUAL to AUTOTEMP. You can also use one of the following TVSTemperature query bands to load data into a new AUTOTEMP table or subtable.
  • TVSTEMPERATURE_PRIMARY
  • TVSTEMPERATURE_PRIMARYCLOBS
  • TVSTEMPERATURE_FALLBACK
  • TVSTEMPERATURE_FALLBACKCLOBS

You can also use the Ferret commands COMPRESS and UNCOMPRESS to manually change the state of an AUTOTEMP table. Ferret returns a warning message when you change these parameters. For information about the Ferret utility, see Teradata Vantage™ - Database Utilities, B035-1102.

If the compressed state of the data does not match its temperature, the block-level compression changes you make using Ferret can be undone by the file system over time if the BLOCKCOMPRESSION option for the table is set to AUTOTEMP. Because of this, the best practice is not to use temperature-based block-level compression for a table determined to require compression consistency for the entire table.

The same set of subtables that can be compressed with ordinary block-level compression are compressed with temperature-based block-level compression.
  • Primary and fallback base tables
  • Primary and fallback CLOB data subtables

The following table lists the available options for BLOCKCOMPRESSION.

Option Description
AUTOTEMP AUTOTEMP identifies tables whose block-level compression should be automatically managed based on the temperature of their data. The file system can change the block-compressed state of the data in the table at any time based on its Teradata Virtual Storage temperature.

AUTOTEMP tables normally exist in a mixed compression state because temperature-based block-level compression does not directly apply compression to an entire table all at once. As various cylinders within a table grow warmer or colder over time, the file system either compresses or decompresses those cylinders as is appropriate for their Teradata Virtual Storage temperature.

The cylinders in an AUTOTEMP table become eligible for temperature-based block-level compression or decompression only when they reach, become lower than, or exceed the defined threshold for the specified TempBLCThresh option.

  For all of the data in a table to be block compressed (or decompressed) at once, Teradata Virtual Storage must become aware that all cylinders in the table had reached or exceeded the threshold for the specified TempBLCThresh option. This would only occur if all of the cylinders in the table had not been accessed in some time and had been classified as COLD with respect to all other cylinders for compression or as HOT for decompression.

AUTOTEMP tables normally exist in a mixed compression state because temperature-based block-level compression does not directly apply compression to an entire table all at once. As various cylinders within a table grow warmer or colder over time, the file system either compresses or decompresses those cylinders as is appropriate for their Teradata Virtual Storage temperature.

Teradata Virtual Storage tracks the cylinder temperature metrics for all tables when temperature-based block-level compression is enabled at the level of cylinders, not tables. Because the file system obtains its temperature information from Teradata Virtual Storage, it also handles temperature-related compression at cylinder level. Temperatures are in effect system-wide, and Teradata Virtual Storage takes all temperatures into account when it determines which data on the system is associated with which temperature.

Once that determination has been made, the file system judges whether a table is using temperature-based block-level compression and if so, applies selective compression or decompression based on the cylinder temperatures. For example, assuming that the DBS Control parameter TempBLCThresh is set to COLD, the threshold defined for COLD applies to all tables in the system, so if an AUTOTEMP table cylinders are in the COLD band, they are eligible for block-level compression.

Temperature-based thresholds for the block-level compression of AUTOTEMP tables work as follows.
  • If data blocks are initially block-level compressed and then become warmer than the defined threshold for compression, the file system decompresses them.
  • If data blocks are initially not block-level compressed and then become colder than the defined threshold for decompression, the file system compresses them.

If temperature-based block-level compression is disabled but block-level compression is enabled, the database treats AUTOTEMP tables the same as MANUAL tables.

You can still issue TVSTemperature 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 file system over time. Because of this, the best practice is not to use temperature-based block-level compression for a table that you think requires compression consistency for the entire table.

DEFAULT DEFAULT identifies tables whose temperature-based block-level compression is determined by the DBS Control parameter DefaultTableMode. For more information, see Teradata Vantage™ - Database Utilities, B035-1102.
The value of DefaultTableMode is not saved in the table definition as part of a CREATE TABLE or ALTER TABLE statement, so a table set to BLOCKCOMPRESSION=DEFAULT is affected by any future change to the DefaultTableMode parameter.
MANUAL MANUAL identifies tables that are not managed automatically like AUTOTEMP tables are.

You can specify a TVSTemperature query band option to determine the block-level compression state of the data before you load rows into an empty table.

Once a table is populated, the file system does not change the compressed state of the data in the table unless you take specific action to do so using Ferret commands.

You can use the non-temperature-related BlockCompression query band to load an empty table with non-temperature-related data to block-level compress.
NEVER NEVER identifies tables that should never be temperature-based block-level compressed or decompressed, even if a query band or the applicable DBS Control parameter defaults indicate otherwise. The file system does not compress or decompress table or subtable data even if the DBS Control block-level compression settings indicate otherwise.

This means that the database rejects Ferret commands to manually compress table data, but Ferret commands to decompress table data are valid.

A table is fully functional in a mixed block-level compression state, but with inconsistent block compression. Best practice is not to use the AUTOTEMP option or any form of temperature-based block-level compression for a table that requires compression consistency for the entire table.

If you submit an ALTER TABLE statement 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 noncompressed blocks of the table and have changed the BLOCKCOMPRESSION option to either MANUAL or NEVER, newly created blocks remain noncompressed.

For tables that are not defined with BLOCKCOMPRESSION=AUTOTEMP, you must control their block-level compression states yourself using Ferret commands or, if a table is not populated with rows, you can use one of the TVSTemperature query bands to specify the type of block-level compression to use for the newly loaded rows. If temperature-based block-level compression is disabled but block-level compression is enabled, the database treats AUTOTEMP tables the same as MANUAL tables.

For all of the data in a table to be block compressed or decompressed at once in an AUTOTEMP table, Teradata Virtual Storage must recognize that all cylinders in the table have reached the threshold specified by the DBS Control parameter TempBLCThresh. For example, suppose the threshold value for TempBLCThresh is set to WARM.

All Cylinders in the Table Eligibility
Reach or fall below the WARM or COLD thresholds Block-level compression.
Reach or exceed the HOT threshold Decompression.

For information about the actions necessary to make the block compression consistent, see BLOCKCOMPRESSION.

You can combine multivalue 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.