15.10 - TVSTemperature Query Bands - Teradata Database

Teradata Database Teradata Virtual Storage

prodname
Teradata Database
vrm_release
15.10
category
Configuration
featnum
B035-1179-151K

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 temperature-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 CLOBs

TVSTEMPERATURE_FALLBACK

Fallback data for tables and secondary indexes

TVSTEMPERATURE_FALLBACKCLOB

Fallback data for CLOBs

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 fast 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.

Note: 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
  • 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 TVSTemperature, 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. Loading it directly to cold storage makes sense in this instance since the likely frequency of data access is clearly known.

    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;

    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;

    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 a.m. to 6 p.m.
  • Data loads occur every 10 minutes, and then a set of critical queries runs against the newly loaded data and 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 VERY HOT cache. The default setting for VERY HOT 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 VERY HOT cache available for this application.

    To load the new data into VH (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 VERY HOT and will remain VERY HOT until it also cools down.