Create a subpartitioned table group|V3.2.4|OceanBase Database| docs|Distributed Database

Create a subpartitioned table group

Last Updated：2023-10-24 09:23:03 Updated

This topic describes how to create a partitioned table group in OceanBase Database in MySQL mode.

Usage notes

Only users with the tenant administrator privileges can create table groups.
Before you create a partitioned table group, you must specify the partitioning strategy of the table group. The partitioning strategy of the table group must be consistent with that of the partitioned tables to be added to the table group.
You cannot separately manage partitions of tables in a table group. Instead, you must manage partitions by using the table group in a unified manner.

Partitioning methods

In OceanBase Database in MySQL mode, you can create partitioned table groups, template-based subpartitioned table groups, and non-template-based subpartitioned table groups.

The following table describes the partitioning methods supported for partitioned table groups.

Partitioning methods	Partitioned table group
RANGE, LIST, RANGE COLUMNS, LIST COLUMNS, HASH, and KEY	Supported

Partitioning methods not supported for subpartitioned table groups:

Template-based subpartitioned table groups: HASH-KEY, HASH-HASH, KEY-HASH, and KEY-KEY
Non-template-based subpartitioned table groups: LIST-RANGE/LIST/RANGE COLUMNS /LIST COLUMNS/HASH/KEY, HASH-RANGE/LIST/RANGE COLUMNS/LIST COLUMNS/HASH/KEY, and KEY-RANGE/LIST/RANGE COLUMNS/LIST COLUMNS/HASH/KEY

Note

Other combinations of partitioning methods than the listed ones are supported for both template-based and non-template-based table groups.

Create a partitioned table group

You can create a table group only if you have tenant administrator privileges. At present, a table group supports the RANGE, LIST, RANGE COLUMNS, LIST COLUMNS, HASH, and KEY partitioning methods.

Syntax

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 
 KEY column_num  PARTITIONS INTNUM
| HASH PARTITIONS INTNUM
| RANGE {PARTITION partition_name VALUES LESS THAN range_partition_expr [, PARTITION partition_name VALUES LESS THAN range_partition_expr]...}
| LIST  {PARTITION partition_name VALUES list_partition_expr [, PARTITION partition_name VALUES list_partition_expr]...}
| RANGE COLUMNS column_num {PARTITION partition_name VALUES LESS THAN range_partition_expr, ...}
| LIST  COLUMNS column_num {PARTITION partition_name VALUES IN list_partition_expr, ...}

Parameters

Parameter	Description
tablegroup_name	The table group name. It can contain up to 64 characters in length and contain uppercase and lowercase letters, digits, and underscores (). It must start with a letter or an underscore (), and cannot contain keywords of OceanBase Database. If the name of the created table group already exists and `IF NOT EXISTS` is not specified, an error is returned.
opt_tablegroup_option_list	The partitioning method, locality, and primary zone of the table group must be the same as those of the tables in the table group. Consistent locality means that the replica type, quantity, and location must be consistent. Consistent primary zone means that the location and priority of the leaders must be consistent. Consistent partitioning method: Tables in the same table group must share the same partitioning method such as `HASH`-`RANGE` partitioning. `KEY`-partitioned tables must have the same number of referenced columns and the same number of partitions. `HASH`-partitioned tables must have the same number of partitions. `RANGE COLUMNS`-partitioned tables must have the same number of partitions, the same number of referenced columns, and the same range definitions. `RANGE`-partitioned tables must have the same number of partitions and the same range definitions. `LIST COLUMNS`-partitioned tables must have the same number of referenced columns, the same number of partitions, and the same LIST COLUMNS definitions. `LIST`-partitioned tables must have the same number of partitions and the same LIST definitions. Subpartitions in a table group must meet the preceding requirements. Notice You cannot modify a single item of a table. You must perform batch operations on the entire table group.
opt_tg_partition_option	The definition of partitioning rules of the table group. The same partitioning method as `CREATE TABLE` is used. A table group does not have specific column definitions. Therefore, you do not need to specify column names for the `KEY`, `RANGE COLUMNS`, and `LIST COLUMNS` partitioning methods. You only need to specify the number of columns for the `COLUMN_NUM` parameter.
COLUMN_NUM	The number of partitioning keys.

Examples

Create a RANGE-partitioned table group.

obclient> CREATE TABLEGROUP tR18 PARTITION BY RANGE
            (PARTITION p0 VALUES LESS THAN(1970),
             PARTITION p1 VALUES LESS THAN(1971),
             PARTITION p2 VALUES LESS THAN(1972)
             );
Query OK, 0 rows affected

Create a LIST-partitioned table group.

obclient> CREATE TABLEGROUP tg1_l8 PARTITION BY LIST
            (PARTITION p0 VALUES IN (1),
            PARTITION p1 VALUES IN (2),
            PARTITION p2 VALUES IN (3)
           );
Query OK, 0 rows affected

Create a RANGE COLUMNS-partitioned table group.

obclient> CREATE TABLEGROUP tblgroup1 PARTITION BY RANGE COLUMNS 1 
            (PARTITION p0 VALUES LESS THAN(100),
             PARTITION p1 VALUES LESS THAN(200)
            );
Query OK, 0 rows affected

Create a LIST COLUMNS-partitioned table group.

obclient> CREATE TABLEGROUP tblgroup8 PARTITION BY LIST COLUMNS 1 
            (PARTITION p_northwest VALUES IN('OR', 'WA'),
             PARTITION p_southwest VALUES IN('AZ', 'UT', 'NM')
            );
Query OK, 0 rows affected

Create a HASH-partitioned table group.

obclient> CREATE TABLEGROUP tg1_h PARTITION BY HASH PARTITIONS 10;
Query OK, 0 rows affected

Create a KEY-partitioned table group.

obclient> CREATE TABLEGROUP tblgroup1 PARTITION BY KEY 1 PARTITIONS 10;
Query OK, 0 rows affected

Create a subpartitioned table group by using a template

Syntax

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, ...}
    | RANGE COLUMNS[tg_subpartition_option] 
          {PARTITION partition_name VALUES LESS THAN range_partition_expr, ...}
    | LIST COLUMNS[tg_subpartition_option] 
          {PARTITION partition_name VALUES IN list_partition_expr, ...}
    | LIST COLUMNS column_num [tg_subpartition_option] 
          {PARTITION partition_name VALUES IN list_partition_expr, ...}

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

Examples

Create a RANGE-RANGE-subpartitioned table group by using a template.

obclient> CREATE TABLEGROUP tg2_m_rr01
            PARTITION BY RANGE
            SUBPARTITION BY RANGE
            SUBPARTITION TEMPLATE
            (SUBPARTITION mp0 VALUES LESS THAN (1970),
             SUBPARTITION mp1 VALUES LESS THAN (1972),
             SUBPARTITION mp2 VALUES LESS THAN (1973)
             )
             (PARTITION p0 VALUES LESS THAN (100),
              PARTITION p1 VALUES LESS THAN (200)
             );
Query OK, 0 rows affected

Create a RANGE-LIST-subpartitioned table group by using a template.

obclient> CREATE TABLEGROUP tg2_m_rl02
            PARTITION BY RANGE
            SUBPARTITION BY LIST
            SUBPARTITION TEMPLATE
           (SUBPARTITION mp0 VALUES IN(01),
            SUBPARTITION mp1 VALUES IN(02),
            SUBPARTITION mp2 VALUES IN(03)
             )
             (PARTITION p0 VALUES LESS THAN (100),
              PARTITION p1 VALUES LESS THAN (200)
             );
Query OK, 0 rows affected

Create a RANGE-RANGE COLUMNS-subpartitioned table group by using a template.

obclient> CREATE TABLEGROUP tg2_m_rrc03
            PARTITION BY RANGE
            SUBPARTITION BY RANGE COLUMNS 1
            SUBPARTITION TEMPLATE
            (SUBPARTITION mp0 VALUES LESS THAN (1970),
             SUBPARTITION mp1 VALUES LESS THAN (1972),
             SUBPARTITION mp2 VALUES LESS THAN (1973)
             )
             (PARTITION p0 VALUES LESS THAN (100),
              PARTITION p1 VALUES LESS THAN (200)
             );
Query OK, 0 rows affected

Create a RANGE-LIST COLUMNS-subpartitioned table group by using a template.

obclient> CREATE TABLEGROUP tg2_m_rlc04
            PARTITION BY RANGE
            SUBPARTITION BY LIST COLUMNS 1
            SUBPARTITION TEMPLATE
           (SUBPARTITION mp0 VALUES IN(01),
              SUBPARTITION mp1 VALUES IN(02),
              SUBPARTITION mp2 VALUES IN(03)
             )
             (PARTITION p0 VALUES LESS THAN (100),
              PARTITION p1 VALUES LESS THAN (200)
             );
Query OK, 0 rows affected

Create a RANGE-HASH-subpartitioned table group by using a template.

obclient> CREATE TABLEGROUP tg2_m_rh05
            PARTITION BY RANGE
            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

Create a RANGE-KEY-subpartitioned table group by using a template.

obclient>CREATE TABLEGROUP tg2_m_rck06
            PARTITION BY RANGE
            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

Create a RANGE COLUMNS-RANGE-subpartitioned table group by using a template.

obclient> CREATE TABLEGROUP tg2_m_rcr07
             PARTITION BY RANGE COLUMNS 1
             SUBPARTITION BY RANGE
             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

Create a RANGE COLUMNS-RANGE COLUMNS-subpartitioned table group by using a template.

obclient> CREATE TABLEGROUP tg2_m_rcrc08
        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

Create a RANGE COLUMNS-LIST-subpartitioned table group by using a template.

obclient> CREATE TABLEGROUP tg2_m_rcl09
            PARTITION BY RANGE COLUMNS 1
            SUBPARTITION BY LIST
            SUBPARTITION TEMPLATE
            (SUBPARTITION mp0 VALUES IN(01),
            SUBPARTITION mp1 VALUES IN(02),
            SUBPARTITION mp2 VALUES IN(03)
            )
           (PARTITION p0 VALUES LESS THAN(100),
            PARTITION p1 VALUES LESS THAN(200),
            PARTITION p2 VALUES LESS THAN(300)
           );
Query OK, 0 rows affected

Create a RANGE COLUMNS-LIST COLUMNS-subpartitioned table group by using a template.

obclient> CREATE TABLEGROUP tg2_m_rclc10
       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

Create a RANGE COLUMNS-HASH-subpartitioned table group by using a template.

obclient> CREATE TABLEGROUP tg2_m_rch11
            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

Create a RANGE COLUMNS-KEY-subpartitioned table group by using a template.

obclient> CREATE TABLEGROUP tg2_m_rck12
       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

Create a LIST-RANGE-subpartitioned table group by using a template.

obclient> CREATE TABLEGROUP tg1_lr13
            PARTITION BY LIST
            SUBPARTITION BY RANGE
            (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

Create a LIST-RANGE COLUMNS-subpartitioned table group by using a template.

obclient>CREATE TABLEGROUP tg1_lrc14
           PARTITION BY LIST
           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

Create a LIST-LIST-subpartitioned table group by using a template.

obclient>  CREATE TABLEGROUP tg2_m_ll15
            PARTITION BY LIST
            SUBPARTITION BY LIST
            SUBPARTITION TEMPLATE
            (SUBPARTITION mp0 VALUES IN(04),
             SUBPARTITION mp1 VALUES IN(05),
             SUBPARTITION mp2 VALUES IN(06)
            )
            (PARTITION p0 VALUES IN(01),
             PARTITION p1 VALUES IN(02),
             PARTITION p2 VALUES IN(03)
           );
Query OK, 0 rows affected

Create a LIST-LIST COLUMNS-subpartitioned table group by using a template.

obclient>  CREATE TABLEGROUP tg2_m_llc16
             PARTITION BY LIST
             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),
              PARTITION p1 VALUES IN(02),
              PARTITION p2 VALUES IN(03)
             );
Query OK, 0 rows affected

Create a LIST-KEY-subpartitioned table group by using a template.

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

Create a LIST-HASH-subpartitioned table group by using a template.

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

Create a LIST COLUMNS-RANGE-subpartitioned table group by using a template.

obclient> CREATE TABLEGROUP tg2_m_lcr19
                PARTITION BY LIST COLUMNS 1
                SUBPARTITION BY RANGE
                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

Create a LIST COLUMNS-RANGE COLUMNS-subpartitioned table group by using a template.

obclient> CREATE TABLEGROUP tg2_m_lcrc20
           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

Create a LIST COLUMNS-LIST-subpartitioned table group by using a template.

obclient> CREATE TABLEGROUP tg2_m_lcl21
            PARTITION BY LIST COLUMNS 1
            SUBPARTITION BY LIST
            SUBPARTITION TEMPLATE
            (SUBPARTITION mp0 VALUES IN(04),
            SUBPARTITION mp1 VALUES IN(05),
            SUBPARTITION mp2 VALUES IN(06)
            )
            (PARTITION p0 VALUES IN(01),
             PARTITION p1 VALUES IN(02),
             PARTITION p2 VALUES IN(03)
             );
Query OK, 0 rows affected

Create a LIST COLUMNS-LIST COLUMNS-subpartitioned table group by using a template.

obclient> CREATE TABLEGROUP tg2_m_lclc22
            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

Create a LIST COLUMNS-HASH-subpartitioned table group by using a template.

obclient> CREATE TABLEGROUP tg2_m_lch23
            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

Create a LIST COLUMNS-KEY-subpartitioned table group by using a template.

obclient> CREATE TABLEGROUP tg2_m_lck24
            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

Create a KEY-RANGE-subpartitioned table group by using a template.

obclient>CREATE TABLEGROUP tg2_m_kr25
            PARTITION BY  KEY 1
            SUBPARTITION BY RANGE
            SUBPARTITION TEMPLATE
            (SUBPARTITION p0 VALUES LESS THAN(100),
            SUBPARTITION p1 VALUES LESS THAN(200),
            SUBPARTITION  p2 VALUES LESS THAN(300)
            )
            PARTITIONS 5;
Query OK, 0 rows affected

Create a KEY-RANGE COLUMNS-subpartitioned table group by using a template.

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

Create a KEY-LIST-subpartitioned table group by using a template.

obclient> CREATE TABLEGROUP tg2_m_kl27
             PARTITION BY KEY 1
             SUBPARTITION BY LIST
             SUBPARTITION TEMPLATE
             (SUBPARTITION mp0 VALUES IN(01),
             SUBPARTITION mp1 VALUES IN(02),
             SUBPARTITION mp2 VALUES IN(03)
             )
             PARTITIONS 6;
Query OK, 0 rows affected

Create a KEY-LIST COLUMNS-subpartitioned table group by using a template.

obclient> CREATE TABLEGROUP tg2_m_klc28
          PARTITION BY KEY 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')
            )
            PARTITIONS 6;
Query OK, 0 rows affected

Create a HASH-RANGE-subpartitioned table group by using a template.

obclient> CREATE TABLEGROUP tg2_m_hr29
            PARTITION BY Hash
            SUBPARTITION BY RANGE
            SUBPARTITION TEMPLATE
            (SUBPARTITION p0 VALUES LESS THAN(100),
             SUBPARTITION p1 VALUES LESS THAN(200),
             SUBPARTITION  p2 VALUES LESS THAN(300)
             )
             PARTITIONS 6;
Query OK, 0 rows affected

Create a HASH-RANGE COLUMNS-subpartitioned table group by using a template.

obclient> CREATE TABLEGROUP tg2_m_hrc30
            PARTITION BY  Hash
            SUBPARTITION BY RANGE Columns 1
            SUBPARTITION TEMPLATE
            (SUBPARTITION p0 VALUES LESS THAN(100),
            SUBPARTITION p1 VALUES LESS THAN(200),
            SUBPARTITION  p2 VALUES LESS THAN(300)
            )
          PARTITIONS 6;
Query OK, 0 rows affected

Create a HASH-LIST-subpartitioned table group by using a template.

obclient> CREATE TABLEGROUP tg2_m_hl31
            PARTITION BY  Hash
            SUBPARTITION BY LIST
            SUBPARTITION TEMPLATE
            (SUBPARTITION mp0 VALUES IN(01),
            SUBPARTITION mp1 VALUES IN(02),
            SUBPARTITION mp2 VALUES IN(03)
            )
            PARTITIONS 6;
Query OK, 0 rows affected

Create a HASH-LIST COLUMNS-subpartitioned table group by using a template.

obclient> CREATE TABLEGROUP tg2_m_hlc32
            PARTITION BY Hash
            SUBPARTITION BY LIST
            SUBPARTITION TEMPLATE
            (SUBPARTITION mp0 VALUES IN(01),
            SUBPARTITION mp1 VALUES IN(02),
            SUBPARTITION mp2 VALUES IN(03)
            )
            PARTITIONS 6;
Query OK, 0 rows affected

Create a subpartitioned table group without using a template

Syntax

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 [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) ...] }
 | 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] SUBPARTITION 
  | LIST [COLUMNS]  SUBPARTITION 
  | HASH [SUBPARTITIONS int_num]

  SUBPARTITION BY 
    RANGE [COLUMNS] column_num SUBPARTITION 
  | LIST [COLUMNS] column_num SUBPARTITION 
  | HASH [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]...

Examples

Create a RANGE-RANGE-subpartitioned table group without using a template.

obclient> CREATE TABLEGROUP tg1_rr01
            PARTITION BY RANGE
            SUBPARTITION BY RANGE
            (
            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

Create a RANGE-LIST-subpartitioned table group without using a template.

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

Create a RANGE-RANGE COLUMNS-subpartitioned table group without using a template.

obclient> CREATE TABLEGROUP tg1_rrc03
            PARTITION BY RANGE
            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

Create a RANGE-LIST COLUMNS-subpartitioned table group without using a template.

obclient> CREATE TABLEGROUP tg1_rlc04
            PARTITION BY RANGE
            SUBPARTITION BY List 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

Create a RANGE-HASH-subpartitioned table group without using a template.

obclient> CREATE TABLEGROUP tg1_rh05
            PARTITION BY RANGE
            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

Create a RANGE-KEY-subpartitioned table group without using a template.

obclient>CREATE TABLEGROUP tg2_f_rk06
            PARTITION BY RANGE
            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

Create a RANGE COLUMNS-RANGE-subpartitioned table group without using a template.

obclient> CREATE TABLEGROUP tg1_rcr07
            PARTITION BY RANGE COLUMNS 1
            SUBPARTITION BY RANGE
            (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

Create a RANGE COLUMNS-RANGE COLUMNS-subpartitioned table group without using a template.

obclient> CREATE TABLEGROUP tg1_rr08
            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

Create a RANGE COLUMNS-LIST-subpartitioned table group without using a template.

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

Create a RANGE COLUMNS-LIST COLUMNS-subpartitioned table group without using a template.

obclient>CREATE TABLEGROUP tg1_rclc10
            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

Create a RANGE COLUMNS-HASH-subpartitioned table group without using a template.

obclient> CREATE TABLEGROUP tg1_rch11
            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

Create a RANGE COLUMNS-KEY-subpartitioned table group without using a template.

obclient> CREATE TABLEGROUP tg1_rch12
            PARTITION BY RANGE COLUMNS 1
            SUBPARTITION BY KEY 1(
            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

Create a LIST COLUMNS-RANGE-subpartitioned table group without using a template.

obclient> CREATE TABLEGROUP tg1_lcr13
            PARTITION BY LIST COLUMNS 1
            SUBPARTITION BY RANGE
            (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

Create a LIST COLUMNS-RANGE COLUMNS-subpartitioned table group without using a template.

obclient> CREATE TABLEGROUP tg1_lrc14
            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

Create a LIST COLUMNS-LIST-subpartitioned table group without using a template.

obclient> CREATE TABLEGROUP tg1_lcl15
            PARTITION BY LIST COLUMNS 1
            SUBPARTITION BY LIST
            (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

Create a LIST COLUMNS-LIST COLUMNS-subpartitioned table group without using a template.

obclient> CREATE TABLEGROUP tg1_lclc16
            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

Create a LIST COLUMNS-HASH-subpartitioned table group without using a template.

obclient> CREATE TABLEGROUP tg1_lch17
            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

Create a LIST COLUMNS-KEY-subpartitioned table group without using a template.

obclient> CREATE TABLEGROUP tg1_lck18
            PARTITION BY LIST COLUMNS 1
            SUBPARTITION BY KEY 1
            (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