After you create a table, you can modify the partitioning attributes of the table as needed, such as the partitioning method, partition type, and partitioning key.
Modify the partitioning method
In the Oracle-compatible mode of OceanBase Database, you can use the ALTER TABLE statement to modify the partitioning method for a table. The following table describes the supported conversions between partitioning methods.
| Partitioning method | Non-partitioning | Partitioning | Subpartitioning |
|---|---|---|---|
| Non-partitioning | - | Supported | Supported |
| Partitioning | Not supported | - | Not supported |
| Subpartitioning | Not supported | Not supported | - |
Convert a non-partitioned table to a partitioned table
You can use the ALTER TABLE statement to convert a non-partitioned table to a partitioned table of any partitioning method. The syntax is as follows:
ALTER TABLE table_name MODIFY PARTITION BY partition_option;
partition_option:
range_partition_option
| list_partition_option
| hash_partition_option
range_partition_option:
RANGE (column_name)
(PARTITION partition_name VALUES LESS THAN(expr)
[, PARTITION partition_name VALUES LESS THAN (expr) ...]
[, PARTITION partition_name VALUES LESS THAN (MAXVALUE)]
)
list_partition_option:
LIST (expr(column_name) | column_name)
(PARTITION partition_name VALUES ( v01 [, v0N])
[, PARTITION partition_name VALUES ( vN1 [, vNN]) ...]
[, PARTITION partition_name VALUES (DEFAULT)]
)
hash_partition_option:
HASH (expr(column_name) | column_name) PARTITIONS partition_count
Parameters:
table_name: the name of the non-partitioned table to be modified.column_name: the name of the column to be used as the partitioning key.partition_count: the number of partitions.
For more information about the parameters in the syntax, see ALTER TABLE.
Here is an example:
Create a non-partitioned table tbl1 and convert it to a hash-partitioned table.
Create the non-partitioned table
tbl1.obclient> CREATE TABLE tbl1(col1 INT, col2 DATE, PRIMARY KEY(col1, col2));Convert the non-partitioned table
tbl1to a hash-partitioned table.obclient> ALTER TABLE tbl1 MODIFY PARTITION BY HASH(col1) PARTITIONS 4;
Convert a non-partitioned table to a subpartitioned table
You can also use the ALTER TABLE statement to convert a non-partitioned table to a subpartitioned table of any partitioning method.
The following syntax shows how to convert a non-partitioned table to a template-based subpartitioned table:
ALTER TABLE table_name MODIFY PARTITION BY partition_option;
partition_option:
RANGE(column_name) subpartition_option (range_partition_option)
| LIST(expr(column_name) | column_name) subpartition_option (list_partition_option)
| HASH(expr(column_name) | column_name) {(hash_partition_option)
| PARTITIONS partition_count}
subpartition_option:
SUBPARTITION BY
RANGE(column_name) SUBPARTITION TEMPLATE(range_subpartition_option)
| LIST(expr(column_name) | column_name) SUBPARTITION TEMPLATE(list_subpartition_option)
| HASH(expr(column_name) | column_name) {SUBPARTITION TEMPLATE (hash_subpartition_option)
| SUBPARTITIONS subpartition_count}
range_partition_option:
PARTITION partition_name VALUES LESS THAN(expr)
[, PARTITION partition_name VALUES LESS THAN(expr) ...]
[, PARTITION partition_name VALUES LESS THAN(MAXVALUE)]
range_subpartition_option:
SUBPARTITION subpartition_name VALUES LESS THAN(expr)
[, SUBPARTITION subpartition_name VALUES LESS THAN(expr) ...]
[, SUBPARTITION subpartition_name VALUES LESS THAN(MAXVALUE)]
list_partition_option:
PARTITION partition_name VALUES( v01 [, v0N])
[, PARTITION partition_name VALUES( vN1 [, vNN]) ...]
[, PARTITION partition_name VALUES(DEFAULT)]
list_subpartition_option:
SUBPARTITION subpartition_name VALUES(expr)
[, SUBPARTITION subpartition_name VALUES(expr) ...]
hash_partition_option:
PARTITION partition_name
[, PARTITION partition_name ...]
hash_subpartition_option:
SUBPARTITION subpartition_name
[, SUBPARTITION subpartition_name ...]
The following syntax shows how to convert a non-partitioned table to a non-template-based subpartitioned table:
ALTER TABLE table_name MODIFY PARTITION BY partition_option;
partition_option:
RANGE(column_name) subpartition_option
{range_partition_option (subpartition_option_list)
[, range_partition_option (subpartition_option_list) ...]
}
| LIST(expr(column_name) | column_name) subpartition_option
{list_partition_option (subpartition_option_list)
[, list_partition_option (subpartition_option_list) ...]
}
| HASH(expr(column_name) | column_name) subpartition_option
{hash_partition_option (subpartition_option_list)
[, hash_partition_option (subpartition_option_list) ...]
}
subpartition_option:
SUBPARTITION BY
RANGE(column_name)
| LIST(expr(column_name) | column_name)
| HASH(expr(column_name) | column_name)
range_partition_option:
PARTITION partition_name VALUES LESS THAN(expr)
list_partition_option:
PARTITION partition_name VALUES(expr)
hash_partition_option:
PARTITION partition_name
subpartition_option_list:
range_subpartition_option
| list_subpartition_option
| hash_subpartition_option
range_subpartition_option:
SUBPARTITION subpartition_name VALUES LESS THAN(expr)
[, SUBPARTITION subpartition_name VALUES LESS THAN(expr) ...]
list_subpartition_option:
SUBPARTITION subpartition_name VALUES(expr)
[, SUBPARTITION subpartition_name VALUES(expr) ...]
hash_subpartition_option:
SUBPARTITION subpartition_name
[, SUBPARTITION subpartition_name ...]
For more information about the parameters in the syntax, see ALTER TABLE.
Here is an example:
Create a non-partitioned table tbl2 and convert it to a HASH-RANGE template-based subpartitioned table.
Create the non-partitioned table
tbl2.obclient> CREATE TABLE tbl2(col1 INT, col2 DATE, PRIMARY KEY(col1, col2));Convert the non-partitioned table
tbl2to a hash+range-partitioned table.obclient> ALTER TABLE tbl2 MODIFY PARTITION BY HASH(col1) SUBPARTITION BY RANGE(col2) SUBPARTITION TEMPLATE( SUBPARTITION p1 VALUES LESS THAN ('10-OCT-2016'), SUBPARTITION p2 VALUES LESS THAN ('30-MAR-2116') ) PARTITIONS 2;
Modify the partition type
In the Oracle-compatible mode of OceanBase Database, you can use the ALTER TABLE statement to convert a range-partitioned table to an interval-partitioned table, or vice versa.
Limitations
- You cannot convert a non-partitioned table to an interval-partitioned table.
- If a range-partitioned table contains
MAXVALUE, you cannot convert it to an interval-partitioned table. - At present, you cannot change the interval specified when creating an interval-partitioned table.
Syntax
ALTER TABLE table_name SET INTERVAL(expr);
Parameters
| Parameter | Description |
|---|---|
| table_name | The name of the table to be modified. |
| table_definition | The column definitions of the table. |
Example
Convert a range-partitioned table to an interval-partitioned table.
Create a range-partitioned table
test_tbl1.obclient> CREATE TABLE test_tbl1 (col1 VARCHAR2(30), col2 DATE NOT NULL) PARTITION BY RANGE (col2) ( PARTITION p0 VALUES LESS THAN(TO_DATE('2025-01-01', 'yyyy-mm-dd')) );Convert the partitioning of
test_tbl1to an interval partition, with the interval being one month.obclient> ALTER TABLE test_tbl1 SET INTERVAL(NUMTOYMINTERVAL(1, 'month'));
Convert an interval-partitioned table to a range-partitioned table.
Create an interval-partitioned table
test_tbl2, where each partitioning interval is one month.obclient> CREATE TABLE test_tbl2(col1 VARCHAR2(30), col2 DATE NOT NULL) PARTITION BY RANGE (col2) INTERVAL(NUMTOYMINTERVAL(1, 'month')) ( PARTITION p0 VALUES LESS THAN(TO_DATE('2025-01-01', 'yyyy-mm-dd')) );Convert the partitioning of
test_tbl2to range partitioning.obclient> ALTER TABLE test_tbl2 SET INTERVAL();
Modify the partitioning key
The Oracle-compatible mode of OceanBase Database currently does not support modifying the partitioning key of a partitioned table.