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

Create a subpartitioned table

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

Subpartitioning is a technique that partitions a table in two dimensions. For example, it is frequently used in scenarios that involve bills.

Partitioning types

OceanBase Database in MySQL mode supports the HASH, RANGE, LIST, KEY, RANGE COLUMNS, and LIST COLUMNS partitioning methods, and a combination of any two of them can be used for subpartitioning. The following table describes the support for subpartitioned table creation.

Subpartitioning type	Create a subpartitioned table by using a template	Create a subpartitioned table without using a template
RANGE-RANGE, RANGE-RANGE COLUMNS, RANGE-LIST, RANGE-LIST COLUMNS, RANGE-HASH, and RANGE-KEY	Supported	Supported
RANGE COLUMNS-RANGE, RANGE COLUMNS-RANGE COLUMNS, RANGE COLUMNS-LIST, RANGE COLUMNS-LIST COLUMNS, RANGE COLUMNS-HASH, and RANGE COLUMNS-KEY	Supported	Supported
LIST-RANGE, LIST-RANGE COLUMNS, LIST-LIST, LIST-LIST COLUMNS, LIST-HASH, and LIST-KEY	Supported	Supported
LIST COLUMNS-RANGE, LIST COLUMNS-RANGE COLUMNS, LIST COLUMNS-LIST, LIST COLUMNS-LIST COLUMNS, LIST COLUMNS-HASH, and LIST COLUMNS-KEY	Supported	Supported
HASH-RANGE, HASH-RANGE COLUMNS, HASH-LIST, HASH-LIST COLUMNS, HASH-HASH, and HASH-KEY	Supported	Supported
KEY-RANGE, KEY-RANGE COLUMNS, KEY-LIST, KEY-LIST COLUMNS, KEY-HASH, and KEY-KEY	Supported	Supported

Create a subpartitioned table

When you create a subpartitioned table, you can choose whether to use a template.

SQL syntax to create a subpartitioned table by using a template

CREATE TABLE [IF NOT EXISTS] table_name(column_option_list)
  [table_option_list] partition_option_list;

column_option_list:
  column_name column_type [, column_name column_type]

table_option_list:
table_option [table_option]

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

partition_option_list:
PARTITION BY
  RANGE {(expression) | COLUMNS (column_name_list)}{subpartition_option} (range_partition_list)
 | LIST {(expression) | COLUMNS (column_name_list)}{subpartition_option} (list_partition_list)
 | HASH(expression) {subpartition_option} { (hash_partition_list)
                                           | PARTITIONS partition_count }
 | KEY(column_name_list) {subpartition_option} { (key_partition_list)
                                                | PARTITIONS partition_count }

subpartition_option:
  SUBPARTITION BY
     RANGE {(expression) | COLUMNS (column_name_list)} SUBPARTITION TEMPLATE (range_subpartition_list)
    | LIST {(expression) | COLUMNS (column_name_list)} SUBPARTITION TEMPLATE (list_subpartition_list)
    | HASH(expression) { SUBPARTITION TEMPLATE (hash_subpartition_list)
                       | SUBPARTITIONS subpartition_count}
    | KEY(column_name_list) { SUBPARTITION TEMPLATE (key_subpartition_list)
                            | SUBPARTITIONS subpartition_count}

range_partition_list:
    range_partition [, range_partition ...]

range_partition:
    PARTITION partition_name VALUES LESS THAN {(expression_list) | MAXVALUE}

range_subpartition_list:
    range_subpartition [, range_subpartition ...]

range_subpartition:
    SUBPARTITION subpartition_name VALUES LESS THAN {(expression_list) | MAXVALUE}

list_partition_list:
    list_partition [, list_partition ...]

list_partition:
    PARTITION partition_name VALUES IN {(expression_list) | DEFAULT}

list_subpartition_list:
    list_subpartition [, list_subpartition ...]

list_subpartition:
    SUBPARTITION subpartition_name VALUES IN {(expression_list) | DEFAULT}

hash_partition_list:
    hash_partition [, hash_partition ...]

key_partition_list:
    key_partition [, key_partition ...]

hash_partition | key_partition:
    PARTITION partition_name

hash_subpartition_list:
    hash_subpartition [, hash_subpartition ...]

key_subpartition_list:
    key_subpartition [, key_subpartition ...]

hash_subpartition | key_subpartition:
    SUBPARTITION subpartition_name

expression_list:
    expression [, expression ...]

column_name_list:
    column_name [, column_name ...]

partition_count | subpartition_count:
    INT_VALUE

Note

When you use a template to create a subpartitioned table, the template defines subpartitions under each partition. In this case, subpartitions under each partition share the same definition.

For a template-based subpartitioned table, the subpartitions are named in the ($part_name)s($subpart_name) format. For the tbl_rr table in the following example, the p0 partition has three subpartitions: p0smp1, p0smp2, and p0smp3.

obclient> CREATE TABLE tbl_rr(col1 INT,col2 INT,col3 INT)
       PARTITION BY RANGE(col1)
       SUBPARTITION BY RANGE(col2)
       SUBPARTITION TEMPLATE
        (SUBPARTITION mp1 VALUES LESS THAN(100),
         SUBPARTITION mp2 VALUES LESS THAN(200),
         SUBPARTITION mp3 VALUES LESS THAN(300)
        )
        (PARTITION p0 VALUES LESS THAN(100),
         PARTITION p1 VALUES LESS THAN(200),
         PARTITION p2 VALUES LESS THAN(300)
        );
Query OK, 0 rows affected

SQL syntax to create a subpartitioned table without using a template

CREATE TABLE [IF NOT EXISTS] table_name(column_option_list)
  [table_option_list] partition_option_list;

column_option_list:
  column_name column_type [, column_name column_type]

table_option_list:
table_option [table_option]

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

partition_option_list:
PARTITION BY
  RANGE {(expression) | COLUMNS (column_name_list)}{subpartition_option}
    { range_partition_option (subpartition_option_list)
       [, range_partition_option (subpartition_option_list)]...
     }
 | LIST {(expression) | COLUMNS (column_name_list)}{subpartition_option}
     { list_partition_option (subpartition_option_list)
       [, list_partition_option (subpartition_option_list)]...
      }
 | HASH(expression) {subpartition_option}
    { hash_partition_option (subsubpartition_option_list)
     [, hash_partition_option (subsubpartition_option_list)]...
     }
 | KEY(column_name_list) {subpartition_option}
     { key_partition_option (subsubpartition_option_list)
       [, key_partition_option (subsubpartition_option_list)]
      }

subpartition_option:
SUBPARTITION BY
  RANGE {(expression) | COLUMNS (column_name_list)}
 | LIST {(expression) | COLUMNS (column_name_list)}
 | HASH (expression)
 | KEY(column_name_list)

subpartition_option_list:
    range_partition_option_list
  | list_partition_option_list
  | hash_partition_option_list
  | key_partition_option_list

range_partition_option_list:
  range_partition_option [, range_partition_option ]...

list_partition_option_list:
  list_partition_option [, list_partition_option ]...

hash_partition_option_list:
  hash_partition_option [, hash_partition_option ]...

key_partition_option_list:
  key_partition_option [, key_partition_option ]...

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

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

hash_partition_option_list:
  SUBPARTITION subpartition_name
    [, SUBPARTITION subpartition_name]...

key_partition_option_list:
  SUBPARTITION subpartition_name
    [, SUBPARTITION subpartition_name]...

Parameters

Parameter	Description
table_name	The table name.
column_name	The column name.
column_type	The data type of the column.
locality_name	The distribution of replicas across the zones. For example, `F@z1,F@z2,F@z3,R@z4` indicates that `z1`, `z2`, and `z3` are full-featured replicas, and `z4` is a read-only replica.
primary_zone_name	The primary zone where the leader resides.
partition_name	The partition name.
subpartition_name	The subpartition name.
INT_VALUE	The number of HASH or KEY subpartitions.

Examples

Create a subpartitioned table by using a template

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

obclient> CREATE TABLE t_m_rcrc(col1 INT,col2 INT)
       PARTITION BY RANGE COLUMNS(col1)
       SUBPARTITION BY RANGE COLUMNS(col2)
       SUBPARTITION TEMPLATE
        (SUBPARTITION mp0 VALUES LESS THAN(1000),
         SUBPARTITION mp1 VALUES LESS THAN(2000),
         SUBPARTITION mp2 VALUES LESS THAN(3000)
        )
        (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 by using a template.

obclient> CREATE TABLE t_m_rclc(col1 INT,col2 INT)
       PARTITION BY RANGE COLUMNS(col1)
       SUBPARTITION BY LIST COLUMNS(col2)
       SUBPARTITION TEMPLATE
        (SUBPARTITION mp0 VALUES IN(1,3),
         SUBPARTITION mp1 VALUES IN(4,6),
         SUBPARTITION mp2 VALUES IN(7)
        )
        (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 by using a template.

obclient> CREATE TABLE t2_m_rch(col1 INT,col2 INT)
       PARTITION BY RANGE COLUMNS(col1)
       SUBPARTITION BY HASH(col2) 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 by using a template.

obclient> CREATE TABLE t2_m_rck(col1 INT,col2 INT)
       PARTITION BY RANGE COLUMNS(col1)
       SUBPARTITION BY KEY(col2) 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 by using a template.

obclient> CREATE TABLE t2_m_lcrc(col1 INT,col2 INT)
       PARTITION BY LIST COLUMNS(col1)
       SUBPARTITION BY RANGE COLUMNS(col2)
       SUBPARTITION TEMPLATE
        (SUBPARTITION mp0 VALUES LESS THAN(100),
         SUBPARTITION mp1 VALUES LESS THAN(200),
         SUBPARTITION mp2 VALUES LESS THAN(300)
        )
        (PARTITION p0 VALUES IN(1,3),
         PARTITION p1 VALUES IN(4,6),
         PARTITION p2 VALUES IN(7,9)
        );
Query OK, 0 rows affected

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

obclient> CREATE TABLE t2_m_lclc(col1 INT,col2 INT)
       PARTITION BY LIST COLUMNS(col1)
       SUBPARTITION BY LIST COLUMNS(col2)
       SUBPARTITION TEMPLATE
        (SUBPARTITION mp0 VALUES IN(2),
         SUBPARTITION mp1 VALUES IN(5),
         SUBPARTITION mp2 VALUES IN(8)
        )
        (PARTITION p0 VALUES IN(1,3),
         PARTITION p1 VALUES IN(4,6),
         PARTITION p2 VALUES IN(7,9)
        );
Query OK, 0 rows affected

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

obclient> CREATE TABLE t2_m_lch(col1 INT,col2 INT)
       PARTITION BY LIST COLUMNS(col1)
       SUBPARTITION BY HASH(col2) SUBPARTITIONS 5
        (PARTITION p0 VALUES IN(100),
         PARTITION p1 VALUES IN(200),
         PARTITION p2 VALUES IN(300)
        );
Query OK, 0 rows affected

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

obclient> CREATE TABLE t2_m_lck(col1 INT,col2 INT)
       PARTITION BY LIST COLUMNS(col1)
       SUBPARTITION BY KEY(col2)
        SUBPARTITIONS 3
        (PARTITION p0 VALUES IN(100),
         PARTITION p1 VALUES IN(200),
         PARTITION p2 VALUES IN(300)
        );
Query OK, 0 rows affected

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

obclient> CREATE TABLE tbl2_m_hr(col1 INT,col2 INT,col3 INT)
       PARTITION BY HASH(col1)
       SUBPARTITION BY RANGE(col2)
       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 HASH-LIST-subpartitioned table by using a template.

obclient> CREATE TABLE tbl2_m_hl(col1 INT,col2 INT,col3 INT)
       PARTITION BY HASH(col1)
       SUBPARTITION BY LIST(col2)
       SUBPARTITION TEMPLATE
        (SUBPARTITION p0 VALUES IN(1,3),
         SUBPARTITION p1 VALUES IN(4,6),
         SUBPARTITION p2 VALUES IN(7,9)
         )
        PARTITIONS 5;
Query OK, 0 rows affected

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

obclient> CREATE TABLE tbl2_m_hh(col1 INT,col2 INT,col3 INT)
       PARTITION BY HASH(col1)
       SUBPARTITION BY HASH(col2)
       SUBPARTITION TEMPLATE
        (SUBPARTITION sp0,
         SUBPARTITION sp1,
         SUBPARTITION sp2
         )
        PARTITIONS 5;
Query OK, 0 rows affected

Create a HASH-KEY-subpartitioned table by using a template.

obclient> CREATE TABLE tbl2_m_hk(col1 INT,col2 INT,col3 INT)
       PARTITION BY HASH(col1)
       SUBPARTITION BY KEY(col2)
       SUBPARTITION TEMPLATE
        (SUBPARTITION sp0,
         SUBPARTITION sp1,
         SUBPARTITION sp2
         )
        PARTITIONS 5;
Query OK, 0 rows affected

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

obclient> CREATE TABLE tbl2_m_krc(col1 INT,col2 INT,col3 INT)
       PARTITION BY KEY(col1)
       SUBPARTITION BY RANGE COLUMNS(col2)
       SUBPARTITION TEMPLATE
        (SUBPARTITION sp0 VALUES LESS THAN(100),
         SUBPARTITION sp1 VALUES LESS THAN(200),
         SUBPARTITION sp2 VALUES LESS THAN(300)
         )
        (PARTITION p0,
         PARTITION p1,
         PARTITION p2
         );
Query OK, 0 rows affected

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

obclient> CREATE TABLE tbl2_m_klc(col1 INT,col2 INT,col3 INT)
       PARTITION BY KEY(col1)
       SUBPARTITION BY LIST COLUMNS(col2)
       SUBPARTITION TEMPLATE
        (SUBPARTITION p0 VALUES IN(1,3),
         SUBPARTITION p1 VALUES IN(4,6),
         SUBPARTITION p2 VALUES IN(7,9)
        )
       PARTITIONS 5;
Query OK, 0 rows affected

Create a KEY-HASH-subpartitioned table by using a template.

obclient> CREATE TABLE tbl2_m_kh(col1 INT,col2 INT,col3 INT)
       PARTITION BY KEY(col1)
       SUBPARTITION BY HASH(col2)
       SUBPARTITION TEMPLATE
        (SUBPARTITION sp0,
         SUBPARTITION sp1,
         SUBPARTITION sp2
         )
        PARTITIONS 5;
Query OK, 0 rows affected

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

obclient> CREATE TABLE tbl2_m_kk(col1 INT,col2 INT,col3 INT)
       PARTITION BY KEY(col1)
       SUBPARTITION BY KEY(col2)
       SUBPARTITION TEMPLATE
        (SUBPARTITION sp0,
         SUBPARTITION sp1,
         SUBPARTITION sp2
         )
        PARTITIONS 5;
Query OK, 0 rows affected

Create a subpartitioned table without using a template

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

obclient> CREATE TABLE t2_f_rr(col1 INT,col2 TIMESTAMP)
       PARTITION BY RANGE(col1)
       SUBPARTITION BY RANGE(UNIX_TIMESTAMP(col2))
        (PARTITION p0 VALUES LESS THAN(100)
           (SUBPARTITION sp0 VALUES LESS THAN(UNIX_TIMESTAMP('2021/04/01')),
            SUBPARTITION sp1 VALUES LESS THAN(UNIX_TIMESTAMP('2021/07/01')),
            SUBPARTITION sp2 VALUES LESS THAN(UNIX_TIMESTAMP('2021/10/01')),
            SUBPARTITION sp3 VALUES LESS THAN(UNIX_TIMESTAMP('2022/01/01'))
           ),
         PARTITION p1 VALUES LESS THAN(200)
           (SUBPARTITION sp4 VALUES LESS THAN(UNIX_TIMESTAMP('2021/04/01')),
            SUBPARTITION sp5 VALUES LESS THAN(UNIX_TIMESTAMP('2021/07/01')),
            SUBPARTITION sp6 VALUES LESS THAN(UNIX_TIMESTAMP('2021/10/01')),
            SUBPARTITION sp7 VALUES LESS THAN(UNIX_TIMESTAMP('2022/01/01'))
            )
         );
Query OK, 0 rows affected

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

obclient> CREATE TABLE t2_f_rclc (col1 INT,col2 INT)
       PARTITION BY RANGE COLUMNS(col1)
       SUBPARTITION BY LIST COLUMNS(col2)
       (PARTITION p0 VALUES LESS THAN(100)
         (SUBPARTITION sp0 VALUES IN(1,3),
          SUBPARTITION sp1 VALUES IN(4,6),
          SUBPARTITION sp2 VALUES IN(7,9)),
        PARTITION p1 VALUES LESS THAN(200)
         (SUBPARTITION sp3 VALUES IN(1,3),
          SUBPARTITION sp4 VALUES IN(4,6),
          SUBPARTITION sp5 VALUES IN(7,9))
       );
Query OK, 0 rows affected

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

obclient> CREATE TABLE t2_f_rch (col1 INT,col2 INT)
       PARTITION BY RANGE(col1)
       SUBPARTITION BY HASH(col2)
       (PARTITION p0 VALUES LESS THAN(100)
         (SUBPARTITION sp0,
          SUBPARTITION sp1,
          SUBPARTITION sp2),
        PARTITION p1 VALUES LESS THAN(200)
         (SUBPARTITION sp3,
          SUBPARTITION sp4,
          SUBPARTITION sp5)
       );
Query OK, 0 rows affected

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

obclient> CREATE TABLE t2_f_hr (col1 INT,col2 INT)
       PARTITION BY Hash(col1)
       SUBPARTITION BY RANGE(col2)
         (PARTITION p1
          (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 p2
          (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 HASH-LIST-subpartitioned table without using a template.

obclient> CREATE TABLE t2_f_hl (col1 INT,col2 INT)
       PARTITION BY HASH(col1)
       SUBPARTITION BY LIST(col2)
        (PARTITION p1
          (SUBPARTITION sp0 VALUES IN (2020)
          ,SUBPARTITION sp1 VALUES IN (2021)
          ,SUBPARTITION sp2 VALUES IN (2022)
          ,SUBPARTITION sp3 VALUES IN (2023)
           ),
         PARTITION p2
          (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 HASH-KEY-subpartitioned table without using a template.

obclient> CREATE TABLE t2_f_hk (col1 INT,col2 INT)
       PARTITION BY HASH(col1)
       SUBPARTITION BY KEY(col2)
        (PARTITION p1
         (SUBPARTITION sp0
         ,SUBPARTITION sp1
         ,SUBPARTITION sp2
         ,SUBPARTITION sp3
         ),
         PARTITION p2
         (SUBPARTITION sp4
         ,SUBPARTITION sp5
         ,SUBPARTITION sp6
         ,SUBPARTITION sp7
         )
        );
Query OK, 0 rows affected