This topic describes how to create a partition-level hot/cold storage policy (STORAGE_CACHE_POLICY) by using SQL statements.
Note
OceanBase AI Database supports specifying a partition-level hot/cold storage policy when creating a table.
Syntax
When you create a partitioned table, you can specify the caching policy for a partition or subpartition. You must specify STORAGE_CACHE_POLICY for a specific partition. The syntax is as follows:
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"}]);
Related parameters
The partition-level STORAGE_CACHE_POLICY is optional. Valid values are as follows:
hot: Indicates that all data in the specified partition is hot data. If sufficient cache disk space is available, all data in the partition will be cached to the local cache disk.auto: Indicates that the system automatically identifies hot data in the specified partition.cold: Indicates that the data in the specified partition is cold data and will not be cached to the local cache disk. For data already persisted to the cache, cache eviction is actively triggered. Subsequent accesses to the partition data can be stored in the macroblock memory cache and will not be persisted to the macroblock cache.none: The default value, indicating that the hot/cold storage policy of this partition follows theSTORAGE_CACHE_POLICYvalue of its parent partition.
For more information about creating a partitioned table, see Create a partitioned table.
Examples
Specify
STORAGE_CACHE_POLICYfor a partition 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
STORAGE_CACHE_POLICYfor a subpartition 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)) );