Create a partition-level storage cache policy

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

Considerations

You can specify a partition-level storage cache policy only in shared storage mode.

Syntax

When you create a partitioned table, you can specify the cache policy for a partition or a subpartition. To specify the STORAGE_CACHE_POLICY for a specific partition, use the following syntax:

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

Parameters

The STORAGE_CACHE_POLICY parameter specifies the cache policy for a partition. Valid values: hot, auto, cold, and none. The default value is none.

• hot: specifies that all data in the partition is hot data. If the cache disk space is sufficient, all data in the partition is cached to the local cache disk.
• auto: specifies that the system automatically identifies the hot data in the partition.
• cold: specifies that all data in the partition is cold data. The data is not cached to the local cache disk. For data that has been persisted to the cache, the system proactively triggers cache eviction. Subsequent table data can be stored in the macroblock memory cache without being persisted to the macroblock cache or microblock cache.
• none: specifies that the cache policy of the partition follows that of the parent partition. The default value is none.

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

Examples

• Specify the STORAGE_CACHE_POLICY for a partition when you create a partitioned table. The following example specifies the STORAGE_CACHE_POLICY for a partition named p1 in a partitioned table named t1.

  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 a subpartition when you create a partitioned table. The following example specifies the STORAGE_CACHE_POLICY for a subpartition named p1 in a partitioned table named t1.

  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 and cold storage cache policies
• Create a table-level storage cache policy
• Modify a table-level storage cache policy
• Modify a partition-level storage cache policy
• CREATE TABLE
• O&M guide for custom hot and cold storage cache policies