OceanBase分布式存储管理Oracle模式修改分区规则

表创建成功后，您可以根据业务需要，修改分区规则，即可以修改表的分区方式和分区类型。

分区方式和分区类型转换的支持情况

OceanBase 数据库的 Oracle 模式支持将非分区表转化为一级分区表或二级分区表，暂不支持修改分区表的分区类型。

OceanBase 数据库 Oracle 模式中分区方式转换的支持情况如下表所示。

非分区表转化为一级分区表

您可以通过 ALTER TABLE 语句将非分区表转化为任意分区类型的一级分区表。

语法

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

参数解释

示例

创建非分区表 tbl1 后，转换为 Hash 类型的一级分区表。

1. 创建非分区表 tbl1。

   obclient> CREATE TABLE tbl1(c1 int, c2 date, PRIMARY KEY(c1, c2));
   Query OK, 0 rows affected
   

2. 将非分区表 tbl1 转化为一级分区表。

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

非分区表转化为二级分区表

您可以将非分区表转换为任意分区类型的模板化或非模板化二级分区表。

语法

将非分区表转换为模板化二级分区表的 SQL 语法如下：

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

将非分区表转化为非模板化二级分区表的 SQL 语法如下：

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

示例

创建非分区表 tbl2 后，转换为 Hash + Range 分区的模板化二级分区表。

1. 创建非分区表 tbl2。

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

2. 将非分区表 tbl2 转化为 Hash+ Range 类型的模板化二级分区表。

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