Partitioning strategies

This topic describes the partitioning strategies in OceanBase Database.

OceanBase Database supports the following partitioning strategies:

• MySQL mode

  • RANGE partitioning

  • RANGE COLUMNS partitioning

  • LIST partitioning

  • LIST COLUMNS partitioning

  • HASH partitioning

  • KEY partitioning

  • Composite partitioning

• Oracle mode

  • 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 type of partitioning and is often used with dates. For example, you can partition business log tables by day, week, or month.

RANGE COLUMNS partitioning

RANGE COLUMNS partitioning is similar to RANGE partitioning, but they are different in the following aspects:

• The results of RANGE COLUMNS partitioning do not have to be integer columns and all column types are accepted.

• You cannot use an expression as the partitioning key for RANGE COLUMNS partitioning.

• LIST COLUMNS partitioning enables the use of multiple columns as partitioning keys (column vectors)

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.

LIST COLUMNS partitioning

LIST COLUMNS partitioning is similar to LIST partitioning, but they are different in the following aspects:

• The partitioning key of LIST COLUMNS partitions does not have to be of the integer type. It can be of any data type.

• The partitioning key of LIST COLUMNS partitions can contain multiple columns (column vectors).

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.

KEY partitioning

KEY partitioning is similar to HASH partitioning. They both use the modulus operation to determine which partition the requested data belongs to. Their difference is that in KEY partitioning, the system applies an internal default function on the partitioning key before the modulus operation.

KEY partitioning has the following characteristics:

• The partitioning key does not have to be of the integer type. It can be of any type.

• You cannot use an expression as the partitioning key.

• You can use a vector as the partitioning key.

• If you do not specify any column as the partitioning key, the primary key is used as the partitioning key.

  Example:

  obclient>CREATE TABLE t1 (
  c1 INT PRIMARY KEY, 
  c2 INT) 
  PARTITION BY KEY() 
  PARTITIONS 5;
  

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.

You can use the SUBPARTITION TEMPLATE clause when you specify the subpartiton strategy details. In Oracle mode, OceanBase Database allows you to create subpartitions with or without a template.