What is RANGE partitioning - PolarDB - Alibaba Cloud Documentation Center

Range partitioning divides a table or index into segments using boundary values. It is the standard approach for time-series data and large tables that need regular data rotation—allowing the query optimizer to skip irrelevant segments entirely and letting you add or remove data at the partition level rather than row by row.

When range partitioning fits

Accelerate range queries with partition pruning

When large tables are frequently queried by date or numeric range—such as an orders table (ORDER) or a purchase schedule (LINEITEM)—range partitioning lets the query optimizer skip irrelevant partitions entirely. This technique is called partition pruning.

For example, if you partition an orders table by year and query data from a single year, the optimizer accesses one partition instead of scanning all partitions. On a table with eight years of data, a single-year query touches one partition out of eight—delivering a proportional reduction in I/O and scan time.

Simplify data rotation with a tumbling window

Range partitioning is the standard approach for maintaining a rolling data window—for example, keeping the last 36 months of data. Adding a new month and dropping the oldest works at the partition level, not the row level:

Load the new month's data into a staging table.
Clean and index the staging table.
Run EXCHANGE PARTITION to swap the staging table into the partitioned table. The partitioned table remains queryable throughout.
Run DROP PARTITION to remove the oldest month's partition.

Operating on whole partitions is far more efficient than deleting individual rows or rebuilding indexes across the full table.

Divide large tables for manageability

Backup and restoration operations on a single large table can exceed maintenance windows. Range partitioning splits the table into smaller logical blocks, allowing you to back up, restore, or archive individual partitions independently.

Range partitioning example

The following example shows an orders table that spans more than nine years. The table is partitioned by the o_orderdate column into eight annual partitions (plus an overflow partition). This layout supports both short-interval sales analysis through partition pruning and rolling window data management.

CREATE TABLE `orders` (
  `o_orderkey` int(11) NOT NULL,
  `o_custkey` int(11) NOT NULL,
  `o_orderstatus` char(1) DEFAULT NULL,
  `o_totalprice` decimal(10,2) DEFAULT NULL,
  `o_orderDATE` date NOT NULL,
  `o_orderpriority` char(15) DEFAULT NULL,
  `o_clerk` char(15) DEFAULT NULL,
  `o_shippriority` int(11) DEFAULT NULL,
  `o_comment` varchar(79) DEFAULT NULL,
  PRIMARY KEY (`o_orderkey`,`o_orderDATE`,`o_custkey`),
  KEY `o_orderkey` (`o_orderkey`),
  KEY `i_o_custkey` (`o_custkey`),
  KEY `i_o_orderdate` (`o_orderDATE`)
) ENGINE=InnoDB
PARTITION BY RANGE (TO_DAYS(o_orderdate))
(PARTITION item1 VALUES LESS THAN (TO_DAYS('1992-01-01')),
 PARTITION item2 VALUES LESS THAN (TO_DAYS('1993-01-01')),
 PARTITION item3 VALUES LESS THAN (TO_DAYS('1994-01-01')),
 PARTITION item4 VALUES LESS THAN (TO_DAYS('1995-01-01')),
 PARTITION item5 VALUES LESS THAN (TO_DAYS('1996-01-01')),
 PARTITION item6 VALUES LESS THAN (TO_DAYS('1997-01-01')),
 PARTITION item7 VALUES LESS THAN (TO_DAYS('1998-01-01')),
 PARTITION item8 VALUES LESS THAN (TO_DAYS('1999-01-01')),
 PARTITION item9 VALUES LESS THAN (MAXVALUE));

Run EXPLAIN to confirm partition pruning. A query on o_orderDATE = '1992-03-01' should access only item2:

EXPLAIN SELECT * FROM orders WHERE o_orderDATE = '1992-03-01';

+----+-------------+--------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | orders | item2      | ref  | i_o_orderdate | i_o_orderdate | 3       | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+---------------+---------+-------+------+----------+-------+

Due to the limits of the RANGE type, the original DDL statement may not be displayed after you partition the table by using TO_DAYS() and execute the SHOW CREATE TABLE statement. Partition boundaries may appear as integer day values rather than date strings. This is a known display limitation and does not affect query behavior.

SHOW CREATE TABLE orders;

CREATE TABLE `orders` (
  ...
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (to_days(`o_orderDATE`))
(PARTITION item1 VALUES LESS THAN (727563),
 PARTITION item2 VALUES LESS THAN (727929),
 PARTITION item3 VALUES LESS THAN (728294),
 PARTITION item4 VALUES LESS THAN (728659),
 PARTITION item5 VALUES LESS THAN (729024),
 PARTITION item6 VALUES LESS THAN (729390),
 PARTITION item7 VALUES LESS THAN (729755),
 PARTITION item8 VALUES LESS THAN (730120),
 PARTITION item9 VALUES LESS THAN MAXVALUE) */

Range columns partitioning

Range columns partitioning extends range partitioning in three ways:

Column names only: RANGE COLUMNS() accepts column names, not expressions.
Multi-column support: RANGE COLUMNS() can include one or more columns. Partition placement is determined by tuple comparison rather than scalar value comparison.
Broader type support: Partition key columns can be of the INTEGER, STRING, DATE, or DATETIME type.

CREATE TABLE `orders` (
  `o_orderkey` int(11) NOT NULL,
  `o_custkey` int(11) NOT NULL,
  `o_orderstatus` char(1) DEFAULT NULL,
  `o_totalprice` decimal(10,2) DEFAULT NULL,
  `o_orderDATE` date NOT NULL,
  `o_orderpriority` char(15) DEFAULT NULL,
  `o_clerk` char(15) DEFAULT NULL,
  `o_shippriority` int(11) DEFAULT NULL,
  `o_comment` varchar(79) DEFAULT NULL,
  PRIMARY KEY (`o_orderkey`,`o_orderDATE`,`o_custkey`),
  KEY `o_orderkey` (`o_orderkey`),
  KEY `i_o_custkey` (`o_custkey`),
  KEY `i_o_orderdate` (`o_orderDATE`)
) ENGINE=InnoDB
PARTITION BY RANGE COLUMNS(o_orderdate)
(PARTITION item1 VALUES LESS THAN ('1992-01-01'),
 PARTITION item2 VALUES LESS THAN ('1993-01-01'),
 PARTITION item3 VALUES LESS THAN ('1994-01-01'),
 PARTITION item4 VALUES LESS THAN ('1995-01-01'),
 PARTITION item5 VALUES LESS THAN ('1996-01-01'),
 PARTITION item6 VALUES LESS THAN ('1997-01-01'),
 PARTITION item7 VALUES LESS THAN ('1998-01-01'),
 PARTITION item8 VALUES LESS THAN ('1999-01-01'),
 PARTITION item9 VALUES LESS THAN (MAXVALUE));