In OceanBase Database in Oracle mode, you can perform partition operations such as re-partitioning (converting a non-partitioned table into a partitioned table or a subpartitioned table), adding partitions, dropping partitions, and truncating partitions.
Re-partitioning
The syntax of the REPARTITION procedure is as follows:
ALTER TABLE table_name MODIFY partition_option;
Notice
Currently, only non-partitioned tables can be converted into partitioned tables.
Examples
Convert a non-partitioned table into a partitioned table
obclient> CREATE TABLE tbl1(c1 INT PRIMARY KEY, c2 DATE);
Query OK, 0 rows affected
obclient> ALTER TABLE tbl1 MODIFY PARTITION BY HASH(c1) PARTITIONS 4;
Query OK, 0 rows affected
Convert a non-partitioned table into a subpartitioned table (template-based)
obclient> CREATE TABLE tbl2(c1 INT, c2 DATE, PRIMARY KEY(c1, c2));
Query OK, 0 rows affected
obclient> ALTER TABLE tbl2 MODIFY PARTITION BY HASH(c1)
SUBPARTITION BY RANGE (c2)
SUBPARTITION TEMPLATE(
SUBPARTITION p1 VALUES LESS THAN (TO_DATE('2016/02/01','YYYY/MM/DD')),
SUBPARTITION p2 VALUES LESS THAN (TO_DATE('2116/02/01','YYYY/MM/DD'))
);
Query OK, 0 rows affected
Convert a non-partitioned table into a subpartitioned table (non-template-based)
obclient> CREATE TABLE tbl3(c1 INT, c2 DATE, PRIMARY KEY(c1, c2));
Query OK, 0 rows affected
obclient> ALTER TABLE tbl3 MODIFY PARTITION BY RANGE(c1)
SUBPARTITION BY RANGE(c2) (
PARTITION p0 VALUES LESS THAN(0),
PARTITION p1 VALUES LESS THAN(100));
Query OK, 0 rows affected
Add a partition
The syntax of the ADD_PARTITION procedure is as follows:
ALTER TABLE table_name ADD PARTITION (partition_definition)
The following example adds a partition p3 to the template-based subpartitioned table tbl4. You only need to specify the definition of the partition. The definitions of the subpartitions are automatically filled in based on the template.
obclient> CREATE TABLE tbl4(col1 INT, col2 INT, PRIMARY KEY(col1,col2))
PARTITION BY RANGE(col1)
SUBPARTITION BY RANGE(col2)
SUBPARTITION TEMPLATE
(
SUBPARTITION p0 VALUES LESS THAN (50),
SUBPARTITION p1 VALUES LESS THAN (100)
)
(
PARTITION p0 VALUES LESS THAN (100),
PARTITION p1 VALUES LESS THAN (200),
PARTITION p2 VALUES LESS THAN (300)
);
Query OK, 0 rows affected
obclient> ALTER TABLE tbl4 ADD PARTITION p3 VALUES LESS THAN (400);
Query OK, 0 rows affected
Drop a partition
The syntax of the DROP_PARTITION procedure is as follows:
ALTER TABLE table_name DROP PARTITION partition_name;
Here is an example of dropping a partition:
obclient> ALTER TABLE tbl4 DROP PARTITION p3;
Query OK, 0 rows affected
Truncate a partition
The syntax of the TRUNCATE_PARTITION procedure is as follows:
ALTER TABLE table_name TRUNCATE PARTITION partition_name;
Here is an example of truncating a partition:
obclient> ALTER TABLE tbl4 TRUNCATE PARTITION p2;
Query OK, 0 rows affected