In the Oracle mode of OceanBase Database, you can repartition a table (convert a non-partitioned table into a partitioned or subpartitioned table), add partitions, drop partitions, and truncate partitions.
Repartition a table
The syntax for repartitioning a table is as follows:
ALTER TABLE table_name MODIFY partition_option;
Notice
At present, you can only convert a non-partitioned table into a partitioned or subpartitioned table.
Examples of repartitioning a table
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 template-based subpartitioned table
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 non-template-based subpartitioned table
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 for adding a partition is as follows:
ALTER TABLE table_name ADD PARTITION (partition_definition)
Add the p3 partition to a template-based subpartitioned table named tbl4. You only need to specify the partition definition. The subpartition definition is automatically filled 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 for dropping a partition is as follows:
ALTER TABLE table_name DROP PARTITION partition_name;
Here is an example:
obclient> ALTER TABLE tbl4 DROP PARTITION p3;
Query OK, 0 rows affected
Truncate a partition
The syntax for truncating a partition is as follows:
ALTER TABLE table_name TRUNCATE PARTITION partition_name;
Here is an example:
obclient> ALTER TABLE tbl4 TRUNCATE PARTITION p2;
Query OK, 0 rows affected
