Create a subpartitioned table group

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

Subpartitioning types

In OceanBase Database in MySQL mode, you can choose whether to use a template when you create a subpartitioned table group.

Considerations

You cannot separately manage partitions of tables in a table group. Instead, you must manage the partitions by using the table group in a unified manner.

SQL syntaxes to create a subpartitioned table group

SQL syntax to create a subpartitioned table group by using a template

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
      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, ...}
    | KEY column_num tg_subpartition_option PARTITIONS int_num
    | HASH tg_subpartition_option PARTITIONS INTNUM

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

SQL syntax to create a subpartitioned table group without using a template

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) ...] }

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

Syntax description

• opt_tablegroup_option_list: the partitioning method of the table group. The locality and primary zone information of the table group must be consistent with that of the tables in the table group. The locality specifies the replica type, quantity, and location. The primary zone specifies the leader location and priority.

• opt_tg_partition_option: the partitioning rules of the table group. The partitioning rules of the table group must be consistent with those of the tables in the table group. The partitioning type and number of partitions are specified by the rules.

  In RANGE COLUMNS, LIST COLUMNS, and KEY partitioning, you must specify the number of columns by setting the COLUMN_NUM parameter.

Examples

Create a subpartitioned table group without using a template

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

  obclient> CREATE TABLEGROUP tg2_f_rr
            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 COLUMNS-KEY-subpartitioned table group without using a template.

  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
  

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

  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
  

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

  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
  

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

  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
  

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

  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
  

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

  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
  

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

  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
  

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

  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
  

Create a subpartitioned table group by using a template

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

  obclient> CREATE TABLEGROUP tg2_m_rl
            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 COLUMNS-RANGE COLUMNS-subpartitioned table group by using a template.

  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
  

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

  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
  

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

  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
  

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

  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
  

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

  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
  

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

  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
  

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

  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
  

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

  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