Modify the table-level hot cache policy

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

• 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 strategy of a table or index only in shared storage mode.

Modify the hot cache policy of a regular table

After a regular 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

Parameters

The storage_cache_policy_option parameter is defined in the Key-Value format. The following describes the parameters:

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

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

• timeline_strategy_list: specifies the list of time axis strategy parameters, 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 is defined based on the partition range values for hot data caching. When certain time conditions are met, the data in the partition is determined to be hot data. When using the time axis strategy, note the following:

  • Only Range-partitioned tables (either primary or secondary) are supported, because time is needed to determine if the data has expired. Double Range partitions are not supported because it is unclear which Range partition's time to use.
  • When using the time axis strategy, the partition expression can only contain column names and does not support expression conversion. For example, PARTITION BY RANGE COLUMNS(expr(col3)) is not supported.
  • BOUNDARY_COLUMN must be a partition key. If there are multiple partition keys, BOUNDARY_COLUMN must be the first one to determine if the partition has expired.

  • BOUNDARY_COLUMN = column_name: specifies the column used to determine hot data. Supports integer types (BIGINT or INT, formatted as Unix timestamps) and time types (TIMESTAMP, DATE, DATETIME, or YEAR).

    • If BOUNDARY_COLUMN is an integer type, the table can be partitioned using Range or Range Columns.
    • If BOUNDARY_COLUMN is a time type, the table can only be partitioned using Range Columns.

  • BOUNDARY_COLUMN_UNIT = {"s" | "ms"}: specifies the time unit for the BOUNDARY_COLUMN parameter. This parameter can only be set when BOUNDARY_COLUMN is an integer type. When using an integer value as a timestamp, you must specify the timestamp unit to avoid incorrect parsing. Valid values:

    • If the partition column is INT, BOUNDARY_COLUMN_UNIT must be s.

    • If the partition column is BIGINT, 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 for hot data.

    • intnum specifies an integer.

    • retention_time_unit specifies the time unit, and can be one of the following values:

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

Examples

• Modify the hot cache of table tbl1 to be automatically identified by the system.

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

• Modify the hot cache of table tbl2 to retain data in partitions within 2 months from the current time, based on the value of column col3.

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

Modify the hot cache strategy of an index

You can use the ALTER TABLE statement to modify the hot cache strategy (STORAGE_CACHE_POLICY) of an existing index. The syntax is 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

Parameters

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

  • hot: specifies that the index is stored in hot storage. If the cache disk space is sufficient, all data in the index table will be cached to the local cache disk.
  • auto: specifies that the system automatically identifies hot data in the index table.
  • none: the default value. This option means that the index follows the STORAGE_CACHE_POLICY value of the primary table.

• timeline_strategy_list: specifies the list of time-axis strategy parameters. 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 partition data on the local cache disk based on the configured strategy. We recommend that you do not use this parameter on a local index.

  Note

  A time-axis strategy caches hot data based on the partition range. When a partition meets a certain time condition, its data is considered hot data. When you use a time-axis strategy, note the following:

  • A time-axis strategy is supported only for RANGE-partitioned tables (RANGE partitioning is supported at the primary or secondary level). This is because a time-axis strategy requires a time to determine whether data has expired. However, a time-axis strategy is not supported for RANGE-RANGE partitioning. This is because it is impossible to determine which RANGE partition to use for the time-axis strategy.
  • When you use a time-axis strategy, the partition expression must 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 has expired.

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

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

  • 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 timestamp may be incorrectly parsed. Valid values:

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

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

    • intnum: specifies an integer.

    • retention_time_unit: specifies the time unit. Valid values:

      • YEAR: Represents the year.
      • MONTH: Represents the month.
      • WEEK: Represents the week.
      • DAY: Represents the day.
      • HOUR: Represents the hour.
      • MINUTE: Represents the minute.

Example

Modify the hot data of the index idx2 to be automatically identified by the system.

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

References

• Overview of custom hot cache policies
• Create a table-level hot cache policy
• Create a partition-level hot cache policy
• Modify a partition-level hot cache policy
• ALTER TABLE
• Operations for custom hot cache policies