Modify the partition-level hot cache policy

This topic describes how to modify the partition-level hot cache policy (STORAGE_CACHE_POLICY) by using SQL statements.

Considerations

You can modify the partition-level hot cache policy only in shared storage mode.

Modify the partition-level hot cache policy

After you create a partitioned table, you can use the ALTER TABLE statement to specify the STORAGE_CACHE_POLICY when you add a partition or to modify the STORAGE_CACHE_POLICY of a partition. The syntax is as follows:

• Specify the STORAGE_CACHE_POLICY when you add a partition:

  ALTER TABLE table_name
      ADD PARTITION (partition_list [STORAGE_CACHE_POLICY = {"hot" | "auto" | "none"}]);
  

• Modify the STORAGE_CACHE_POLICY of a partition:

  ALTER TABLE table_name
      ALTER PARTITION (partition_list [STORAGE_CACHE_POLICY = {"hot" | "auto" | "none"}]);
  

Parameter description

The STORAGE_CACHE_POLICY at the partition level is optional and can be set to the following values:

• hot: All data in the specified partition is considered 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: The default value. The hot cache policy of the partition follows the value of STORAGE_CACHE_POLICY at the upper level.

For more information about how to modify a partitioned table, see Add a partition and Modify a partition rule.

Examples

• Add a partition p2 to the tbl1 table. The system automatically identifies the hot data in partition p2 that needs to be cached to the local disk.

  ALTER TABLE tbl1 ADD PARTITION (PARTITION p2 VALUES LESS THAN(300) STORAGE_CACHE_POLICY = "auto");
  

• Change the data in the primary partition p1 of the tbl1 table to hot data.

  ALTER TABLE tbl1 ALTER PARTITION p1 STORAGE_CACHE_POLICY = "hot";
  

• Change the data in the subpartition sp3 of the tbl2 table to hot data that is automatically identified by the system.

  ALTER TABLE tbl2 ALTER SUBPARTITION sp3 STORAGE_CACHE_POLICY = "auto";
  

References

• Overview of custom hotspot caching strategies
• Create a table-level hotspot caching strategy
• Create a partition-level hotspot caching strategy
• Modify a table-level hotspot caching strategy
• ALTER TABLE
• Operations for custom hotspot caching strategies