OceanBase Database supports a variety of partitioning strategies:
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.
The following rules apply when you use RANGE partitioning:
Each partition has a VALUES LESS THAN clause, which specifies a non-inclusive upper bound literal for the partitions. Values of the partitioning key equal to or higher than this literal are added to the next higher partition.
All partitions, except the first, have an implicit lower bound, which is the upper bound of the previous partition.
A MAXVALUE literal can and can only be defined for the last partition. MAXVALUE represents a virtual infinite value that is always greater than other possible values for the partitioning key, including the NULL value.
Example: Creating a RANGE partitioned table
obclient> CREATE TABLE t_log_part_by_range (
log_id number NOT NULL
, log_value varchar2(50)
, log_date date NOT NULL DEFAULT sysdate
) PARTITION BY RANGE(log_date)
(
PARTITION M202001 VALUES LESS THAN(TO_DATE('2020/02/01','YYYY/MM/DD'))
, PARTITION M202002 VALUES LESS THAN(TO_DATE('2020/03/01','YYYY/MM/DD'))
, PARTITION M202003 VALUES LESS THAN(TO_DATE('2020/04/01','YYYY/MM/DD'))
, PARTITION M202004 VALUES LESS THAN(TO_DATE('2020/05/01','YYYY/MM/DD'))
, PARTITION M202005 VALUES LESS THAN(TO_DATE('2020/06/01','YYYY/MM/DD'))
, PARTITION M202006 VALUES LESS THAN(TO_DATE('2020/07/01','YYYY/MM/DD'))
, PARTITION M202007 VALUES LESS THAN(TO_DATE('2020/08/01','YYYY/MM/DD'))
, PARTITION M202008 VALUES LESS THAN(TO_DATE('2020/09/01','YYYY/MM/DD'))
, PARTITION M202009 VALUES LESS THAN(TO_DATE('2020/10/01','YYYY/MM/DD'))
, PARTITION M202010 VALUES LESS THAN(TO_DATE('2020/11/01','YYYY/MM/DD'))
, PARTITION M202011 VALUES LESS THAN(TO_DATE('2020/12/01','YYYY/MM/DD'))
, PARTITION M202012 VALUES LESS THAN(TO_DATE('2021/01/01','YYYY/MM/DD'))
, PARTITION MMAX VALUES LESS THAN (MAXVALUE)
);
You can add and delete range partitions. If the MAXVALUE is specified in the last range partition, you cannot add another partition.
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.
Example: Creating a LIST partitioned table
obclient> CREATE TABLE t_log_part_by_list (
log_id number NOT NULL
, log_value varchar2(50)
, log_date date NOT NULL DEFAULT sysdate
, PRIMARY key(log_id, log_value)
) PARTITION BY list(log_value)
(
PARTITION P01 VALUES ( '01' )
, PARTITION P02 VALUES ( '02' )
, PARTITION P03 VALUES ( '03' )
, PARTITION P04 VALUES ( '04' )
, PARTITION P05 VALUES ( '05' )
);
Query OK, 0 rows affected (0.10 sec)
obclient> INSERT INTO t_log_part_by_list(log_id, log_value) values(1,'01'),(2,'02'),(3,'03'),(4,'04'),(5,'05'),(6,'01');
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0
You can add more LIST partitions, specifying a new non-duplicate table.
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.
Example: Creating a HASH partitioned table
obclient> CREATE TABLE ware(
w_id number
, w_ytd number(12,2)
, w_tax number(4,4)
, w_name varchar(10)
, w_street_1 varchar(20)
, w_street_2 varchar(20)
, w_city varchar(20)
, w_state char(2)
, w_zip char(9)
, primary key(w_id)
) PARTITION by hash(w_id) partitions 60;
Hash partitioning does not support partition deletion.
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 enables you to take advantage of multiple partitioning strategies.
You can use the SUBPARTITION TEMPLATE clause to specify subpartition settings. OceanBase Database supports templated subpartitioning and non-templated subpartitioning.
Example: Creating a composite partitioned table
obclient> CREATE TABLE t_ordr_part_by_hash_range (o_w_id int
, o_d_id int
, o_id int
, o_c_id int
, o_carrier_id int
, o_ol_cnt int
, o_all_local int
, o_entry_d date
, index idx_ordr(o_w_id, o_d_id, o_c_id, o_id) LOCAL
, primary key ( o_w_id, o_d_id, o_id, o_entry_d )
)
PARTITION BY hash(o_w_id)
SUBPARTITION BY RANGE(o_entry_d)
SUBPARTITION template
(
SUBPARTITION M202001 VALUES LESS THAN(TO_DATE('2020/02/01','YYYY/MM/DD'))
, SUBPARTITION M202002 VALUES LESS THAN(TO_DATE('2020/03/01','YYYY/MM/DD'))
, SUBPARTITION M202003 VALUES LESS THAN(TO_DATE('2020/04/01','YYYY/MM/DD'))
, SUBPARTITION M202004 VALUES LESS THAN(TO_DATE('2020/05/01','YYYY/MM/DD'))
, SUBPARTITION M202005 VALUES LESS THAN(TO_DATE('2020/06/01','YYYY/MM/DD'))
, SUBPARTITION M202006 VALUES LESS THAN(TO_DATE('2020/07/01','YYYY/MM/DD'))
, SUBPARTITION M202007 VALUES LESS THAN(TO_DATE('2020/08/01','YYYY/MM/DD'))
, SUBPARTITION M202008 VALUES LESS THAN(TO_DATE('2020/09/01','YYYY/MM/DD'))
, SUBPARTITION M202009 VALUES LESS THAN(TO_DATE('2020/10/01','YYYY/MM/DD'))
, SUBPARTITION M202010 VALUES LESS THAN(TO_DATE('2020/11/01','YYYY/MM/DD'))
, SUBPARTITION M202011 VALUES LESS THAN(TO_DATE('2020/12/01','YYYY/MM/DD'))
, SUBPARTITION M202012 VALUES LESS THAN(TO_DATE('2021/01/01','YYYY/MM/DD'))
, SUBPARTITION MMAX VALUES LESS THAN (MAXVALUE)
)
partitions 16;
Query OK, 0 rows affected (1.36 sec)
obclient> CREATE TABLE t_log_part_by_range_hash (
log_id number NOT NULL
, log_value varchar2(50)
, log_date date NOT NULL DEFAULT sysdate
, PRIMARY key(log_id, log_date)
) PARTITION BY RANGE(log_date)
SUBPARTITION BY HASH(log_id) SUBPARTITIONS 16
(
PARTITION M202001 VALUES LESS THAN(TO_DATE('2020/02/01','YYYY/MM/DD'))
, PARTITION M202002 VALUES LESS THAN(TO_DATE('2020/03/01','YYYY/MM/DD'))
, PARTITION M202003 VALUES LESS THAN(TO_DATE('2020/04/01','YYYY/MM/DD'))
, PARTITION M202004 VALUES LESS THAN(TO_DATE('2020/05/01','YYYY/MM/DD'))
, PARTITION M202005 VALUES LESS THAN(TO_DATE('2020/06/01','YYYY/MM/DD'))
, PARTITION M202006 VALUES LESS THAN(TO_DATE('2020/07/01','YYYY/MM/DD'))
, PARTITION M202007 VALUES LESS THAN(TO_DATE('2020/08/01','YYYY/MM/DD'))
, PARTITION M202008 VALUES LESS THAN(TO_DATE('2020/09/01','YYYY/MM/DD'))
, PARTITION M202009 VALUES LESS THAN(TO_DATE('2020/10/01','YYYY/MM/DD'))
, PARTITION M202010 VALUES LESS THAN(TO_DATE('2020/11/01','YYYY/MM/DD'))
, PARTITION M202011 VALUES LESS THAN(TO_DATE('2020/12/01','YYYY/MM/DD'))
, PARTITION M202012 VALUES LESS THAN(TO_DATE('2021/01/01','YYYY/MM/DD'))
, PARTITION MMAX VALUES LESS THAN (MAXVALUE)
);
Query OK, 0 rows affected (1.09 sec)