Partition routing

Partitioning is a built-in feature of OceanBase Database. You only need to specify the partitioning strategy and the number of partitions when you create a table. This topic describes the background, syntax, and examples of partition routing.

Background

The SQL queries for a partitioned table are the same as those for a regular table. ODP or OBServer of OceanBase automatically routes SQL queries of users to the corresponding nodes. The partitioning details of a partitioned table are transparent to the applications.

Syntax

If you know the partition number of the data you want to read, you can run SQL queries to directly access the partition in the partitioned table. Basic syntax:

part_table partition (partition_name)

By default, unless you define the partition names, each partition is given a number as its name based on a particular rule. Examples:

• Partition names: p0, p1, p2, ...

• Subpartition names: p0sp0, p0sp1, p0sp2, ..., p1sp0, p1sp1, p1sp2, ...

Examples

1. Query the p0 partition.

   obclient> select * from t_log partition(p0);
   +------+---------------------+
   | col1 | col2                |
   +------+---------------------+
   |   10 | 2021-03-30 00:00:00 |
   |   18 | 2021-04-30 00:00:00 |
   |   10 | 2021-07-01 00:00:00 |
   |   11 | 2021-09-30 00:00:00 |
   +------+---------------------+
   4 rows in set (0.01 sec)
   

2. Query the p0sp1 subpartition.

   obclient> select * from t_log partition(p0sp1);
   +------+---------------------+
   | col1 | col2                |
   +------+---------------------+
   |   18 | 2021-04-30 00:00:00 |
   +------+---------------------+
   1 row in set (0.00 sec)