Modify partitioning rules|V4.1.0| docs|Distributed Database

Modify partitioning rules

Last Updated：2023-07-28 02:55:42 Updated

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 OceanBase Database in MySQL mode, you can change the partitioning type of a partitioned table. You can change the partitioning type of a partitioned table to any other type.

In MySQL mode of OceanBase Database, you can also change the partitioning method of a table. The following table describes the support for the change of the partitioning method.

Table type	Non-partitioning	Partitioning	Subpartitioning
Non-partitioned table	-	Supported	Supported
Partitioned table	Unsupported	-	Supported
Subpartitioned table	Unsupported	Supported	-

Change the partitioning type of a partitioned table

You can change the partitioning type of the current partitioned table to any other type.

Perform the following steps to convert a HASH-partitioned table into a KEY-partitioned table.

Create a HASH-partitioned table named tbl1_h.

obclient> CREATE TABLE tbl1_h(c1 int, c2 datetime, PRIMARY KEY(c1, c2))
          PARTITION BY HASH(c1) PARTITIONS 4;
Query OK, 0 rows affected

Change the partitioning type of the tbl1_h table to KEY.

obclient> ALTER TABLE tbl1_h PARTITION BY KEY(c1) PARTITIONS 10;
Query OK, 0 rows affected

Change the partitioning type of a subpartitioned table

You can change the partitioning type of the current partitioned table to any other type.

Perform the following steps to convert a template-based RANGE-RANGE-subpartitioned table into a template-based LIST COLUMNS-KEY-subpartitioned table.

Create a RANGE COLUMNS-RANGE COLUMNS-subpartitioned table by using a template.

obclient> CREATE TABLE t_m_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)
        );
Query OK, 0 rows affected

Change the partitioning type of the subpartitioned table to LIST COLUMNS-KEY.

obclient> ALTER TABLE t_m_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)
        );
Query OK, 0 rows affected

Convert a non-partitioned or subpartitioned table into a partitioned table

You can convert a non-partitioned or subpartitioned table into a partitioned table of any partitioning type.

Syntax

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

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_name	The name of the partition.
partition_count	The number of partitions.

Examples

Create a non-partitioned table named tbl1 and then convert it into a HASH-partitioned table.
1. Create a non-partitioned table named tbl1.
```
obclient> CREATE TABLE tbl1(c1 int, c2 datetime);
Query OK, 0 rows affected
```
2. Convert a non-partitioned table into a partitioned table
```
obclient> ALTER TABLE tbl1 PARTITION BY HASH(c1) partitions 4;
Query OK, 0 rows affected
```

Create a HASH-RANGE COLUMNS-subpartitioned table named tbl3_hrc by using a template and then convert it into a HASH-partitioned table.

Create a HASH-RANGE COLUMNS-subpartitioned table named tbl3_hrc.

obclient> CREATE TABLE tbl3_hrc (c1 int ,c2 datetime,primary key(c1,c2))
     PARTITION BY HASH(c1)
                     SUBPARTITION BY RANGE COLUMNS(c2)
                     SUBPARTITION template(
                     SUBPARTITION p1 VALUES LESS THAN ('2016-10-10'),
                     SUBPARTITION p2 VALUES LESS THAN ('2116-3-30'))
                     PARTITIONS 2;
Query OK, 0 rows affected

Convert the subpartitioned table into a partitioned table.

obclient> ALTER TABLE tbl3_hrc PARTITION BY HASH(c1) PARTITIONS 4;
Query OK, 0 rows affected

Convert a non-partitioned or partitioned table into a subpartitioned table

You can convert a non-partitioned or partitioned table into a template-based or non-template-based subpartitioned table of any partitioning type.

Syntax

SQL syntax for converting a non-partitioned or partitioned table into a template-based subpartitioned table:

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]...

SQL syntax for converting a non-partitioned or partitioned table into a non-template-based subpartitioned table:

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]...

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_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.

Examples

Create a non-partitioned table named tbl2 and then convert it into a template-based HASH-RANGE COLUMNS-subpartitioned table.

Create a non-partitioned table named tbl2.

obclient> CREATE TABLE tbl2(c1 int, c2 datetime);
Query OK, 0 rows affected

Convert the tbl2 table into a template-based subpartitioned table.

obclient> ALTER TABLE tbl2 PARTITION BY HASH(c1)                
        SUBPARTITION BY RANGE COLUMNS(c2)
                SUBPARTITION TEMPLATE(
                SUBPARTITION p1 VALUES LESS THAN('2016-10-10'),
                SUBPARTITION p2 VALUES LESS THAN('2116-3-30'))
                PARTITIONS 2;
Query OK, 0 rows affected

Create a HASH-partitioned table named tbl2 and then convert it into a template-based HASH-RANGE COLUMNS-subpartitioned table.

Create a HASH-partitioned table named tbl2_h.

obclient> CREATE TABLE tbl2_h(c1 int, c2 datetime, PRIMARY KEY(c1, c2))
          PARTITION BY HASH(c1) PARTITIONS 4;
Query OK, 0 rows affected

Convert the tbl2_h table into a template-based subpartitioned table.

obclient> ALTER TABLE tbl2_h PARTITION BY HASH(c1)
                SUBPARTITION BY RANGE COLUMNS(c2)
                SUBPARTITION TEMPLATE(
                SUBPARTITION p1 VALUES LESS THAN('2016-10-10'),
                SUBPARTITION p2 VALUES LESS THAN('2116-3-30'))
                PARTITIONS 2;
Query OK, 0 rows affected