16.20 - TVSTemperature Query Bands - Teradata Vantage NewSQL Engine

Teradata Vantage™ - Teradata® Virtual Storage

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
created_date
March 2019
category
Configuration
featnum
B035-1179-162K

TVSTemperature query bands can be used to specify an initial temperature setting for subsets of table data. This allows the data to be stored on access-appropriate media sooner than the normal Teradata VS monitoring and migration process would otherwise provide. Use the TVSTemperature query bands when the frequency of access for different kinds of data is known or can be anticipated.

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.

The following TVSTemperature query bands can be used in any combination.

Query Band Sets Initial Temperature For
TVSTEMPERATURE Data for which a temperature is not specified by other TVSTemperature query bands.

If this query band is not used, the initial temperature set for this data follows the defaults specified by the Storage settings in DBS Control.

TVSTEMPERATURE_PRIMARY Primary data for tables and secondary indexes
TVSTEMPERATURE_PRIMARYCLOB Primary data for LOBs that are eligible for compression.
TVSTEMPERATURE_FALLBACK Fallback data for tables and secondary indexes.
TVSTEMPERATURE_FALLBACKCLOB Fallback data for LOBs that are eligible for compression.

Each of the query bands can be set to either COLD, WARM, HOT, or VERYHOT.

Query Band Value Set For Data Expected to be Accessed
COLD Infrequently. Data is stored initially on relatively slow media.
WARM Moderately frequently. Data is stored on media of moderate speed.
HOT Very frequently. Data is stored initially on the fastest media.
VERYHOT The most frequently accessed. Data is stored on the fastest available storage media. Teradata Database attempts to maintain this data in a memory cache for fastest access.
Although the data is initially stored on media that corresponds to the temperature set by the query bands, if subsequent monitoring by Teradata Database indicates the data is accessed more or less frequently than the set temperature, the data can be migrated to other media.
These query bands are used with load utilities such as FastLoad, MultiLoad, Teradata Parallel Data Pump. The following SQL statements, used to populate tables, support the temperatures that have been set using the TVSTemperature query bands:
  • INSERT into an empty table
  • INSERT ... SELECT into an empty table
  • MERGE inserts into an empty table
  • CREATE TABLE ... AS ... WITH DATA
  • CREATE HASH INDEX
  • CREATE JOIN INDEX

Example: Force Newly Loaded Historical Data to COLD

A customer is loading five years of historical data (not previously stored in the Teradata Database) and intends to perform some background analytics. Given the age of the data and the relatively light usage being planned, this data should reside on slow storage intended for infrequently accessed data.

Without using the TVSTemperature query band, the newly loaded data would be stored on cylinders having the default temperature (specified in DBS Control), and subsequently migrated over time to cold storage, as the system determines that the data is infrequently accessed.

Using the query band to load this data directly to cold storage makes sense in this instance because the likely frequency of data access is clearly known.

Example: Forcing Newly Loaded Fallback Data to COLD

The following SQL statement causes the temperature for all fallback data loaded into an empty table during the current session to be classified as COLD, and stored on storage media (or portions of storage devices) that are graded as slow in relation to the other storage media.

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

Example: Forcing Newly Loaded Primary Data to HOT

The following SQL statement causes the temperature for all primary data loaded into an empty table during the current session to be classified as HOT, and stored on relatively fast storage media. All other types of data loaded during this session will be classified as COLD.

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

Example: Forcing Newly Loaded Data to VERYHOT for Fastest Access

You can use query bands strategically to set the temperature of data being loaded into the system, as in the following example scenario.

The telephone company requires very fast response time on queries processing data from today and yesterday. However, the query volume varies greatly depending on the time of day:
  • The volume is highest on the hour, every hour, from 7:00 a.m. to 6:00 p.m.
  • Data loads occur every 10 minutes, and then a set of critical queries runs against the newly loaded data, and the data from today and yesterday.
  • When an issue occurs, very fast processing is required on queries against this data.

Assume that there is enough physical memory on the system so that an entire load can fit into VERYHOT cache. The default setting for VERYHOT cache is 50% of FSG cache. If the customer has a 6-node system with 768 GB of memory per node, there would be more than 2 TB of VERYHOT cache available for this application.

To load the new data into VERYHOT:

SET QUERY_BAND = 'TVSTEMPERATURE = VERYHOT;'
FOR SESSION;

The older data will cool down on its own as access to that data becomes less frequent. The latest data is loaded as VERYHOT and will remaining VERYHOT until it cools down.

Related Topics

For more information on ... See ...
Data temperatures Teradata Vantage™ - Database Design, B035-1094.
Query bands
  • Teradata Vantage™ SQL Data Definition Language Detailed Topics , B035-1184 .
  • Teradata Vantage™ - Database Administration, B035-1093.
Storage settings in DBS Control Teradata Vantage™ - Database Utilities , B035-1102 .
CREATE TABLE, CREATE JOIN INDEX, CREATE HASH INDEX statements Teradata Vantage™ SQL Data Definition Language Syntax and Examples, B035-1144.
INSERT, INSERT ... SELECT, MERGE statements Teradata Vantage™ SQL Data Manipulation Language , B035-1146 .