After a table is created, you can modify its partitioning rules as needed. In other words, you can change the partitioning method and partitioning type of the table.
Support for the conversion of the partitioning method and partitioning type
In the Oracle mode of OceanBase Database, a non-partitioned table can be converted into a partitioned or subpartitioned table. However, the partitioning type of a partitioned table cannot be changed.
The following table describes the support for the conversion of the partitioning methods in the Oracle mode of OceanBase Database.
| 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 into a partitioned table
You can use the ALTER TABLE statement to convert a non-partitioned table into a partitioned table of any partitioning type.
Syntax
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
| Parameter | Description |
|---|---|
| table_name | The name of the non-partitioned table. |
| column_name | The name of the column to be used as the partitioning key. |
| partition_count | The number of partitions. |
Example
Create a non-partitioned table named tbl1 and then convert it into a HASH-partitioned table.
Create a non-partitioned table named
tbl1.obclient> CREATE TABLE tbl1(c1 int, c2 date, PRIMARY KEY(c1, c2)); Query OK, 0 rows affectedConvert the
tbl1table into a partitioned table.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
You can convert a non-partitioned table into a template-based or non-template-based subpartitioned table of any partitioning type.
Syntax
The SQL syntax for converting a non-partitioned table into a template-based subpartitioned table is as follows:
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 SQL syntax for converting a non-partitioned table into a non-template-based subpartitioned table is as follows:
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]...
Example
Create a non-partitioned table named tbl2 and then convert it into a template-based HASH-RANGE-subpartitioned table.
Create a non-partitioned table named
tbl2.obclient> CREATE TABLE tbl2(c1 int, c2 date, PRIMARY KEY(c1, c2)); Query OK, 0 rows affectedConvert the
tbl2table into a template-based HASH-RANGE-subpartitioned table.obclient> ALTER TABLE tbl2 MODIFY PARTITION BY HASH(c1) SUBPARTITION BY RANGE(c2) SUBPARTITION template( SUBPARTITION p1 VALUES LESS THAN ('10-OCT-2016'), SUBPARTITION p2 VALUES LESS THAN ('30-MAR-2116')) PARTITIONS 2; Query OK, 0 rows affected
