Subpartitioning is a technique that partitions a table in two dimensions. For example, it is commonly used in scenarios that involve bills.
OceanBase Database supports three partitioning methods: HASH, RANGE, and LIST. The MySQL mode of OceanBase Database supports three additional partitioning methods: KEY, RANGE COLUMNS, and LIST COLUMNS. OceanBase Database supports the combination of any two of these methods.
The following subpartitioning methods are supported in both MySQL and Oracle modes:
HASH-HASH partitioning
HASH-RANGE partitioning
HASH-LIST partitioning
RANGE-HASH partitioning
RANGE-RANGE partitioning
RANGE-LIST partitioning
LIST-HASH partitioning
LIST-RANGE partitioning
LIST-LIST partitioning
In addition to the preceding methods, the following methods are supported in MySQL mode:
HASH-KEY partitioning
HASH-RANGE COLUMNS partitioning
HASH-LIST COLUMNS partitioning
RANGE-KEY partitioning
RANGE-RANGE COLUMNS partitioning
RANGE-LIST COLUMNS partitioning
LIST-KEY partitioning
LIST-RANGE COLUMNS partitioning
LIST-LIST COLUMNS partitioning
KEY-HASH partitioning
KEY-RANGE partitioning
KEY-LIST partitioning
KEY-RANGE COLUMNS partitioning
KEY-LIST COLUMNS partitioning
RANGE COLUMNS-HASH partitioning
RANGE COLUMNS-RANGE partitioning
RANGE COLUMNS-LIST partitioning
RANGE COLUMNS-KEY partitioning
RANGE COLUMNS-LIST COLUMNS partitioning
LIST COLUMNS-HASH partitioning
LIST COLUMNS-RANGE partitioning
LIST COLUMNS-LIST partitioning
LIST COLUMNS-KEY partitioning
LIST COLUMNS-RANGE COLUMNS partitioning
When you create a subpartitioned table, you can choose whether to use a template.
Create a subpartitioned table by using a template
When you use a template in partition creation, the template defines the subpartitioning method of every partition. The subpartitioning method is thus identical for all the partitions.
Syntax:
CREATE TABLE ....
partition BY [hash|range|list] (column_list)
subpartition BY [hash|range|list] (column_list)
subpartition template
(
subpartition subpart_name subpartition_define
, ...
)
(
partition part_name partition_define
, ...
When you use a template in composite partitioned table creation, a subpartition is named as ($part_name)s($subpart_name).
For example, in the t_range_range table created by the following statement, the partition p0 has three subpartitions: p0srp1, p0srp2, and p0srp3.
obclient> CREATE TABLE t_range_range (c1 int, c2 int, c3 int) partition BY range(c1)
subpartition BY range (c2)
subpartition template
(
subpartition rp1 VALUES less than (100),
subpartition rp2 VALUES less than (200),
subpartition rp3 VALUES less than (300)
)
(
partition p0 VALUES less than (100),
partition p1 VALUES less than (200),
partition p2 VALUES less than (300)
);
Create a subpartitioned table without a template
You can define the subpartitioning method for each partition. You can choose whether use the same subpartitioning method for different partitions.
CREATE TABLE ....
partition BY [hash|range|list] (column_list)
subpartition BY [hash|range|list] (column_list)
(
partition part_name partition_define
(
subpartition subpart_name subpartition_define
, ...
)
, ...
)
When you create a composite partitioned table without a template, you name the subpartitions in the creation statement by yourself.
For example, in the t_range_range1 table created by the following statement, the partition p0 has three subpartitions: p0_r1, p0_r2, and p0_r3.
obclient> CREATE TABLE t_range_range1 (c1 int, c2 int, c3 int) partition BY range(c1)
subpartition BY range (c2)
(
partition p0 VALUES less than (100)
(
subpartition p0_r1 VALUES less than (100),
subpartition p0_r2 VALUES less than (200),
subpartition p0_r3 VALUES less than (300)
),
partition p1 VALUES less than (200)
(
subpartition p1_r1 VALUES less than (100),
subpartition p1_r2 VALUES less than (200),
subpartition p1_r3 VALUES less than (300)
),
partition p2 VALUES less than (300)
(
subpartition p2_r1 VALUES less than (100),
subpartition p2_r2 VALUES less than (200),
subpartition p2_r3 VALUES less than (300)
)
);