创建二级分区表表组 | OceanBase 文档中心

创建二级分区表表组

更新时间：2023-07-19 13:03:06

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

支持的分区类型

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

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

关于模板化二级分区表和非模板化二级分区表的详细介绍，请参见创建二级分区表。

注意事项

创建表组后，表组内的表不能单独进行分区管理操作，只能通过表组对分区表进行分区的统一管理。

创建二级分区表组语法

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

CREATE TABLEGROUP tablegroup_name [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, ...}
  | LIST [COLUMNS] column_num tg_subpartition_option {PARTITION partition_name VALUES  list_partition_expr, ...}

tg_subpartition_option:
SUBPARTITION BY 
    RANGE [COLUMNS] column_num SUBPARTITION TEMPLATE {SUBPARTITION subpartition_name VALUES LESS THAN range_partition_expr, ...}
  | HASH [column_num] SUBPARTITIONS int_num
  | LIST [COLUMNS] column_num SUBPARTITION TEMPLATE {SUBPARTITION subpartition_name VALUES  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 list_partition_expr (subpartition_option_list)
       [, PARTITION partition_name VALUES list_partition_expr (subpartition_option_list)]...
      }

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

subpartition_option_list:
  range_subpartition_option | list_subpartition_option | hash_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 list_partition_expr
    [, SUBPARTITION subpartition_name VALUES list_partition_expr]...

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

语法说明

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

由于表组没有具体的列定义，故不需要写出具体的列，只需要指定列个数（column_num）即可。

示例

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

创建模版化 Range + Range 二级分区表表组。

obclient> CREATE TABLEGROUP tg2_m_rr 
       PARTITION BY RANGE 1
       SUBPARTITION BY RANGE 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)
         );
Query OK, 0 rows affected

创建模版化 Range + List 二级分区表表组。

obclient> CREATE TABLEGROUP tg2_m_rl 
       PARTITION BY RANGE 1
       SUBPARTITION BY LIST 1
       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 TABLEGROUP tg2_m_rh 
        PARTITION BY RANGE 1
        SUBPARTITION BY HASH 1 SUBPARTITIONS 5
         (PARTITION p0 VALUES LESS THAN (100),
          PARTITION p1 VALUES LESS THAN (200)
         );
Query OK, 0 rows affected

创建模版化 List + Range 二级分区表表组。

obclient> CREATE TABLEGROUP tg2_m_lr  
       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 ('A'),
         PARTITION p1 VALUES ('B')
        ); 
Query OK, 0 rows affected

创建模版化 List + List 二级分区表表组。

obclient> CREATE TABLEgroup tg2_m_ll  
       PARTITION BY LIST 1
       SUBPARTITION BY LIST 1
       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 TABLEGROUP tg2_m_lh 
       PARTITION BY LIST 1
       SUBPARTITION BY HASH 1 SUBPARTITIONS 5
        (PARTITION p0 VALUES ('01'),
         PARTITION p1 VALUES ('02')
        );
Query OK, 0 rows affected

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

创建非模版化 Range + Range 二级分区表表组。

obclient> CREATE TABLEGROUP tg2_f_rr 
       PARTITION BY RANGE  1 
       SUBPARTITION BY RANGE  1
        (PARTITION p0 VALUES LESS THAN (100)
          (SUBPARTITION sp0 VALUES LESS THAN (1000),
           SUBPARTITION sp1 VALUES LESS THAN (2000)
           ), 
         PARTITION p1 VALUES LESS THAN (200)
          (SUBPARTITION sp2 VALUES LESS THAN (1000),
           SUBPARTITION sp3 VALUES LESS THAN (2000),
           SUBPARTITION sp4 VALUES LESS THAN (3000)
          ),
         PARTITION p2 VALUES LESS THAN(300)
          (SUBPARTITION sp5 VALUES LESS THAN (1000),
           SUBPARTITION sp6 VALUES LESS THAN (2000)
          )
        );
Query OK, 0 rows affected

创建非模版化 Range + List 二级分区表表组。

obclient> CREATE TABLEGROUP tg2_f_rl 
       PARTITION BY RANGE 1
       SUBPARTITION BY LIST 1 
        (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 TABLEGROUP tg2_f_rh 
       PARTITION BY RANGE 1
       SUBPARTITION BY HASH 1
        (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 + Hash 二级分区表表组。

obclient> CREATE TABLEGROUP tg2_f_lh
       PARTITION BY LIST  1
       SUBPARTITION BY HASH 1
        (PARTITION p0 VALUES ('A', 'B')
          (SUBPARTITION sp0, 
           SUBPARTITION sp1
          ),
         PARTITION p1 VALUES ('C','D','E')
          (SUBPARTITION sp2,
           SUBPARTITION sp3
          )
        );
Query OK, 0 rows affected

创建非模版化 List + List 二级分区表表组。

obclient> CREATE TABLEGROUP tg2_f_ll
       PARTITION BY LIST  1
       SUBPARTITION BY LIST  1
        (PARTITION p0 VALUES ('A')
          (SUBPARTITION sp0 VALUES ('01','03'),
           SUBPARTITION sp1 VALUES ('04','06'),
           SUBPARTITION sp2 VALUES ('07','09')
          ),
         PARTITION p1 VALUES ('B')
          (SUBPARTITION sp3 VALUES ('01','03'),
           SUBPARTITION sp4 VALUES ('04','06'),
           SUBPARTITION sp5 VALUES ('07','09')
          )
        );
Query OK, 0 rows affected

创建非模版化 List + Range 二级分区表表组。

obclient> CREATE TABLEGROUP tg2_f_lr
       PARTITION BY LIST  1
       SUBPARTITION BY RANGE 1
        (PARTITION p0 VALUES ('OR', 'WA')
          (SUBPARTITION sp0 VALUES LESS THAN (1000),
           SUBPARTITION sp1 VALUES LESS THAN (2000)
          ),
         PARTITION p1 VALUES ('AZ', 'UT', 'NM')
          (SUBPARTITION sp2 VALUES LESS THAN (1000),
           SUBPARTITION sp3 VALUES LESS THAN (2000)
           )
         );
Query OK, 0 rows affected