When to Use Range Partitioning

Range partitioning is useful for organizing similar data, especially date and time data.

Range partitioning is a convenient way to partition historical data. The boundaries of a range partition define the order of partitions in a table or index

Most SQL statements that access range partitions focus on time ranges. For example, SQL statements select data from a specific time period. If each partition represents a month of data, the amount of data scanned is reduced to a fraction of the total. This optimization method is called partition pruning.

Range partitions are also useful when you need to periodically load new data and clean up old data, because range partitions are easy to add or remove. For example, the system typically keeps a scrolling data window that keeps data online for the last 36 months. Range partitioning simplifies this process.

In summary, you can consider using range partitioning when:

• It is often necessary to perform range predicate scans on easily partitioned columns in large tables.
• You want to maintain a scrolling data window.
• Large tables cannot be managed within a specified time range, such as backup and restoration, but can be divided into smaller logical blocks based on partition range columns.

Example 1 Creating a range-partitioned table

CREATE TABLE sales_table
(
    order_no              INTEGER          NOT NULL,
    goods_name            CHAR(20)         NOT NULL,
    sales_date            DATE             NOT NULL,
    sales_volume          INTEGER,
    sales_store           CHAR(20)
)
PARTITION BY RANGE(sales_date)
(
        PARTITION season1 VALUES LESS THAN('2021-04-01 00:00:00'),
        PARTITION season2 VALUES LESS THAN('2021-07-01 00:00:00'),
        PARTITION season3 VALUES LESS THAN('2021-10-01 00:00:00'),
        PARTITION season4 VALUES LESS THAN(MAXVALUE)
);