Purpose
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.
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 }
- tid
- Identifies the subtable for which cylinder temperatures are set.
- 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.
- 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.
Related Topics
For more information on… | See… |
---|---|
nonpartitioned NoPI tables | Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144. |
PARTITION columns | Teradata Vantage™ - Database Design, B035-1094. |
Example: Using FORCE to change the temperature of a table
The following example changes the temperature of a table.
Ferret ==> > force 0 6CE 400 temperature = hot FORCE command changed the temperature of table test.table1 to HOT. The temperature of 117 cylinders have been changed to HOT.
Example: Using FORCE to change the temperature of a row partition
The following example sets the temperature for a subset of rows a partitioned table. In this case, the rows are those in the combined partition identified by an external partition number of 9.
Ferret ==> > force "test.ppi_t02" 400 9 temperature = hot FORCE command changed the temperature of table test.ppi_t02 to HOT for the specified partition. The temperature of 5 cylinders have been changed to HOT.