15.00 - BLOCKCOMPRESSION - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1184-015K
Language
English (United States)

BLOCKCOMPRESSION

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, so it also handles temperature‑related compression at cylinder level. See Teradata Virtual Storage for more information about this.

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 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 the cylinders of an AUTOTEMP table are in the defined COLD band, they are eligible for block-level compression. If temperature-based block-level compression is disabled but block-level compression is enabled, Teradata Database treats AUTOTEMP tables the same 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 request 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. Note that Ferret returns a warning message to the requestor who attempts to do this.

    If the compressed state of the data does not match its temperature, the block-level compression changes you make using Ferret might 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 that you think requires 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
  • Fallback secondary index 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.

    AUTOTEMP (continued)

    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, Teradata 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.

    See Utilities: Volume 1 (A-K) for information about how to use the Ferret utility.

    DEFAULT

    DEFAULT identifies tables whose temperature‑based block‑level compression is determined by the DBS Control parameter DefaultTableMode (see Utilities: Volume 1 (A-K) for details).

    Note: The value of DefaultTableMode is not saved in the table definition as part of a CREATE TABLE or ALTER TABLE request, 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.

    See Utilities: Volume 1 (A-K) for information about how to use the Ferret utility.

    Note: 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 Teradata Database rejects Ferret commands to manually compress table data, but Ferret commands to decompress table data are valid.

    See Utilities: Volume 1 (A-K) for information about how to use the Ferret utility.

    A table is fully functional when it is in a mixed block‑level compression state, but its block compression is inconsistent. Because of this, the best practice is not to use the AUTOTEMP option, or not to use 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 request 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 uncompressed blocks of the table and have changed the BLOCKCOMPRESSION option to either MANUAL or NEVER, newly created blocks remain uncompressed.

    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, Teradata 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 become aware that all cylinders in the table have reached the threshold specified by the DBS Control parameter TempBLCThresh. This would occur in the following case. Suppose the threshold value for TempBLCThresh is set to WARM.

     

    IF all of the cylinders in the table …

    THEN they all become eligible for …

    reach or fall below the WARM or COLD thresholds

    block‑level compression.

    reach or exceed the HOT threshold

    decompression.

    See “BLOCKCOMPRESSION” on page 47 for information about what you must do to make the block compression for such a data table consistent. See Utilities: Volume 1 (A-K) for information about how to use the Ferret utility.

    You can combine multi‑value 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.