Compressing Data Loaded into Empty Subtables Set to AUTOTEMP | Vantage - 17.10 - Compressing Data Loaded into Empty Subtables Set to AUTOTEMP - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Database Design

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
User Guide
Publication ID
B035-1094-171K
Language
English (United States)
To assign different temperatures to data loaded in empty primary, fallback, and compressible LOB subtables (XML, JSON, and character LOB) that have the AUTOMATIC setting, use the SET QUERY_BAND statement choosing the temperature values VERYHOT, HOT, WARM, or COLD for the following:
  • TVSTEMPERATURE_PRIMARY: The temperature to assign to the primary row subtable, the primary row secondary index subtables, and the primary LOB subtables (excluding compressible LOBs).
  • TVSTEMPERATURE_PRIMARYCLOBS: The temperature to assign to the primary row compressible LOB subtables.
  • TVSTEMPERATURE_FALLBACK: The temperature to assign to the fallback row subtable, the fallback row secondary index subtables, and the fallback LOB subtables (excluding compressible LOBs).
  • TVSTEMPERATURE_FALLBACKCLOBS: The temperature to assign to the fallback row compressible LOB subtable.
  • TVSTEMPERATURE: The temperature to assign to all the subtables in the preceding bullets.

For example:

SET QUERY_BAND = 'TVSTEMPERATURE_FALLBACK = COLD;' FOR SESSION;

This indicates that the fallback data should be cold and the other subtables should use the system default temperature for the table type (as defined in DBS Control).

An example of using multiple specifications is:

SET QUERY_BAND = 'TVSTEMPERATURE_PRIMARY=HOT; TVSTEMPERATURE = COLD;' FOR SESSION;

This indicates that the primary row subtable should be hot and all other subtables should be cold. In this example, the TVSTEMPERATURE name takes precedence over any system default temperature settings.

You can specify multiple TVSTEMPERATURE-style names if the exact name is used only once.

There are a number of advantages to managing compression based on the data temperature.
  • Large sets of historical data that are not frequently accessed are often kept uncompressed.

    In some cases, you might not even realize that data is no longer being used. Temperature-based compression automatically locates such data and compresses it.

  • Data in partitions of partitioned tables are often not frequently used.

    Temperature-based compression enables you to compress such partition data automatically.

  • By using temperature-based compression with date-partitioned tables where new partitions are constantly being added, but old partitions are retained, you are relieved of determining which partitions are old enough to have become infrequently used.

For more information, see Teradata Vantageā„¢ - SQL Data Definition Language Detailed Topics, B035-1184.

In most cases, the data will be assigned the temperature you specify and will be moved more quickly to appropriate storage media. However, in cases where newly loaded table data is stored on existing cylinders that also store data from other tables, the temperature and storage location of those cylinders will not be changed.