Partitioning strategies
OceanBase Database provides various partitioning strategies to control how data is distributed into partitions.
The basic partitioning strategies in OceanBase Database include RANGE, LIST, and HASH partitioning.
A partition can use only one data distribution method. For example, it can use only LIST partitioning or only RANGE partitioning.
When creating subpartitions, a table is first partitioned using one data distribution method, and then each partition is further divided into subpartitions using a second method. For example, if a table contains the create_time and user_id columns, you can use RANGE partitioning on the create_time column and then use HASH partitioning on the user_id column for subpartitioning.
RANGE partitioning
RANGE partitioning is the most common partition type and is often used with dates. In RANGE partitioning, the database maps rows to partitions based on the range of values of the partitioning key.
Each RANGE partition includes a VALUES LESS THAN clause that specifies the non-inclusive upper limit of the partition. Any value of the partitioning key that is equal to or greater than this value is added to the next higher partition. Except for the first partition, all partitions have an implicit lower limit specified by the VALUES LESS THAN clause of the preceding partition.
RANGE partitioning supports defining MAXVALUE for the highest partition. MAXVALUE represents a virtual infinite value that sorts higher than any other possible value of the partitioning key, including null values.
INTERVAL partitioning
INTERVAL partitioning extends RANGE partitioning by allowing new partitions to be automatically created when newly inserted data exceeds the range of existing partitions.
HASH partitioning
In HASH partitioning, the database maps rows to partitions based on the hash algorithm applied to the user-specified partitioning key. The target partition for a row is determined by calculating a hash value using an internal hash function, and then identifying the corresponding partition based on the number of HASH partitions. When the number of partitions is a power of two, the hash algorithm creates a roughly even distribution of rows across all partitions. The hash algorithm evenly distributes rows among partitions, making their sizes approximately equal.
HASH partitioning is an ideal method for distributing data evenly across nodes. It is also a convenient alternative to RANGE partitioning, especially when the data to be partitioned is not historical or lacks an obvious partitioning key.
HASH partitioning is particularly useful in OLTP systems with high update conflicts. This is because HASH partitioning divides a table into several partitions, distributing modifications to different partitions rather than modifying the entire table.
LIST partitioning
In LIST partitioning, the database uses a list of discrete values as the partitioning key for each partition. The partitioning key consists of one or more columns.
You can use LIST partitioning to control how individual rows are mapped to specific partitions.
When sorting by the partitioning key is inconvenient, you can use LIST partitioning to group and manage data.
Composite partitioning
RANGE, LIST, and HASH partitioning can all be used as subpartitioning strategies for composite partitioned tables.
In composite partitioning, a table is first partitioned using one data distribution method, and then each partition is further subdivided into subpartitions using a second method. Thus, composite partitioning combines basic data distribution methods. All subpartitions of a specified partition represent logical subsets of the data.
Composite partitioning has the following advantages:
Partition pruning in one or two dimensions based on SQL statements can improve performance.
Queries can use full partitions or partial partitions for joins in either dimension.
You can perform parallel backup and restore on a single table.
The number of partitions is greater than that of single-level partitions, which may benefit parallel execution.
You can implement a rolling window to support historical data. If many statements benefit from partition pruning or partition joins, you can still partition in another dimension.
You can store data in different ways based on the identifier of the partitioning key. For example, you may decide to store data of a specific product type in a read-only compressed format and keep the data of other product types uncompressed.
The following figure shows combined partitioning using RANGE-HASH and RANGE-LIST.
Random partitioning
Random partitioning is a new partitioning strategy provided by OceanBase Database, suitable for scenarios where data routing based on specific column values is not required. This strategy automatically distributes data randomly across multiple partitions by the system, achieving uniform data distribution and load balancing. Compared with traditional partitioning methods based on business fields, users do not need to specify a partitioning key or manually manage the partition structure, reducing the complexity of use.