Create a table-level hot cache policy

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

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

Considerations

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

Create a hot cache policy for a regular table

You can specify the hot cache policy for a regular table when you create the table. Add the STORAGE_CACHE_POLICY option 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 parameter. If you do not specify the STORAGE_CACHE_POLICY parameter, the value of the tenant-level parameter default_storage_cache_policy is used by default. The attributes in storage_cache_policy_option are defined in the key-value format. The following table describes the attributes.

* When the partitioning column is of the `INT` type, the value of `BOUNDARY_COLUMN_UNIT` can only be `s`.
* When the partitioning column is of the `BIGINT` type, the value of `BOUNDARY_COLUMN_UNIT` can be `s` or `ms`.

  <main id="notice" type='notice'>
    <h4>Notice</h4>
    <p>If the timestamp is not in the Unix format, the system cannot correctly interpret the timestamp represented by the <code>INT</code> type. </p>
  </main>

• 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 in manual mode.

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

• Specify hot data in time series mode.

  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);
  

Hot cache strategy for index tables

You can specify the hot cache strategy for an index table in the following ways:

• When you create an index, specify the hot cache strategy for the index table.
• When you create a table, create an index and specify the hot cache strategy for the index table.
• When you add an index to an existing table, specify the hot cache strategy for the index table.

For more information about how to create an index, see Create an index.

Specify the table-level hot cache policy for an index when creating the index

The syntax 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

Specify the table-level hot cache policy for an index when creating 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 the table-level hot cache strategy for an existing index.

The syntax 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

Parameter Description

You can configure the hot cache strategy for the index table.

• For a local index, you can specify the STORAGE_CACHE_POLICY at the table level. If you do not specify the STORAGE_CACHE_POLICY at the table level, the default value is none, which means that the hot cache strategy of the partitions of the local index table follows that of the data table.

• For a global index, you can specify the STORAGE_CACHE_POLICY at the table level. If the index table is partitioned, you can also specify the STORAGE_CACHE_POLICY at the partition level. If you do not specify the STORAGE_CACHE_POLICY at the table level, the default value is none.

  Note

  In V4.4.1 and later, if you do not specify the STORAGE_CACHE_POLICY at the table level for a global index, the value of the STORAGE_CACHE_POLICY of the global index follows that of the primary table if the primary table uses the Global strategy. If the primary table uses the TimeLine strategy, the default value of the STORAGE_CACHE_POLICY of the global index is hot.

When you set the STORAGE_CACHE_POLICY at the table level, you define different attributes in the storage_cache_policy_option parameter in the key-value format. The attributes are described as follows:

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

  • hot: specifies that the index is hot-stored. If the cache disk space is sufficient, all data in the index table is cached to the local cache disk.
  • auto: specifies that the hot data of the index table is automatically identified by the system.
  • none: the default value. The strategy of the index follows that of the primary table's STORAGE_CACHE_POLICY.

• timeline_strategy_list: specifies the list of parameters for the time-based hot cache strategy. Parameters are separated by commas. The time-based hot cache strategy determines whether the data of a partition is hot data based on the time. The system automatically adjusts the partitions cached on the local cache disk based on the configured strategy. We recommend that you do not use this parameter for a local index.

  Note

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

  • Only range-partitioned tables can use the time-based hot cache strategy. A range partition can be a primary or secondary partition. However, a double range partition cannot be used because it is impossible to determine which range partition to use for the time-based hot cache strategy.
  • When you use the time-based hot cache strategy, the partitioning expression can contain only column names and cannot contain expressions. For example, PARTITION BY RANGE COLUMNS(expr(col3)) is invalid.
  • 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: the column used to determine the hot data. Supported types include integer types (BIGINT or INT, formatted as Unix timestamps) and time types (TIMESTAMP, DATE, DATETIME, or YEAR).

    • If the BOUNDARY_COLUMN is an integer type, the table supports Range and Range Columns partitioning.
    • If the BOUNDARY_COLUMN is a time type, the table only supports Range Columns partitioning.

  • 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 an integer value is used as a timestamp, the time unit must be specified to avoid incorrect parsing. Valid values are:

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

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

    • intnum: an integer.

    • retention_time_unit: the time unit, with valid values:

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

Examples

• When creating an index, specify the STORAGE_CACHE_POLICY at the table level:

  1. Create the tbl3 table.

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

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

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

• When creating a table and an index, specify the index table data as hot data.

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

• When adding an index to an existing table, specify the index table data as hot data.

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

References

• Overview of custom hot spot caching strategies
• Create a partition-level hot spot caching strategy
• Modify a table-level hot spot caching strategy
• Modify a partition-level hot spot caching strategy
• CREATE TABLE
• ALTER TABLE
• CREATE INDEX
• Operations on hot spot caching strategies