Create a table-level hot cache strategy

This topic describes how to create a table-level hot cache strategy (STORAGE_CACHE_POLICY) by using SQL statements:

• Create a hot cache strategy for a regular table.
• Create a hot cache strategy for an index table.

Considerations

You can specify the hot cache strategy when you create a table/index in shared storage mode.

Specify the hot cache strategy for a common table

You can specify the table-level hot cache strategy by adding the STORAGE_CACHE_POLICY clause to the CREATE TABLE statement. The syntax is as follows:

CREATE TABLE table_name (table_definition_list)
    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

STORAGE_CACHE_POLICY is an optional clause. If you do not specify the STORAGE_CACHE_POLICY clause, the default value of the tenant-level parameter default_storage_cache_policy is used. The parameters in storage_cache_policy_option are defined in the key-value format. The semantics of the parameters are as follows:

• GLOBAL = {"hot" | "auto"}: specifies the hot cache strategy for 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 a strategy that caches hot data in partitions based on the partition range defined in a RANGE partition. When a certain time condition is met, the data in the partition is considered hot data. When you use a time-axis strategy, note the following:

  • Only RANGE partitioned tables are supported. RANGE partitions can be at the first or second level. This is because time is used to determine whether data has expired, but dual RANGE partitions cannot be used because it is impossible to determine which RANGE partition's time to use.
  • 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 has expired.

  • BOUNDARY_COLUMN = column_name: specifies the column used to determine hot data. The column can be of the integer type (BIGINT or INT, in the Unix timestamp format) or the time type (TIMESTAMP, DATE, DATETIME, or YEAR).

    • If the BOUNDARY_COLUMN is of the integer type, the table can be partitioned by RANGE or RANGE COLUMNS.
    • If the BOUNDARY_COLUMN is of the time type, the table can be partitioned by RANGE COLUMNS only.

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

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

    • If the partitioning column is of the BIGINT type, the BOUNDARY_COLUMN_UNIT can be set to 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. Valid values:

      • YEAR: specifies a year.
      • MONTH: specifies a month.
      • WEEK: specifies a week.
      • DAY: specifies a day.
      • HOUR: specifies an hour.
      • MINUTE: specifies a minute.

Examples

• Specify hot data manually.

  CREATE TABLE tbl1 (
      col1 INT, 
      col2 INT)
      STORAGE_CACHE_POLICY (GLOBAL = "hot");
  

• Specify hot data by using a time-axis strategy.

  CREATE TABLE tbl2 (
      col1 BIGINT NOT NULL,
      col2 VARCHAR(50),
      col3  DATE NOT NULL)
      STORAGE_CACHE_POLICY (
          BOUNDARY_COLUMN = col3,
          HOT_RETENTION = 1 MONTH)
      PARTITION BY RANGE COLUMNS(col3) (
          PARTITION M202001 VALUES LESS THAN('2020/02/01'),
          PARTITION M202002 VALUES LESS THAN('2020/03/01'),
          PARTITION M202003 VALUES LESS THAN('2020/04/01'),
          PARTITION MMAX VALUES LESS THAN MAXVALUE);
  

Create a hotspot cache strategy for the index table

You can specify a hot cache strategy for an index as follows:

• When creating an index, specify the table-level hot cache policy for the index.
• When you create an index, specify a hot cache strategy at the table level.
• When adding an index to an existing table, you can specify the table-level hot cache policy for the index.

For more information about creating indexes, see Create an Index.

Specify the table-level hot cache strategy for an index when you create it.

The syntax for this statement is as follows:

CREATE INDEX index_name ON table_name (column_name)
    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

Create an index and specify the table-level hot cache strategy for an index when you create the table

The syntax is as follows:

CREATE TABLE table_name (
    column_name column_definition,
    [column_name column_definition,...],
    INDEX index_name(column_name) 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

Specify a hot cache strategy at the table level when you add an index to an existing table

The syntax format is as follows:

ALTER TABLE table_name ADD INDEX index_name(column_name)
    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

Support configure hot cache strategy for index table:

• For local indexes, you can specify the table-level STORAGE_CACHE_POLICY. If you do not specify the table-level STORAGE_CACHE_POLICY, the default is none, which means that the hot cache strategy for local index partitions follows the partitioning rules of the data table. The hot cache strategy for local index partitions is determined by the partitioning rules of the data table.

• For global indexes, in addition to specifying the STORAGE_CACHE_POLICY at the table level, you can also specify the STORAGE_CACHE_POLICY at the partition level for the indexed table, if partitioning is used. If the table-level STORAGE_CACHE_POLICY is not specified, it defaults to none.

  Note

  For global indexes, in V4.4.1 and later, if the table-level STORAGE_CACHE_POLICY is not specified, it follows the STORAGE_CACHE_POLICY of the primary table if the primary table uses the Global storage cache policy. If the primary table uses the timeline storage cache policy, the global index defaults to the hot storage cache policy.

When setting the STORAGE_CACHE_POLICY table attribute, the different attributes in the storage_cache_policy_option parameter are defined in Key-Value format. The attributes have the following semantic meanings:

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

  • hot: Specifies the index as a hot storage. If there is enough space on the cache disk, all the data in the index table will be cached on the local cache disk.
  • auto: Indicates that the system automatically identifies the hot data in the specified index table.
  • none : default. The index strategy inherits the strategy set for the primary table STORAGE_CACHE_POLICY. This strategy is not recommended. We recommend that you set the STORAGE_CACHE_POLICY attribute of the index.

• timeline_strategy_list: This indicates the time-axis strategy parameters list, with each parameter separated by a comma. The hotspot cache time-axis strategy supports determining whether partitioned data is hot based on time. The system then automatically adjusts the partitioned data on the local cache disk based on the configured strategies. It is not recommended to use this parameter on local indexes.

  Note

  Time-based partitioning: Data is cached in hot data partitions based on the partition range definition. Data from partitions is determined to be hot based on a specified time condition. When using the time-based partitioning strategy, consider the following:

  • Only RANGE partitions (either primary or sub-partitioned) are supported. Double RANGE partitions are not supported because it's unclear which RANGE partition's time to use for determining data freshness.
  • Time-based partitioning allows only column names in the partition expression, not expressions. Examples include PARTITION BY RANGE COLUMNS(expr(col3)).
  • BOUNDARY_COLUMN must be the first partition key column in multi-column partition keys to determine if data has expired.

  • BOUNDARY_COLUMN = column_name: The column for determining hot data. Supported data types include integers (BIGINT or INT, formatted as Unix timestamps) and datetime types (TIMESTAMP, DATE, DATETIME, or YEAR).

    • If the type of the BOUNDARY_COLUMN column is INT, the range and range columns partitioning types are supported.
    • If the type of BOUNDARY_COLUMN is a datetime type, the partitioning type of the table must be Range Columns partitioning.

  • BOUNDARY_COLUMN_UNIT = {"s" | "ms"}: Specifies the time unit for the BOUNDARY_COLUMN parameter. This parameter is supported only when the BOUNDARY_COLUMN parameter is an integer. If an integer is used as a timestamp, the time unit must be specified to avoid parsing errors. Valid values:

    • When the partitioning column is of the INT type, the value of BOUNDARY_COLUMN_UNIT can only be s.
    • The value of the BOUNDARY_COLUMN_UNIT parameter can be s or ms when the partitioning column is of the BIGINT type.

  • HOT_RETENTION = intnum retention_time_unit : The time duration for hot data.

    • intnum: Represents an integer.

    • retention_time_unit: specifies the time unit. Valid values:

      • YEAR : the year.
      • MONTH : indicates month.
      • WEEK: specifies a week.
      • DAY: specifies days.
      • HOUR: Expresses hours.
      • MINUTE: Indicates the minutes.

Example

• Specify the STORAGE_CACHE_POLICY table-level option when creating an index:

  1. Create a table named tbl3.

     CREATE TABLE tbl3 (col1 INT, col2 INT, col3 INT);
     

  2. Create the index idx1_tbl3 and specify the index table data as hot data.

     CREATE INDEX idx1_tbl3 ON tbl3 (col1) LOCAL 
         STORAGE_CACHE_POLICY (GLOBAL = "hot");
     

• Create an index table when creating a table, and specify the data in the index table as hot data.

  CREATE TABLE tbl4 (col1 INT, col2 INT,
      INDEX idx2 ((col1 + 1)) STORAGE_CACHE_POLICY (GLOBAL = "hot"), UNIQUE KEY ((col1 + col2)));
  

• When an index is created on an existing table, the index table is specified as a hot table.

  ALTER TABLE tbl4 ADD INDEX idx3(col1, col2) GLOBAL 
      STORAGE_CACHE_POLICY (GLOBAL = "hot");
  

References

• Overview of custom hot cache policies
• Modify the table-level hot cache policy
• Modify the partition-level hot cache policy
• CREATE TABLE
• ALTER TABLE
• CREATE INDEX
• Operations on hot cache policies