Modify the table-level hot cache policy

This topic describes how to use SQL statements to modify the table-level hot cache policy (STORAGE_CACHE_POLICY):

• Modify the hot cache policy of a regular table.
• Modify the hot cache policy of an index table.

Considerations

You can modify the hot cache policy of a table/index only in the shared storage mode.

Modify the hot cache policy of a regular table

After a table is created, you can use the ALTER TABLE statement to modify the hot cache policy (STORAGE_CACHE_POLICY) of the table. The syntax is as follows:

ALTER TABLE table_name SET STORAGE_CACHE_POLICY (storage_cache_policy_option);

storage_cache_policy_option:
    GLOBAL = {"hot" | "auto"}
    | timeline_strategy_list

timeline_strategy_list:
    BOUNDARY_COLUMN = column_name
    | BOUNDARY_COLUMN_UNIT = {"s" | "ms"}
    | HOT_RETENTION = intnum retention_time_unit

retention_time_unit:
    YEAR
    | MONTH
    | WEEK
    | DAY
    | HOUR
    | MINUTE

Parameter description

The attributes in storage_cache_policy_option are defined in the Key-Value format. The following table describes the attributes.

• GLOBAL = {"hot" | "auto"}: specifies the hot cache policy of the entire table. Valid values:

  • hot: specifies that all data in the table is hot data. If the cache disk space is sufficient, all data in the table will be cached to the local cache disk.
  • auto: specifies that the hot data in the table is automatically identified by the system.

• timeline_strategy_list: specifies the list of time-axis strategy parameters. The parameters are separated by commas. The hot cache time-axis strategy supports a mechanism to determine whether partition data is hot data based on time. The system automatically adjusts the partition data on the local cache disk based on the configured strategy.

  Note

  A time-axis strategy is based on the partition range defined by the RANGE partitioning method. When a certain time condition is met, the data in the partition is identified as hot data. When you use a time-axis strategy, note the following:

  • Only RANGE-partitioned tables are supported. The RANGE partition can be a primary partition or a secondary partition. This is because a time-based strategy is used to determine whether data is expired. However, a double RANGE partition is not supported. This is because the system cannot determine which RANGE partition to use as the time reference.
  • When you use a time-axis strategy, the partition expression can contain only column names. Expressions are not supported. For example, PARTITION BY RANGE COLUMNS(expr(col3)) is not supported.
  • BOUNDARY_COLUMN must be a partitioning key. If the partitioning key contains multiple columns, BOUNDARY_COLUMN must be the first column, which is used to determine whether the partition is expired.

  • BOUNDARY_COLUMN = column_name: specifies the column used to determine hot data. Supported data types: integer (BIGINT or INT), and time (TIMESTAMP, DATE, DATETIME, or YEAR). The data in the column is in the Unix timestamp format for integer data types.

    • If the data type of BOUNDARY_COLUMN is an integer, the table can be RANGE-partitioned or RANGE COLUMNS-partitioned.
    • If the data type of BOUNDARY_COLUMN is a time type, the table must be RANGE COLUMNS-partitioned.

  • BOUNDARY_COLUMN_UNIT = {"s" | "ms"}: specifies the time unit of the BOUNDARY_COLUMN parameter. This parameter is supported only when the data type of BOUNDARY_COLUMN is an integer. If the integer value is used as a timestamp, you must specify the timestamp unit. Otherwise, the system may incorrectly parse the timestamp. Valid values:

    • If the partitioning column is of the INT type, the value of BOUNDARY_COLUMN_UNIT can only be s.

    • If the partitioning column is of the BIGINT type, the value of BOUNDARY_COLUMN_UNIT can be s or ms.

      Notice

      If the format is not a Unix timestamp, the system cannot correctly identify the time represented by the INT type.

  • HOT_RETENTION = intnum retention_time_unit: specifies the time range of hot data.

    • intnum: specifies an integer.

    • retention_time_unit: specifies the time unit. Valid values:

      • YEAR: specifies years.
      • MONTH: specifies months.
      • WEEK: specifies weeks.
      • DAY: specifies days.
      • HOUR: specifies hours.
      • MINUTE: specifies minutes.

Examples

• Modify the hot cache policy of table tbl1 to automatic.

  ALTER TABLE tbl1 SET STORAGE_CACHE_POLICY (GLOBAL = "auto");
  

• Modify the hot cache policy of table tbl2 to cache partition data that is within 2 months from the current time based on the col3 column.

  ALTER TABLE tbl2 SET STORAGE_CACHE_POLICY (BOUNDARY_COLUMN = col3, HOT_RETENTION = 2 MONTH); 
  

Modify the hot cache strategy of an index table

You can use the ALTER TABLE statement to modify the hot cache strategy for an existing index (that is, the STORAGE_CACHE_POLICY clause), as follows:

ALTER TABLE table_name ALTER INDEX STORAGE_CACHE_POLICY (storage_cache_policy_option);

storage_cache_policy_option:
    GLOBAL = {"hot" | "auto" | "none"}
    | timeline_strategy_list

timeline_strategy_list:
    | BOUNDARY_COLUMN = column_name
    | BOUNDARY_COLUMN_UNIT = {"s" | "ms"}
    | HOT_RETENTION = intnum retention_time_unit

retention_time_unit:
    YEAR
    | MONTH
    | WEEK
    | DAY
    | HOUR
    | MINUTE

Description of related parameters

• GLOBAL = {"hot" | "auto" | "none"}: specifies whether the data in the index is hot data. Valid values:

  • hot: The specified index is hot storage. If the cache disk space is sufficient, all the data in the index will be cached in the local cache disk.
  • auto : specifies that the system automatically identifies hot data in the table.
  • none: default value. The cache policy for the index is inherited from the cache policy defined for the partitioned table specified by STORAGE_CACHE_POLICY. For more information, see the parameter description of STORAGE_CACHE_POLICY.

• timeline_strategy_list: A list of time axis strategy parameters separated by English commas. The hot cache time axis strategy supports a mechanism to determine if partition data is hot by time. The system automatically adjusts the partition data on the local cache disk based on the configured strategy. This parameter is not recommended for use on local indexes.

  Note

  Time-to-live: Specifies partitioning columns and the time-to-live values for these columns to cache hot data for each partition. A partition is considered hot data when the specified time-to-live conditions are met. The time-to-live strategy is applicable to only Range-partitioned tables (which may be Level 1 or Level 2 partitions). You must ensure that only one time-to-live value can be specified for each column in the partitioning expression. Otherwise, the specified value is not applicable to the column. If you specify a partitioning column in a partitioning expression, the partitioning column must be specified as a time-to-live column, and its data type must be TIMESTAMP or DATETIME. In a partitioning expression, you can use only column names and not expressions. You cannot specify multiple partitions for a single column as the time-to-live column. For example, the partitioning expression PARTITION BY RANGE COLUMNS(expr(col3)) is invalid.

  • BOUNDARY_COLUMN = column_name : Used to identify hot data. Supports integer (BIGINT or INT type, in the Unix timestamp format) and time (TIMESTAMP, DATE, DATETIME, or YEAR type) types.

    • If the BOUNDARY_COLUMN data type is integer, the table supports the RANGE or RANGE_COLUMNS partitioning type.
    • If BOUNDARY_COLUMN is a temporal type, only range column partitioning is supported.

  • BOUNDARY_COLUMN_UNIT = {"s" | "ms"}: specifies the unit of time for the BOUNDARY_COLUMN parameter. This parameter can be set only when BOUNDARY_COLUMN is an integer. When an integer is used as a timestamp, the corresponding time unit must be provided. Otherwise, the timestamp may be incorrectly parsed. Valid values: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, and 64.

    • When the partitioning column is of the INT type, the BOUNDARY_COLUMN_UNIT can be only set to s.
    • When the partitioning column is of the BIGINT type, BOUNDARY_COLUMN_UNIT can be s or ms.

  • HOT_RETENTION = intnum retention_time_unit specifies the time range for hot data. The unit is seconds.

    • intnum: Represents an integer.

    • retention_time_unit : time unit. Valid values:

      • YEAR: Indicates year.
      • MONTH : Month.
      • WEEK: Represents week.
      • DAY specifies a day.
      • HOUR : The hours.
      • MINUTE indicates the minute.

Example

Set the hot data of index idx2 to be recognized by the system.

ALTER TABLE test_tbl2 ALTER INDEX idx2
    STORAGE_CACHE_POLICY (GLOBAL = "auto");

Related documents

• Overview of custom hotspots cache policy
• Create table-level storage cache strategies
• Modify the partition-level hot data caching policy
• ALTER TABLE
• View Operations Guide for Customizing Hotspot Cache Policies