FORCE Command | Specifying Data Temperatures | Teradata Vantage - FORCE - Analytics Database - Teradata Vantage

Teradata Vantage™ - Teradata® Virtual Storage - 17.20
Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-10-30
dita:mapPath
ulv1628112427040.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
hkz1472253437334
Product Category
Teradata Vantage

Sets the cylinders occupied by a table or range of rows, to a specified data temperature, regardless of how frequently the data has been historically accessed.

Use the FORCE command only under the direction of Teradata Support Center personnel.

This command can have short- and long-term performance effects on both the targeted table and on other tables. Forcing a specific temperature can impact performance for tables that were not explicitly referenced in the FORCE command.

Syntax

FORCE tid [ rowspec [ TO rowspec ] ]
  TEMPERATURE = { HOT | WARM | COLD | VERYHOT }

Syntax Elements

tid
Identifies the subtable for which cylinder temperatures are set.
A tid consists of a unique identifier for the table plus an identifier for one subtable component of the table, such as the primary data subtable. The table can be uniquely identified in either of two ways:
  • Specify the name of the database to which the table belongs and the table name, separated by a period. The names individually or together must be delimited. The following formats are valid:
    • "database_name.table_name"
    • "database_name"."table_name"
    • 'database_name.table_name'
    • 'database_name'.'table_name'
  • Specify the unique numeric identifier of the table, which consists of two numeric values separated by a space. The unique table identifier is the first two numeric values returned by the TABLEID command. These two numbers are common to all subtables that comprise the table.
    The format of the input numbers depends on the current radix setting, which is displayed by the RADIX command.
The second part of the tid identifies the subtable, and is called a type_and_index. This value can be represented in a number of ways, which are explained in the documentation for the Ferret utility in Teradata Vantage™ - Database Utilities, B035-1102. The primary data subtable has a numeric type_and_index value of decimal 1024 or hex 400. Specifying a 0 causes the FORCE command to be applied to all subtables of the table.
rowspec  [TO rowspec]
  • For a nonpartitioned NoPI table, the row or range of rows for which a data temperature is to be set.
  • For a partitioned table, the “combined partition number” that identifies the partition for which a data temperature is to be set (or the combined partition numbers that identify a range of logically contiguous partitions).

    The combined partition number is a calculated value that takes into account any column partitions and the different levels of partitioning in multilevel partitioned tables. It uniquely identifies every partition in the table.

    For tables with only a single-level partition without column partitioning, the combined partition number for any row is the value of the system-derived PARTITION column of the row.

    For more information about calculating combined partition numbers for other types of tables, see Teradata Vantage™ - Database Design, B035-1094.

VERYHOT
HOT
WARM
COLD
The data temperature that will be set for the cylinders that store row data for the specified table or rows, regardless of the historical frequency of access for that data.
  • VERYHOT cylinders are the most frequently accessed.
  • HOT cylinders are frequently accessed.
  • WARM cylinders are accessed with moderate frequency.
  • COLD cylinders store the least frequently accessed data.

Usage Notes

For new tables, Vantage does not yet have metrics on data access to use in assigning a temperature to the table data, so relies on the defaults set in the Storage group of DBS Control settings. The FORCE command can be used to force a non-default temperature on new tables without waiting for the system to collect data access statistics.

For tables with definitions that include the BLOCKCOMPRESSION = AUTOTEMP option (that is, tables using the temperature-based block-level compression feature), forcing uncompressed data to colder temperatures can cause the data to be compressed, and forcing compressed data to warmer temperatures can cause the data to be uncompressed. If the target of the FORCE command is a large amount of data, this could impact performance in the short term.

In addition to setting the temperature for whole tables, FORCE can be used to set the temperature for a row, range of rows, or specific partitions. For example:
  • When data in older rows of a nonpartitioned NoPI table is less frequently accessed than data in newer rows, you can use FORCE to set the older rows to COLD.
  • When one or more partitions of a partitioned table are expected to be more frequently accessed than other partitions, you can use FORCE to set the frequently accessed partitions to HOT.

When temperature-based block-level compression is enabled, using FORCE to set infrequently accessed tables or partitions to COLD causes them to be compressed sooner than they otherwise would be.

Data temperature can change over time from the temperature specified with the FORCE command if the measured frequency of actual data access differs from the temperature set using the FORCE command.

The temperature of boundary cylinders that store rows from more than a single table are not affected by the FORCE command if COLD is specified.

The FORCE command ignores any table scope that have been previously specified, and operates only on the table that is explicitly specified in the FORCE command.