This topic describes how to modify a partition-level hot/cold storage policy (STORAGE_CACHE_POLICY) by using SQL statements.
Modify a partition-level hot/cold storage policy
After a partitioned table is created, you can use the ALTER TABLE statement to specify the STORAGE_CACHE_POLICY when adding a partition or to modify the STORAGE_CACHE_POLICY of an existing partition. The syntax is as follows:
Specify
STORAGE_CACHE_POLICYwhen adding a partition:ALTER TABLE table_name ADD PARTITION (partition_list [STORAGE_CACHE_POLICY = {"hot" | "auto" | "cold" | "none"}]);Modify the
STORAGE_CACHE_POLICYof a partition:ALTER TABLE table_name ALTER PARTITION partition_name [, partition_name ...] STORAGE_CACHE_POLICY = {"hot" | "auto" | "cold" | "none"};
Related parameters
The STORAGE_CACHE_POLICY at the partition level 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.
For more information about modifying a partitioned table, see Add a partition and Modify partitioning rules.
Examples
Add a new partition
p2to tabletbl1. The system automatically identifies the hot data that needs to be cached to the local disk in partitionp2.ALTER TABLE tbl1 ADD PARTITION (PARTITION p2 VALUES LESS THAN(300) STORAGE_CACHE_POLICY = "auto");Modify the data in the existing partition
p1of tabletbl1to be hot data.ALTER TABLE tbl1 ALTER PARTITION p1 STORAGE_CACHE_POLICY = "hot";Modify the data in subpartition
sp3of tabletbl2so that the system automatically identifies the data that needs to be cached to the local disk.ALTER TABLE tbl2 ALTER SUBPARTITION sp3 STORAGE_CACHE_POLICY = "auto";