Create a partition-level hot cache strategy

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

Considerations

You can specify the partition-level hot cache strategy when you create a table in shared storage mode.

Syntax

When you create a partitioned table, you can specify the cache strategy for the partitions or subpartitions. You need to add the STORAGE_CACHE_POLICY parameter to the specific partitions. The syntax is as follows:

CREATE TABLE table_name (table_definition_list)
    partition_option
        (partition_list [STORAGE_CACHE_POLICY = {"hot" | "auto" | "none"}])
    subpartition_option
        (subpartition_list [STORAGE_CACHE_POLICY = {"hot" | "auto" | "none"}]);

Parameters

The STORAGE_CACHE_POLICY parameter for the partitions is optional. Valid values are as follows:

• hot: All data in the specified partition is hot data. If the cache disk space is sufficient, all data in the partition will be cached to the local cache disk.
• auto: The hot data in the specified partition is automatically identified by the system.
• none: Default value. The hot cache strategy for this partition follows the value of STORAGE_CACHE_POLICY at the upper level.

For more information about how to create a partitioned table, see Create a partitioned table.

Examples

• Specify the STORAGE_CACHE_POLICY for the primary partitions when you create a partitioned table.

  CREATE TABLE tbl1 (col1 INT, col2 INT, col3 INT)
      PARTITION BY RANGE(col1)
      SUBPARTITION BY KEY(col2, col3) SUBPARTITIONS 5
          (PARTITION p0 VALUES LESS THAN(0) STORAGE_CACHE_POLICY = "hot",
           PARTITION p1 VALUES LESS THAN(100) STORAGE_CACHE_POLICY = "auto"
          );
  

• Specify the STORAGE_CACHE_POLICY for the subpartitions when you create a partitioned table.

  CREATE TABLE tbl2 (col1 INT, col2 INT)
      PARTITION BY RANGE(col1)
      SUBPARTITION BY RANGE(col2)
          (PARTITION p0 VALUES LESS THAN(100)
              (SUBPARTITION sp0 VALUES LESS THAN(2020) STORAGE_CACHE_POLICY = "hot",
               SUBPARTITION sp1 VALUES LESS THAN(2021)),
          PARTITION p1 VALUES LESS THAN(200)
              (SUBPARTITION sp2 VALUES LESS THAN(2020),
               SUBPARTITION sp3 VALUES LESS THAN(2021) STORAGE_CACHE_POLICY = "hot",
               SUBPARTITION sp4 VALUES LESS THAN(2022))
          );
  

References

• Overview of custom hot spot caching policies
• Create a table-level hot spot caching policy
• Modify a table-level hot spot caching policy
• Modify a partition-level hot spot caching policy
• CREATE TABLE
• Operations for custom hot spot caching policies