After a table is created, you can modify its partitioning method, partition type, and partitioning key based on business requirements.
Modify the partitioning method
OceanBase AI Database supports modifying the partitioning method of a table. The conversion support is shown in the following table.
Partitioning method |
Non-partitioning |
Partitioning |
Subpartitioning |
|---|---|---|---|
| Non-partitioning | - | Supported | Supported |
| Partitioning | Not supported | - | Supported |
| Subpartitioning | Not supported | Supported | - |
Convert a non-partitioned table or a subpartitioned table into a partitioned table
You can convert a non-partitioned table or a subpartitioned table into a partitioned table of any type. The SQL syntax is as follows:
ALTER TABLE table_name PARTITION BY partition_option;
partition_option:
range_partition_option
| list_partition_option
| hash_partition_option
| key_partition_option
range_partition_option:
RANGE [COLUMNS](expr(column_name) | 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 [COLUMNS](expr(column_name) | column_name)
(PARTITION partition_name VALUES IN( v01 [, v0N])
[, PARTITION partition_name VALUES IN( vN1 [, vNN]) ...]
[, PARTITION partition_name VALUES IN(DEFAULT)]
)
hash_partition_option:
HASH(expr(column_name) | column_name) PARTITIONS partition_count
key_partition_option:
KEY(expr(column_name) | column_name) PARTITIONS partition_count
The parameters are described as follows:
table_name: The name of the non-partitioned table to be modified.column_name: The name of the column used for partitioning.partition_name: The name of the partition.partition_count: The number of partitions.
For detailed parameter descriptions, see ALTER TABLE.
Examples:
After creating a non-partitioned table
tbl1, convert it into a HASH-partitioned table.Create a non-partitioned table
tbl1.obclient> CREATE TABLE tbl1(col1 INT, col2 DATETIME);Convert the non-partitioned table into a partitioned table.
obclient> ALTER TABLE tbl1 PARTITION BY HASH(col1) PARTITIONS 4;
After creating a template-based subpartitioned table
tbl1_hrcwith HASH + RANGE COLUMNS partitioning, modify it into a HASH-partitioned table.Create a subpartitioned table
tbl1_hrcwith HASH + RANGE COLUMNS partitioning.obclient> CREATE TABLE tbl1_hrc (col1 INT, col2 DATETIME, PRIMARY KEY(col1, col2)) PARTITION BY HASH(col1) SUBPARTITION BY RANGE COLUMNS(col2) SUBPARTITION TEMPLATE( SUBPARTITION p1 VALUES LESS THAN ('2016-10-01'), SUBPARTITION p2 VALUES LESS THAN ('2026-10-01') ) PARTITIONS 2;Convert the subpartitioned table into a partitioned table.
obclient> ALTER TABLE tbl1_hrc PARTITION BY HASH(col1) PARTITIONS 4;
Convert a non-partitioned table or a partitioned table into a subpartitioned table
You can convert a non-partitioned table or a partitioned table into a template-based or non-template-based subpartitioned table of any partition type.
The SQL syntax for converting a non-partitioned table or a partitioned table into a template-based subpartitioned table is as follows:
ALTER TABLE table_name PARTITION BY partition_option;
partition_option:
RANGE [COLUMNS](expr(column_name) | column_name) subpartition_option (range_partition_option)
| LIST [COLUMNS](expr(column_name) | column_name) subpartition_option (list_partition_option)
| HASH(expr(column_name) | column_name) {(hash_partition_option)
| PARTITIONS partition_count}
| KEY(expr(column_name) | column_name){(key_partition_option)
| PARTITIONS partition_count}
subpartition_option:
SUBPARTITION BY
RANGE [COLUMNS](expr(column_name) | column_name) SUBPARTITION TEMPLATE(range_subpartition_option)
| LIST [COLUMNS](expr(column_name) | column_name) SUBPARTITION TEMPLATE(list_subpartition_option)
| HASH(expr(column_name) | column_name) {SUBPARTITION TEMPLATE (hash_subpartition_option)
| SUBPARTITIONS subpartition_count}
| KEY(expr(column_name) | column_name) {SUBPARTITION TEMPLATE (key_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 IN( v01 [, v0N])
[,PARTITION partition_name VALUES IN( vN1 [, vNN]) ...]
[,PARTITION partition_name VALUES IN(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 ...]
key_partition_option:
PARTITION partition_name
[, PARTITION partition_name ...]
key_subpartition_option:
SUBPARTITION subpartition_name
[, SUBPARTITION subpartition_name ...]
The SQL syntax for converting a non-partitioned table or a partitioned table into a non-template-based subpartitioned table is as follows:
ALTER TABLE table_name PARTITION BY partition_option;
partition_option:
RANGE [COLUMNS](expr(column_name) | column_name) subpartition_option
{range_partition_option (subpartition_option_list)
[, range_partition_option (subpartition_option_list) ...]
}
| LIST [COLUMNS](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) ...]
}
| KEY(expr(column_name) | column_name) subpartition_option
{key_partition_option (subpartition_option_list)
[, key_partition_option (subpartition_option_list) ...]
}
subpartition_option:
SUBPARTITION BY
RANGE [COLUMNS](expr(column_name) | column_name)
| LIST [COLUMNS](expr(column_name) | column_name)
| HASH(expr(column_name) | column_name)
| KEY(expr(column_name) | column_name)
range_partition_option:
PARTITION partition_name VALUES LESS THAN(expr)
list_partition_option:
PARTITION partition_name VALUES IN(expr)
hash_partition_option:
PARTITION partition_name
key_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 IN(expr)
[, SUBPARTITION subpartition_name VALUES IN(expr) ...]
hash_subpartition_option:
SUBPARTITION subpartition_name
[, SUBPARTITION subpartition_name ...]
key_subpartition_option:
SUBPARTITION subpartition_name
[, SUBPARTITION subpartition_name ...]
The parameters are described as follows:
table_name: The name of the non-partitioned table to be modified.column_name: The name of the column to be used for partitioning.partition_name: The name of the partition.partition_count: The number of partitions.subpartition_name: The name of the subpartition.subpartition_count: The number of subpartitions.
For detailed parameter descriptions, see ALTER TABLE.
Examples:
After creating the non-partitioned table
tbl2, convert it into a template-based subpartitioned table with HASH + RANGE COLUMNS partitioning.Create the non-partitioned table
tbl2.obclient> CREATE TABLE tbl2(col1 INT, col2 DATETIME);Convert the non-partitioned table
tbl2into a template-based subpartitioned table.obclient> ALTER TABLE tbl2 PARTITION BY HASH(col1) SUBPARTITION BY RANGE COLUMNS(col2) SUBPARTITION TEMPLATE( SUBPARTITION p1 VALUES LESS THAN('2016-10-01'), SUBPARTITION p2 VALUES LESS THAN('2026-10-01') ) PARTITIONS 2;
After creating the HASH-partitioned table
tbl2_h, modify it into a template-based subpartitioned table with HASH + RANGE COLUMNS partitioning.Create the partitioned table
tbl2_h.obclient> CREATE TABLE tbl2_h(col1 INT, col2 DATETIME, PRIMARY KEY(col1, col2)) PARTITION BY HASH(col1) PARTITIONS 4;Modify the partitioned table
tbl2_hinto a template-based subpartitioned table.obclient> ALTER TABLE tbl2_h PARTITION BY HASH(col1) SUBPARTITION BY RANGE COLUMNS(col2) SUBPARTITION TEMPLATE( SUBPARTITION p1 VALUES LESS THAN('2016-10-10'), SUBPARTITION p2 VALUES LESS THAN('2116-3-30') ) PARTITIONS 2;
Change the partition type
OceanBase AI Database supports changing the partition type of a partitioned table. You can change the partition type of an existing partitioned table to any other partition type.
Change the partitioning type of a partitioned table
You can change the partitioning type of the current partitioned table to any other type.
Examples:
Convert a HASH-partitioned table into a KEY-partitioned table.
Create a partitioned table named
tbl3_h.obclient> CREATE TABLE tbl3_h(col1 INT, col2 DATETIME, PRIMARY KEY(col1, col2)) PARTITION BY HASH(col1) PARTITIONS 4;Change the partitioning type of the partitioned table
tbl3_hto KEY.obclient> ALTER TABLE tbl3_h PARTITION BY KEY(col1) PARTITIONS 10;
Convert a RANGE-partitioned table into a LIST-partitioned table.
Create a partitioned table named
tbl3_r.obclient> CREATE TABLE tbl3_r(col1 INT, col2 INT) PARTITION BY RANGE(col1) (PARTITION p0 VALUES LESS THAN(100), PARTITION p1 VALUES LESS THAN(200), PARTITION p2 VALUES LESS THAN(300) );Change the partitioning type of the partitioned table
tbl3_rto LIST.obclient> ALTER TABLE tbl3_r PARTITION BY LIST(col1) (PARTITION p0 VALUES IN (1, 2, 3), PARTITION p1 VALUES IN (5, 6), PARTITION p2 VALUES IN (DEFAULT) );
Change the partitioning type of a subpartitioned table
You can change the partitioning type of a subpartitioned table to any other type.
Example:
Convert a template-based RANGE COLUMNS + RANGE COLUMNS subpartitioned table into a template-based LIST COLUMNS + KEY subpartitioned table.
Create a template-based RANGE COLUMNS + RANGE COLUMNS subpartitioned table.
obclient> CREATE TABLE tbl4_rcrc(col1 INT, col2 INT) PARTITION BY RANGE COLUMNS(col1) SUBPARTITION BY RANGE COLUMNS(col2) SUBPARTITION TEMPLATE (SUBPARTITION mp0 VALUES LESS THAN(1000), SUBPARTITION mp1 VALUES LESS THAN(2000), SUBPARTITION mp2 VALUES LESS THAN(3000) ) (PARTITION p0 VALUES LESS THAN(100), PARTITION p1 VALUES LESS THAN(200), PARTITION p2 VALUES LESS THAN(300) );Change the partitioning type of the subpartitioned table to LIST COLUMNS + KEY.
obclient> ALTER TABLE tbl4_rcrc PARTITION BY LIST COLUMNS(col1) SUBPARTITION BY KEY(col2) SUBPARTITIONS 3 (PARTITION p0 VALUES IN(100), PARTITION p1 VALUES IN(200), PARTITION p2 VALUES IN(300) );
Modify the partitioning key
You can modify the partitioning key based on your business requirements. The SQL syntax is as follows:
ALTER TABLE table_name PARTITION BY partition_option;
Example:
Create a table named
tbl5_hwith the partitioning keycol1.obclient> CREATE TABLE tbl5_h(col1 INT, col2 INT, PRIMARY KEY(col1, col2)) PARTITION BY HASH(col1) PARTITIONS 4;Modify the partitioning key of the partitioned table
tbl5_htocol2.obclient> ALTER TABLE tbl5_h PARTITION BY HASH(col2) PARTITIONS 6;