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