Compressing Data Loaded into Empty Subtables Set to AUTOTEMP - Teradata Database

Compressing Data Loaded into Empty Subtables Set to AUTOTEMP

To assign different temperatures to data loaded in empty primary, fallback, and CLOB subtables 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 CLOBs).

TVSTEMPERATURE_PRIMARYCLOBS: The temperature to assign to the primary row CLOB subtables.

TVSTEMPERATURE_FALLBACK: The temperature to assign to the fallback row subtable, the fallback row secondary index subtables, and the fallback LOB subtables (excluding CLOBs).

TVSTEMPERATURE_FALLBACKCLOBS: The temperature to assign to the fallback row CLOB 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 SQL Data Definition Language.

Note: 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.