创建二级分区表

二级分区是按照两个维度来把数据拆分成分区的操作。最常用的地方是类似用户账单的场景。

二级分区分区类型

OceanBase 数据库的 Oracle 模式目前支持 HASH、RANGE 和 LIST 三种分区⽅式，二级分区为任意两种分区方式的组合。创建二级分区表支持情况详见下表：

创建二级分区表

二级分区表可分为模板化二级分区表和非模板化二级分区表。

创建模板化二级分区表

CREATE TABLE [IF NOT EXISTS] table_name(column_option_list)
  [table_option_list] partition_option_list;

column_option_list:
  column_name column_type [, column_name column_type]

table_option_list:
  table_option [table_option]

table_option:
   LOCALITY [=] locality_name
  | PRIMARY_ZONE [=] primary_zone_name

partition_option_list:
  PARTITION BY 
    RANGE(column_name){subpartition_option} (range_partition_list)
   | LIST(expression){subpartition_option} (list_partition_list)
   | HASH(expression){subpartition_option} { (hash_partition_list)
                                             | PARTITIONS partition_count }

subpartition_option:
  SUBPARTITION BY 
    RANGE(column_name) SUBPARTITION TEMPLATE(range_subpartition_list)
   | LIST(expression) SUBPARTITION TEMPLATE(list_subpartition_list)
   | HASH(expression) { SUBPARTITION TEMPLATE (hash_subpartition_list)
                       | SUBPARTITIONS subpartition_count }

range_partition_list:
  range_partition [, range_partition ...]

range_partition:
  PARTITION partition_name VALUES LESS THAN {(expression_list) | MAXVALUE}

range_subpartition_list:
  range_subpartition [, range_subpartition ...]

range_subpartition:
  SUBPARTITION subpartition_name VALUES LESS THAN {(expression_list) | MAXVALUE}

list_partition_list:
  list_partition [, list_partition ...]

list_partition:
  PARTITION partition_name VALUES {(expression_list) | DEFAULT}

list_subpartition_list:
  list_subpartition [, list_subpartition ...]

list_subpartition:
  SUBPARTITION subpartition_name VALUES {(expression_list) | DEFAULT}

hash_partition_list:
    hash_partition [, hash_partition ...]

hash_partition:
    PARTITION partition_name

hash_subpartition_list:
    hash_subpartition [, hash_subpartition ...]

hash_subpartition:
    SUBPARTITION subpartition_name

expression_list:
  expression [, expression ...]

column_name_list:
  column_name [, column_name ...]

partition_count | subpartition_count:
  INT_VALUE

说明

• 模板化二级分区表的每个一级分区下的二级分区都按照模板中的二级分区定义，即每个一级分区下的二级分区定义均相同。

• 对于模板化二级分区表来说，二级分区的命名规则为 ($part_name)s($subpart_name)。例如：对于下⾯的 t_range_range 表，p0下的 3 个二级分区的分区名分别为 p0smp1、p0smp2、p0smp3。

obclient> CREATE TABLE t_range_range(col1 INT,col2 INT)
       PARTITION BY RANGE(col1)
       SUBPARTITION BY RANGE(col2)
       SUBPARTITION TEMPLATE
       (SUBPARTITION mp1 VALUES LESS THAN(100),
        SUBPARTITION mp2 VALUES LESS THAN(200),
        SUBPARTITION mp3 VALUES LESS THAN(300)
        )
       (PARTITION p0 VALUES LESS THAN(2020),
        PARTITION p1 VALUES LESS THAN(2021),
        PARTITION p2 VALUES LESS THAN(2022)
        );
Query OK, 0 rows affected

创建非模板化二级分区表

CREATE TABLE [IF NOT EXISTS] table_name(column_option_list)
  [table_option_list] [partition_option_list];

column_option_list:
  column_name column_type [, column_name column_type]

table_option_list:
  table_option [table_option]

table_option:
  LOCALITY [=] locality_name
  | PRIMARY_ZONE [=] primary_zone_name

partition_option_list:
  PARTITION BY 
    RANGE(column_name){subpartition_option}
      { range_partition_option (subpartition_option_list)
        [, range_partition_option (subpartition_option_list) ...]
       }
   | LIST(expression){subpartition_option}
       { list_partition_option (subpartition_option_list)
        [, list_partition_option (subpartition_option_list) ...]
        }
   | HASH(expression) {subpartition_option}
      { hash_partition_option (subsubpartition_option_list)
       [, hash_partition_option (subsubpartition_option_list) ...]
       }

subpartition_option:
  SUBPARTITION BY { RANGE(column_name) | LIST(expression) | HASH(expression) }

subpartition_option_list:
  range_partition_option_list | list_partition_option_list | hash_partition_option_list

range_partition_option_list:
  range_partition_option [, range_partition_option ...]

list_partition_option_list:
  list_partition_option [, list_partition_option ...]

hash_partition_option_list:
  hash_partition_option [, hash_partition_option ...]

range_partition_option:
  SUBPARTITION subpartition_name VALUES LESS THAN range_partition_expr
  [,SUBPARTITION subpartition_name VALUES LESS THAN range_partition_expr ...]

list_partition_option:
  SUBPARTITION subpartition_name VALUES list_partition_expr
  [, SUBPARTITION subpartition_name VALUES list_partition_expr ...]

hash_partition_option_list:
  SUBPARTITION subpartition_name
  [, SUBPARTITION subpartition_name ...]

说明

非模板化二级分区表的每个一级分区下的二级分区均可以自由定义，即每个一级分区下的二级分区的定义可以相同也可以不同。

参数解释

示例

创建模板化二级分区表

• 创建模板化 Range + Range 分区表。

  obclient> CREATE TABLE t2_m_rr(col1 INT,col2 INT)
         PARTITION BY RANGE(col1)
         SUBPARTITION BY RANGE(col2)
         SUBPARTITION TEMPLATE
           (SUBPARTITION mp0 VALUES LESS THAN(2020),
            SUBPARTITION mp1 VALUES LESS THAN(2021),
            SUBPARTITION mp2 VALUES LESS THAN(2022)
            )
           (PARTITION p0 VALUES LESS THAN(100),
            PARTITION p1 VALUES LESS THAN(200)
            );
  Query OK, 0 rows affected
  

• 创建模板化 Range + List 分区表。

  obclient> CREATE TABLE t2_m_rl(col1 INT,col2 VARCHAR2(50))
         PARTITION BY RANGE(col1)
         SUBPARTITION BY LIST(col2)
         SUBPARTITION TEMPLATE
           (SUBPARTITION mp0 VALUES('01'),
            SUBPARTITION mp1 VALUES('02'),
            SUBPARTITION mp2 VALUES('03')
            )
           (PARTITION p0 VALUES LESS THAN(100),
            PARTITION p1 VALUES LESS THAN(200)
            );
  Query OK, 0 rows affected
  

• 创建模板化 Range + Hash 分区表。

  obclient> CREATE TABLE t2_m_rh(col1 INT,col2 VARCHAR2(50))
         PARTITION BY RANGE(col1)
         SUBPARTITION BY HASH(col2) SUBPARTITIONS 5
          (PARTITION p0 VALUES LESS THAN(100),
           PARTITION p1 VALUES LESS THAN(200)
           );
  Query OK, 0 rows affected
  

• 创建模板化 List + Range 分区表。

  obclient> CREATE TABLE t2_m_lr(col1 INT,col2 varchar2(50))
         PARTITION BY LIST(col2)
         SUBPARTITION BY RANGE(col1)
         SUBPARTITION TEMPLATE
          (SUBPARTITION mp0 VALUES LESS THAN(100),
           SUBPARTITION mp1 VALUES LESS THAN(200),
           SUBPARTITION mp2 VALUES LESS THAN(300)
           )
          (PARTITION p0 VALUES('01'),
           PARTITION p1 VALUES('02')
           );
  Query OK, 0 rows affected
  

• 创建模板化 List + List 分区表。

  obclient> CREATE TABLE t2_m_ll(col1 INT,col2 varchar2(50))
         PARTITION BY LIST(col1)
         SUBPARTITION BY LIST(col2)
         SUBPARTITION TEMPLATE
         (SUBPARTITION mp0 VALUES('A'),
          SUBPARTITION mp1 VALUES('B'),
          SUBPARTITION mp2 VALUES('C')
          )
         (PARTITION p0 VALUES('01'),
          PARTITION p1 VALUES('02')
          );
  Query OK, 0 rows affected
  

• 创建模板化 List + Hash 分区表。

  obclient> CREATE TABLE t2_m_lh(col1 INT,col2 VARCHAR2(50))
         PARTITION BY LIST(col1)
         SUBPARTITION BY HASH(col2) SUBPARTITIONS 5
          (PARTITION p0 VALUES('01'),
           PARTITION p1 VALUES('02')
           );
  Query OK, 0 rows affected
  

• 创建模板化 Hash + Range 分区表。

  obclient> CREATE TABLE tbl2_m_hr(col1 INT,col2 INT,col3 INT) 
         PARTITION BY HASH(col1)
         SUBPARTITION BY RANGE(col2)
         SUBPARTITION TEMPLATE
          (SUBPARTITION sp0 VALUES LESS THAN(100),
           SUBPARTITION sp1 VALUES LESS THAN(200),
           SUBPARTITION sp2 VALUES LESS THAN(300)
          )
          PARTITIONS 5;
  Query OK, 0 rows affected
  

• 创建模板化 Hash + List 分区表。

  obclient> CREATE TABLE tbl2_m_hl(col1 INT,col2 INT,col3 INT) 
         PARTITION BY HASH(col1)
         SUBPARTITION BY LIST(col2)
         SUBPARTITION TEMPLATE
          (SUBPARTITION sp0 VALUES(100),
           SUBPARTITION sp1 VALUES(200),
           SUBPARTITION sp2 VALUES(300)
           )
          PARTITIONS 5;
  Query OK, 0 rows affected
  

• 创建模板化 Hash + Hash 分区表。

  obclient> CREATE TABLE tbl2_m_hh(col1 INT,col2 INT,col3 INT) 
         PARTITION BY HASH(col1)
         SUBPARTITION BY HASH(col2)
          SUBPARTITIONS 3
          PARTITIONS 5;
  Query OK, 0 rows affected
  

创建非模板化二级分区表

• 创建非模板化 Range + Range 分区表。

  obclient> CREATE TABLE t2_f_rr(col1 INT,col2 INT)
         PARTITION BY RANGE(col1)
         SUBPARTITION BY RANGE(col2)
          (PARTITION p0 VALUES LESS THAN(100)
            (SUBPARTITION sp0 VALUES LESS THAN(2020),
             SUBPARTITION sp1 VALUES LESS THAN(2021)
            ),
           PARTITION p1 VALUES LESS THAN(200)
            (SUBPARTITION sp2 VALUES LESS THAN(2020),
             SUBPARTITION sp3 VALUES LESS THAN(2021),
             SUBPARTITION sp4 VALUES LESS THAN(2022)
             )
           );
  Query OK, 0 rows affected
  

• 创建非模板化 Range + List 分区表。

  obclient> CREATE TABLE t2_f_rl(col1 INT,col2 VARCHAR2(50))
         PARTITION BY RANGE(col1)
         SUBPARTITION BY LIST(col2)
         (PARTITION p0 VALUES LESS THAN(100)
           (SUBPARTITION sp0 VALUES('01'),
            SUBPARTITION sp1 VALUES('02')
            ),
          PARTITION p1 VALUES LESS THAN(200)
           (SUBPARTITION sp2 VALUES('01'),
            SUBPARTITION sp3 VALUES('02'),
            SUBPARTITION sp4 VALUES('03')
           )
          );
  Query OK, 0 rows affected
  

• 创建非模板化 Range + Hash 分区表。

  obclient> CREATE TABLE t2_f_rh(col1 INT,col2 VARCHAR2(50))
         PARTITION BY RANGE(col1)
         SUBPARTITION BY HASH(col2)
         (PARTITION p0 VALUES LESS THAN(100)
           (SUBPARTITION sp0,
            SUBPARTITION sp1
            ),
          PARTITION p1 VALUES LESS THAN(200)
           (SUBPARTITION sp2,
            SUBPARTITION sp3,
            SUBPARTITION sp4
            )
          );
  Query OK, 0 rows affected
  

• 创建非模板化 List + Range 分区表。

  obclient> CREATE TABLE t2_f_lr(col1 INT,col2 VARCHAR2(50))
         PARTITION BY LIST(col2)
         SUBPARTITION BY RANGE(col1)
         (PARTITION p0 VALUES('01')
           (SUBPARTITION sp0 VALUES LESS THAN(100),
            SUBPARTITION sp1 VALUES LESS THAN(200)
            ),
          PARTITION p1 VALUES('02')
           (SUBPARTITION sp2 VALUES LESS THAN(100),
            SUBPARTITION sp3 VALUES LESS THAN(200),
            SUBPARTITION sp4 VALUES LESS THAN(300)
            )
          );
  Query OK, 0 rows affected
  

• 创建非模板化 List + List 分区表。

  obclient> CREATE TABLE t2_f_ll(col1 INT,col2 varchar2(50))
         PARTITION BY LIST(col1)
         SUBPARTITION BY LIST(col2)
         (PARTITION p0 VALUES ('01', '02')
           (SUBPARTITION sp0 VALUES ('A'),
            SUBPARTITION sp1 VALUES ('B'),
            SUBPARTITION sp2 VALUES ('C')
            )
  ,
          PARTITION p1 VALUES ('03', '04')
           (SUBPARTITION sp3 VALUES ('A'),
            SUBPARTITION sp4 VALUES ('B'),
            SUBPARTITION sp5 VALUES ('C')
            )
          );
  Query OK, 0 rows affected
  

• 创建非模板化 List + Hash 分区表。

  obclient> CREATE TABLE t2_f_lh(col1 INT,col2 VARCHAR2(50))
         PARTITION BY LIST(col1)
         SUBPARTITION BY HASH(col2)
         (PARTITION p0 VALUES('01')
           (SUBPARTITION sp0,
            SUBPARTITION sp1
            ), 
          PARTITION p1 VALUES('02')
           (SUBPARTITION sp2,
            SUBPARTITION sp3,
            SUBPARTITION sp4
            )
          );
  Query OK, 0 rows affected
  

• 创建非模板化 Hash + Range 分区表。

  obclient> CREATE TABLE tbl2_f_hr(col1 INT,col2 INT,col3 INT)
         PARTITION BY HASH(col1)
         SUBPARTITION BY RANGE(col2)
          (PARTITION p0
           (SUBPARTITION sp0 VALUES LESS THAN(100),
            SUBPARTITION sp1 VALUES LESS THAN(200)),
          PARTITION p1
           (SUBPARTITION sp2 VALUES LESS THAN(100),
            SUBPARTITION sp3 VALUES LESS THAN(200)
           )
          );
  Query OK, 0 rows affected
  

• 创建非模板化 Hash + List 分区表。

  obclient> CREATE TABLE t2_f_hl(col1 INT,col2 INT,col3 INT)
         PARTITION BY HASH(col1)
         SUBPARTITION BY LIST(col2)
          (PARTITION p0
            (SUBPARTITION sp0 VALUES(1,3),
             SUBPARTITION sp1 VALUES(4,7)
            ),
           PARTITION p1
            (SUBPARTITION sp2 VALUES(1,3),
             SUBPARTITION sp3 VALUES(4,7)
             )
          );
  Query OK, 0 rows affected
  

• 创建非模板化 Hash + Hash 分区表。

  obclient> CREATE TABLE t2_f_hh(col1 INT,col2 INT,col3 INT)
         PARTITION BY HASH(col1)
         SUBPARTITION BY HASH(col2)
          (PARTITION p0
            (SUBPARTITION sp0,
             SUBPARTITION sp1
             ),
           PARTITION p1
            (SUBPARTITION sp2,
             SUBPARTITION sp3
             )
          );
  Query OK, 0 rows affected