编组

创建二级分区表组

更新时间：2023-08-09 14:11:21

本节主要介绍如何在 OceanBase 数据库的 MySQL 模式中创建二级分区表表组。

支持的二级分区类型

OceanBase 数据库的 MySQL 模式支持创建模板化和非模板化的二级分区表表组。

分区类型	创建模板化二级分区表组	创建非模板化二级分区表组
Range + Range / Range + Range Columns / Range + List / Range + List Columns / Range + Hash / Range + Key	支持	支持
Range Columns + Range / Range Columns + Range Columns / Range Columns + List / Range Columns + List Columns / Range Columns + Hash / Range Columns + Key	支持	支持
List + Range / List + Range Columns / List + List / List + List Columns / List + Hash / List + Key	支持	支持
List Columns + Range / List Columns + Range Columns / List Columns + List / List Columns + List Columns / List Columns + Hash / List Columns + Key	支持	支持
Hash + Range / Hash + Range Columns / Hash + List / Hash + List Columns	支持	不支持
Hash + Key / Hash + Hash	不支持	不支持
Key + Range / Key + Range Columns / Key + List / Key + List Columns	支持	不支持
Key + Hash / Key + Key	不支持	不支持

注意事项

创建表组需要当前用户具有全局 CREATE 权限。
创建表组后，表组内的表不能单独进行分区管理操作，只能通过表组对分区表进行分区的统一管理。

创建二级分区表组语法

创建模板化二级分区表表组的 SQL 语法

CREATE TABLEGROUP [IF NOT EXISTS] tablegroupname [opt_tablegroup_option_list] opt_tg_partition_option

opt_tablegroup_option_list:
tablegroup_option [tablegroup_option]

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

opt_tg_partition_option:
    PARTITION BY 
      KEY column_num tg_subpartition_option PARTITIONS int_num
    | HASH tg_subpartition_option PARTITIONS INTNUM
    | RANGE [COLUMNS column_num] tg_subpartition_option 
          {PARTITION partition_name VALUES LESS THAN range_partition_expr, ...}
    | LIST [COLUMNS column_num] tg_subpartition_option 
          {PARTITION partition_name VALUES IN list_partition_expr, ...}

tg_subpartition_option:
SUBPARTITION BY 
     RANGE [COLUMNS column_num] SUBPARTITION TEMPLATE 
          {SUBPARTITION partition_name VALUES LESS THAN range_partition_expr, ...}
    | HASH SUBPARTITIONS INTNUM
    | KEY column_num SUBPARTITIONS INTNUM
    | LIST [COLUMNS column_num] SUBPARTITION TEMPLATE 
          {SUBPARTITION partition_name VALUES IN list_partition_expr, ...}

创建非模板化二级分区表表组的 SQL 语法

CREATE TABLEGROUP  <tablegroupname> [opt_tablegroup_option_list] opt_tg_partition_option

opt_tablegroup_option_list
tablegroup_option [tablegroup_option]

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

opt_tg_partition_option:
PARTITION BY 
   RANGE COLUMNS column_num tg_subpartition_option
    { PARTITION partition_name VALUES LESS THAN range_partition_expr (subpartition_option_list)
      [,PARTITION partition_name VALUES LESS THAN range_partition_expr (subpartition_option_list) ...]}
 | LIST COLUMNS column_num tg_subpartition_option
    { PARTITION partition_name VALUES IN list_partition_expr (subpartition_option_list)
      [,PARTITION partition_name VALUES IN list_partition_expr (subpartition_option_list) ...] }
 | HASH [tg_subpartition_option] PARTITIONS int_num

tg_subpartition_option:
 SUBPARTITION BY 
    RANGE [COLUMNS column_num] 
  | LIST [COLUMNS column_num] 
  | HASH [column_num][SUBPARTITIONS int_num]
  | KEY column_num SUBPARTITIONS int_num

subpartition_option_list:
    range_subpartition_option
  | list_subpartition_option
  | hash_subpartition_option
  | key_subpartition_option

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

list_subpartition_option:
  SUBPARTITION subpartition_name VALUES IN list_partition_expr
    [, SUBPARTITION subpartition_name VALUES IN list_partition_expr]...

hash_subpartition_option | key_subpartition_option:
  SUBPARTITION subpartition_name
    [, SUBPARTITION subpartition_name]...

语句说明

opt_tablegroup_option_list：定义表组的分区方式。要求表组的 Locality 和 Primary Zone 信息必须与表组内的表完全一致。表组的 Locality 包括副本类型、个数和位置，Primary Zone 包括 Leader 位置和优先级，以上信息均要与表组内表信息完全一致。
opt_tg_partition_option：定义表组的分区规则。表组的分区规则需要与表组内的表使用相同的分区类型和分区个数。

对于 Range Columns、List Columns 和 Key 分区，必须指定列个数（COLUMN_NUM）。

示例

创建非模板化二级分区表组

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

obclient> CREATE TABLEGROUP tg1_rr
       PARTITION BY RANGE COLUMNS 1
       SUBPARTITION BY RANGE COLUMNS 1
       (PARTITION p0 VALUES LESS THAN (100)
         (SUBPARTITION sp0 VALUES LESS THAN (2020)
         ,SUBPARTITION sp1 VALUES LESS THAN  (2021)
         ,SUBPARTITION sp2 VALUES LESS THAN (2022)
        ,SUBPARTITION sp3 VALUES LESS THAN  (2023)
         ),
       PARTITION p1 VALUES LESS THAN (200)
         (SUBPARTITION sp4 VALUES LESS THAN (2020)
         ,SUBPARTITION sp5 VALUES LESS THAN  (2021)
         ,SUBPARTITION sp6 VALUES LESS THAN (2022)
         ,SUBPARTITION sp7 VALUES LESS THAN  (2023)
         )
       );
Query OK, 0 rows affected

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

obclient> CREATE TABLEGROUP tg1_rh 
    PARTITION BY RANGE COLUMNS 1
    SUBPARTITION BY HASH  (
        PARTITION p0 VALUES LESS THAN (1990) (
            SUBPARTITION s0,
            SUBPARTITION s1
        ),
        PARTITION p1 VALUES LESS THAN (2000) (
            SUBPARTITION s2,
            SUBPARTITION s3
        ),
        PARTITION p2 VALUES LESS THAN (2010) (
            SUBPARTITION s4,
            SUBPARTITION s5
        )
    );
Query OK, 0 rows affected

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

obclient> CREATE TABLEGROUP tg1_rl
       PARTITION BY RANGE COLUMNS 1
       SUBPARTITION BY LIST COLUMNS 2
       (PARTITION p0 VALUES LESS THAN (100)
         (SUBPARTITION sp0 VALUES IN (('US',1),('US',2)),
          SUBPARTITION sp1 VALUES IN  (('CN',1),('CN',2))
         ),
        PARTITION p1 VALUES LESS THAN (200)
         (SUBPARTITION sp3 VALUES IN (('US',1),('US',2)),
          SUBPARTITION sp4 VALUES IN  (('CN',1),('CN',2))
         )
       ); 
Query OK, 0 rows affected

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

obclient> CREATE TABLEGROUP tg1_lh
       PARTITION BY LIST COLUMNS 1
       SUBPARTITION BY HASH 
       (PARTITION p_northwest VALUES IN('OR', 'WA')
         (SUBPARTITION sp0,
          SUBPARTITION sp1
          ),
        PARTITION p_southwest VALUES IN('AZ', 'UT', 'NM')
         (SUBPARTITION sp2,
          SUBPARTITION sp3
          )
       );
Query OK, 0 rows affected

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

obclient> CREATE TABLEGROUP tg1_ll
       PARTITION BY LIST COLUMNS 1
       SUBPARTITION BY LIST COLUMNS 1
       (PARTITION p0 VALUES IN (1,2,3)
        ( SUBPARTITION sp0 VALUES IN (2020)
        , SUBPARTITION sp1 VALUES IN (2021)
        , SUBPARTITION sp2 VALUES IN (2022)
        , SUBPARTITION sp3 VALUES IN (2023)
        ),
        PARTITION p1 VALUES IN (4,5,6)
        ( SUBPARTITION sp4 VALUES IN (2020)
        , SUBPARTITION sp5 VALUES IN (2021)
        , SUBPARTITION sp6 VALUES IN (2022)
        , SUBPARTITION sp7 VALUES IN (2023)
        ) 
       );
Query OK, 0 rows affected

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

obclient> CREATE TABLEGROUP tg1_lr
       PARTITION BY LIST COLUMNS 1
       SUBPARTITION BY RANGE COLUMNS 1
       (PARTITION p0 VALUES IN (1,2,3)
         ( SUBPARTITION sp0 VALUES LESS THAN (2020)
         , SUBPARTITION sp1 VALUES LESS THAN (2021)
         , SUBPARTITION sp2 VALUES LESS THAN (2022)
         , SUBPARTITION sp3 VALUES LESS THAN (2023)
         ),
        PARTITION p1 VALUES IN (4,5,6)
         ( SUBPARTITION sp4 VALUES LESS THAN (2020)
         , SUBPARTITION sp5 VALUES LESS THAN (2021)
         , SUBPARTITION sp6 VALUES LESS THAN (2022)
         , SUBPARTITION sp7 VALUES LESS THAN (2023)
         ) 
       );
Query OK, 0 rows affected

创建非模板化 Range Columns + Key 分区表表组。

obclient> CREATE TABLEGROUP tg2_f_rck
       PARTITION BY RANGE COLUMNS 1
       SUBPARTITION BY KEY 1
       (PARTITION p0 VALUES LESS THAN(100)
         (SUBPARTITION sp0,
          SUBPARTITION sp1,
          SUBPARTITION sp2
         ),
        PARTITION p1 VALUES LESS THAN(200)
         (SUBPARTITION sp3,
          SUBPARTITION sp4,
          SUBPARTITION sp5
         ),
        PARTITION p2 VALUES LESS THAN(300)
         (SUBPARTITION sp6,
          SUBPARTITION sp7,
          SUBPARTITION sp8
         )
       );
Query OK, 0 rows affected

创建非模板化 List Columns + Key 分区表表组。

obclient> CREATE TABLEGROUP tg2_f_lck
       PARTITION BY LIST COLUMNS 1
       SUBPARTITION BY KEY 1
       (PARTITION p0 VALUES IN('01','02')
         (SUBPARTITION sp0,
          SUBPARTITION sp1,
          SUBPARTITION sp2),
        PARTITION p1 VALUES IN('03','04')
         (SUBPARTITION sp3,
          SUBPARTITION sp4,
          SUBPARTITION sp5),
        PARTITION p2 VALUES IN('05','06')
         (SUBPARTITION sp6,
          SUBPARTITION sp7,
          SUBPARTITION sp8)
        );
Query OK, 0 rows affected

创建模板化二级分区表组

创建模板化 Range Columns + Range Columns 分区表表组。

obclient> CREATE TABLEGROUP tg2_m_rcrc
        PARTITION BY RANGE COLUMNS 1
        SUBPARTITION BY RANGE COLUMNS 1
        SUBPARTITION TEMPLATE 
        (SUBPARTITION mp0 VALUES LESS THAN(2019),
         SUBPARTITION mp1 VALUES LESS THAN(2020),
         SUBPARTITION mp2 VALUES LESS THAN(2021)
        )
        (PARTITION p0 VALUES LESS THAN(100),
         PARTITION p1 VALUES LESS THAN(200),
         PARTITION p2 VALUES LESS THAN(300)
        ); 
Query OK, 0 rows affected

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

obclient> CREATE TABLEGROUP tg2_m_rclc
       PARTITION BY RANGE COLUMNS 1
       SUBPARTITION BY LIST COLUMNS 1
       SUBPARTITION TEMPLATE 
       (SUBPARTITION mp0 VALUES IN('A','B'),
        SUBPARTITION mp1 VALUES IN('C','D'),
        SUBPARTITION mp2 VALUES IN('E','F')
       )
       (PARTITION p0 VALUES LESS THAN(100),
        PARTITION p1 VALUES LESS THAN(200),
        PARTITION p2 VALUES LESS THAN(300)
       ); 
Query OK, 0 rows affected

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

obclient> CREATE TABLEGROUP tg2_m_rch
       PARTITION BY RANGE COLUMNS 1
       SUBPARTITION BY HASH SUBPARTITIONS 5
       (PARTITION p0 VALUES LESS THAN(100),
        PARTITION p1 VALUES LESS THAN(200),
        PARTITION p2 VALUES LESS THAN(300)
       );
Query OK, 0 rows affected

创建模板化 Range Columns + Key 分区表表组。

obclient> CREATE TABLEGROUP tg2_m_rck
       PARTITION BY RANGE COLUMNS 1
       SUBPARTITION BY KEY 1 SUBPARTITIONS 3
       (PARTITION p0 VALUES LESS THAN(100),
        PARTITION p1 VALUES LESS THAN(200),
        PARTITION p2 VALUES LESS THAN(300)
       ); 
Query OK, 0 rows affected

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

obclient> CREATE TABLEGROUP tg2_m_lcrc
       PARTITION BY LIST COLUMNS 1
       SUBPARTITION BY RANGE COLUMNS 1
       SUBPARTITION TEMPLATE 
       (SUBPARTITION mp0 VALUES LESS THAN(100),
        SUBPARTITION mp1 VALUES LESS THAN(200),
        SUBPARTITION mp2 VALUES LESS THAN(300)
       )
       (PARTITION p0 VALUES IN('A','B'),
        PARTITION p1 VALUES IN('C','D'),
        PARTITION p2 VALUES IN('E','F')
       );
Query OK, 0 rows affected

创建模板化 List Columns + List Columns 分区表表组。

obclient> CREATE TABLEGROUP tg2_m_lclc
       PARTITION BY LIST COLUMNS 1
       SUBPARTITION BY LIST COLUMNS 1
       SUBPARTITION TEMPLATE 
       (SUBPARTITION mp0 VALUES IN('A','B'),
        SUBPARTITION mp1 VALUES IN('C','D'),
        SUBPARTITION mp2 VALUES IN('E','F')
       )
       (PARTITION p0 VALUES IN('01','02'),
        PARTITION p1 VALUES IN('03','04'),
        PARTITION p2 VALUES IN('05','06')
       ); 
Query OK, 0 rows affected

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

obclient> CREATE TABLEGROUP tg2_m_lch
       PARTITION BY LIST COLUMNS 1
       SUBPARTITION BY HASH SUBPARTITIONS 5
       (PARTITION p0 VALUES IN('01','02'),
        PARTITION p1 VALUES IN('03','04'),
        PARTITION p2 VALUES IN('05','06')
       ); 
Query OK, 0 rows affected

创建模板化 List Columns + Key 分区表表组。

obclient> CREATE TABLEGROUP tg2_m_lck
       PARTITION BY LIST COLUMNS 1
       SUBPARTITION BY KEY 1 SUBPARTITIONS 5 
       (PARTITION p0 VALUES IN('01','02'),
        PARTITION p1 VALUES IN('03','04'),
        PARTITION p2 VALUES IN('05','06')
       ); 
Query OK, 0 rows affected