Overview

This topic describes the partitioning feature of OceanBase Database.

Partitioning feature

OceanBase Database can divide the data of a table into different blocks based on specific rules. Data in the same block is stored together in physical storage. This division of blocks is called partitioning. Each block is called a partition. In Oracle mode, a single table can have up to 65,536 partitions.

The following figure shows a table divided into five partitions distributed across two servers:

Each partition in the partitioned table can be further divided into multiple partitions based on specific rules. This type of table is called a subpartitioned table.

In each row of a table, a set of columns is used to determine which partition the row belongs to. This set of columns is called a partitioning key. A partitioning key must be a subset of the primary key. An expression that uses the partitioning key to determine which partition a row belongs to is called a partitioning expression.

Partitioning types

OceanBase Database supports the following partitioning types in Oracle mode:

• RANGE partitioning

• LIST partitioning

• HASH partitioning

• Composite partitioning

RANGE partitioning

RANGE partitioning maps data to partitions based on the partitioning key value ranges defined for each partition during table creation. It is a common partitioning type often used with date types. For example, you can partition a business log table by day, week, or month.

The partitioning key for RANGE partitioning consists of one or more columns. The partitioning key must be a column name and cannot be an expression. It supports numeric, string, and time types (excluding TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE), INTERVAL, and RAW types. It does not support BLOB and CLOB types.

LIST partitioning

LIST partitioning allows you to explicitly control how rows are mapped to partitions by specifying a list of discrete values for the partitioning key of each partition. This is different from RANGE and HASH partitioning. The advantage of LIST partitioning is that it allows you to easily partition unordered or unrelated data sets.

The partitioning key for LIST partitioning consists of one or more columns. The partitioning key must be a column name and cannot be an expression. It supports numeric, string, and time types (excluding TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE), INTERVAL, and RAW types. It does not support BLOB and CLOB types.

HASH partitioning

HASH partitioning is suitable for scenarios where RANGE and LIST partitioning methods cannot be used. It is simple to implement and distributes records to different partitions based on the hash values of the partitioning key. HASH partitioning is a good choice in the following cases:

• You cannot specify a list of partitioning key values for the data.

• The sizes of data in different ranges are very different and it is difficult to manually balance them.

• RANGE partitioning causes the data to be clustered.

• Performance features such as parallel DML, partition pruning, and partition-wise joins are important.

The partitioning key for HASH partitioning consists of one or more columns. The partitioning key must be a column name and cannot be an expression. It supports numeric, string, and time types (excluding TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE), INTERVAL, and RAW types. It does not support BLOB and CLOB types.

Composite partitioning

Composite partitioning typically involves using one partitioning strategy at the partition level and another strategy at the subpartition level. It is suitable for large business tables. Composite partitioning allows you to combine the advantages of multiple partitioning strategies.

Partition naming rules

For LIST and RANGE partitioned tables, the partition names are specified during table creation and are therefore the names specified at that time.

For HASH partitioned tables, if no partition names are specified during table creation, the system generates the partition names based on a naming rule. This is typically represented by names such as p0, p1, ..., pn.

For subpartitioned tables that use templates, the naming rule for subpartitions is ($part_name)s($subpart_name). For non-template subpartitioned tables, the subpartition names are custom-defined.