Overview

This topic describes the basic concepts and types of partitioning in OceanBase Database.

Basic concepts

OceanBase Database can split the data of a table into different groups based on some rules. Data in the same group is stored in the same physical area. This area is called a partition. A table split in this way is called a partitioned table. In Oracle mode of OceanBase Database, you can create at most 65,536 partitions in a single table.

Each partition can be divided into subpartitions based on some rules. A table that contains both partitions and subpartitions is called a subpartitioned table.

In a table row, a column determines which partition the row belongs to. The set of data in this column is called the partitioning key. The partitioning key must be a subset of a primary key or a unique key. An expression contains the partitioning key and determines which partitions the rows belong to. This expression is called a partitioning expression.

Partitioning types

OceanBase Database in Oracle mode supports the following partitioning types:

• RANGE partitioning

• LIST partitioning

• HASH partitioning

• Composite partitioning

RANGE partitioning

RANGE partitioning maps data to partitions based on ranges of partitioning key values that you set up for each partition when you define the partitioned table. It is the most common partitioning type and is often used with dates. For example, you can partition business log tables by day, week, or month.

LIST partitioning

Unlike RANGE partitioning and HASH partitioning, LIST partitioning enables you to explicitly control how rows map to partitions by specifying a list of discrete values for the partitioning key in the description for each partition. The advantage of LIST partitioning is that you can partition unordered and unrelated data.

HASH partitioning

HASH partitioning applies to scenarios where RANGE partitioning or LIST partitioning cannot be used. HASH partitioning enables easy partitioning of data by distributing records over partitions based on a HASH function on the partitioning key. HASH partitioning is a better choice in the following cases:

• You cannot identify an obvious partitioning key for the data.

• The sizes of range partitions differ substantially or are difficult to balance manually.

• RANGE partitioning can cause the data to be undesirably clustered.

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

Composite partitioning

Composite partitioning partitions a table using one partitioning strategy and partitions each partition using a different partitioning strategy. It is suitable for business tables containing large amounts of data. Composite partitioning gives full play to the advantages of the two partitioning strategies that you use in combination.

Partition naming conventions

For a LIST- or RANGE-partitioned table, the partition names are specified when the table is created.

For a HASH-partitioned table, if no partition names are specified when the table is created, the system generates the names based on the following naming conventions: p0, p1, ..., and pn.

For a template-based subpartitioned table, the subpartitions are named in the ($part_name)s($subpart_name) format. For a non-template-based subpartitioned table, the subpartitions are named by yourself.