Create a macro-block-level hot and cold storage strategy

This topic describes how to create a macro-block-level hot and cold storage strategy by using an SQL statement without partitioning.

Procedure

To create a macro-block-level hot and cold storage strategy, set GRANULARITY = "block" in the STORAGE_CACHE_POLICY syntax. For more information, see Create a table-level hot and cold storage strategy.

Currently, you can create a macro-block-level hot and cold storage strategy by using the system time column scheme or the custom time column scheme.

System time column scheme

Considerations

After a large-scale structural change (such as DDL operations, direct load, or index creation), the system may identify too many hot data blocks in the short term, which may cause the real hot data blocks to be evicted.

Features

The system uses the internal time column to determine the hot and cold storage of data. In this case, the system determines the hot and cold storage of data based on the actual write time of data. This scheme is suitable for scenarios where you cannot provide a business time column or need a ready-to-use solution.

Example

Create a table named tbl1 and create a macro-block-level hot and cold storage strategy based on the system time column. In this case, you do not need to specify the BOUNDARY_COLUMN parameter in the STORAGE_CACHE_POLICY clause.

obclient> CREATE TABLE tbl1 (
    col1 INT,
    col2 DATE NOT NULL,
    col3 VARCHAR(100))
    STORAGE_CACHE_POLICY (
        HOT_RETENTION = 6 DAY,
        GRANULARITY = 'block');

Custom time column scheme

Considerations

If the custom time column cannot be used to filter data effectively, the system cannot perform preheating, and the accuracy of hot data block identification decreases.

Features

The system uses a user-specified business time column to determine the validity period of hot data blocks.

Recommendations

This scheme is suitable for scenarios where you need more precise control over the hot data block range and the business time column is stable and reliable.

Example

Create a table named tbl2 and create a macro-block-level hot and cold storage strategy based on the custom time column col2.

obclient> CREATE TABLE tbl2 (
    col1 INT,
    col2 DATE NOT NULL,
    col3 VARCHAR(100))
    STORAGE_CACHE_POLICY (
        BOUNDARY_COLUMN = 'col2',
        HOT_RETENTION = 6 DAY,
        GRANULARITY = 'block');

Considerations for other orthogonal scenarios

• Hot data blocks are not included in the backup and restore process. After restore, hot data blocks are identified based on the current strategy. If you do not want the strategy to affect the restore process, you can disable the manual strategy by setting the tenant parameter enable_manual_storage_cache_policy to off.
• When you update data in a primary table, the internal time column of an index does not change. To precisely control the hot and cold status of an index, we recommend that you manually set a strategy for the index.

References

• CREATE TABLE
• CREATE INDEX