---
title: 编组
description: 创建二级分区表表组 本节主要介绍如何在 OceanBase 数据库的 Oracle 模式中创建二级分区表表组。 支持的分区类型 OceanBase 数据库的 Oracle 模式支持创建模板化和非模板化的二级分区表表组。 分区类型 创建模板化二级分区表组 创建非模板化二级分区表组 Range + Range 支持 支持…
---
切换语言

- 中文站 - 简体中文
- International - English
- 日本站 - 日本語

文档反馈![](https://mdn.alipayobjects.com/huamei_22khvb/afts/img/A*P8CuR4UJ_FkAAAAAAAAAAAAADiGDAQ/original) OceanBase 数据库分布式版 - V 3.2.3 企业版 LTS

# 创建二级分区表表组

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

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

## 支持的分区类型

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

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

关于模板化二级分区表和非模板化二级分区表的详细介绍，请参见 [创建二级分区表](https://www.oceanbase.com/docs/enterprise-oceanbase-database-cn-10000000000357037)。

## 注意事项

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

## 创建二级分区表组语法

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

```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 语法

```sql
CREATE TABLEGROUP  tablegroupname [opt_tablegroup_option_list] opt_tg_partition_option;

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 二级分区表表组。

  ```sql
  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 二级分区表表组。

  ```sql
  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 二级分区表表组。

  ```sql
   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 二级分区表表组。

  ```sql
  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 二级分区表表组。

  ```sql
  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 二级分区表表组。

  ```sql
  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 二级分区表表组。

  ```sql
  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 二级分区表表组。

  ```sql
  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 二级分区表表组。

  ```sql
  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 二级分区表表组。

  ```sql
  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 二级分区表表组。

  ```sql
  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 二级分区表表组。

  ```sql
  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

  ```

 上一篇 下一篇 ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线