Create a subpartitioned table

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

Subpartitioning types

OceanBase Database in Oracle mode supports the HASH, RANGE, and LIST 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.

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)
  partition_option_list;

column_option_list:
  column_name column_type [, column_name column_type]

partition_option:
  PARTITION BY
    RANGE(column_name){subpartition_option} (range_partition_list)
   | LIST(expression){subpartition_option} (list_partition_list)
   | HASH(expression){subpartition_option} { (hash_partition_list)
                                             | PARTITIONS partition_count }

subpartition_option:
  SUBPARTITION BY
    RANGE(column_name) SUBPARTITION TEMPLATE(range_subpartition_list)
   | LIST(expression) SUBPARTITION TEMPLATE(list_subpartition_list)
   | HASH(expression) { SUBPARTITION TEMPLATE (hash_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 {(expression_list) | DEFAULT}

list_subpartition_list:
  list_subpartition [, list_subpartition ...]

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

hash_partition_list:
    hash_partition [, hash_partition ...]

hash_partition:
    PARTITION partition_name

hash_subpartition_list:
    hash_subpartition [, hash_subpartition ...]

hash_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 format of ($part_name)s($subpart_name). For example, in the t_range_range table created by using the following statement, the partition p0 has three subpartitions: p0smp1, p0smp2, and p0smp3.

  obclient> CREATE TABLE t_range_range(col1 INT,col2 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(2020),
          PARTITION p1 VALUES LESS THAN(2021),
          PARTITION p2 VALUES LESS THAN(2022)
          );
  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)
  partition_option_list;

column_option_list:
  column_name column_type [, column_name column_type]

partition_option_list:
  PARTITION BY
    RANGE(column_name){subpartition_option}
      { range_partition_option (subpartition_option_list)
        [, range_partition_option (subpartition_option_list) ...]
       }
   | LIST(expression){subpartition_option}
       { list_partition_option (subpartition_option_list)
        [, list_partition_option (subpartition_option_list) ...]
        }
   | HASH(expression) {subpartition_option}
      { hash_partition_option (subpartition_option_list)
       [, hash_partition_option (subpartition_option_list) ...]
       }

subpartition_option:
  SUBPARTITION BY { RANGE(column_name) | LIST(expression) | HASH(expression) }

subpartition_option_list:
  range_partition_option_list | list_partition_option_list | hash_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 ...]

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 list_partition_expr
  [, SUBPARTITION subpartition_name VALUES list_partition_expr ...]

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

Note

For a non-template-based subpartitioned table, you can define the subpartitioning method for each partition. You can choose whether to use the same subpartitioning method for different partitions.

Parameters

Examples

Create a subpartitioned table by using a template

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

  obclient> CREATE TABLE t2_m_rr(col1 NUMBER,col2 NUMBER)
         PARTITION BY RANGE(col1)
         SUBPARTITION BY RANGE(col2)
         SUBPARTITION TEMPLATE
           (SUBPARTITION mp0 VALUES LESS THAN(2020),
            SUBPARTITION mp1 VALUES LESS THAN(2021),
            SUBPARTITION mp2 VALUES LESS THAN(2022)
            )
           (PARTITION p0 VALUES LESS THAN(100),
            PARTITION p1 VALUES LESS THAN(200)
            );
  Query OK, 0 rows affected
  

  View the t2_m_rr subpartitioned table.

  obclient> SHOW CREATE TABLE t2_m_rr\G
  *************************** 1. row ***************************
         TABLE: T2_M_RR
  CREATE TABLE: CREATE TABLE "T2_M_RR" (
    "COL1" NUMBER(38),
    "COL2" NUMBER(38)
  ) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
   partition by range(col1) subpartition by range(col2) subpartition template (
  subpartition MP0 values less than (2020),
  subpartition MP1 values less than (2021),
  subpartition MP2 values less than (2022))
  (partition P0 values less than (100),
  partition P1 values less than (200))
  1 row in set
  

  Insert data into the t2_m_rr table.

  obclient> INSERT INTO t2_m_rr VALUES (1,1818);
  Query OK, 1 row affected
  

  View the t2_m_rr subpartitioned table.

  obclient> SELECT * FROM t2_m_rr partition(p0);
  +------+------+
  | COL1 | COL2 |
  +------+------+
  |    1 | 1818 |
  +------+------+
  1 row in set
  

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

  obclient> CREATE TABLE t2_m_rl(col1 NUMBER,col2 VARCHAR2(50))
         PARTITION BY RANGE(col1)
         SUBPARTITION BY LIST(col2)
         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
  

  View the t2_m_rl subpartitioned table.

  obclient> SHOW CREATE TABLE t2_m_rl\G
  *************************** 1. row ***************************
         TABLE: T2_M_RL
  CREATE TABLE: CREATE TABLE "T2_M_RL" (
    "COL1" NUMBER,
    "COL2" VARCHAR2(50)
  ) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
   partition by range(col1) subpartition by list(col2) 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))
  1 row in set
  

  Insert data into the t2_m_rl subpartitioned table.

  obclient> INSERT INTO t2_m_rl VALUES (100,'01');
  Query OK, 1 row affected
  

  View the t2_m_rl subpartitioned table.

  null
  +------+------+
  | COL1 | COL2 |
  +------+------+
  |  100 | 01   |
  +------+------+
  1 row in set
  

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

  obclient> CREATE TABLE t2_m_rh(col1 NUMBER,col2 VARCHAR2(50))
         PARTITION BY RANGE(col1)
         SUBPARTITION BY HASH(col2) SUBPARTITIONS 5
          (PARTITION p0 VALUES LESS THAN(100),
           PARTITION p1 VALUES LESS THAN(200)
           );
  Query OK, 0 rows affected
  

  View the t2_m_rh subpartitioned table.

  obclient>SHOW CREATE TABLE t2_m_rh\G
  *************************** 1. row ***************************
         TABLE: T2_M_RH
  CREATE TABLE: CREATE TABLE "T2_M_RH" (
    "COL1" NUMBER(38),
    "COL2" VARCHAR2(50)
  ) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
   partition by range(col1) subpartition by hash(col2) subpartition template (
  subpartition P0,
  subpartition P1,
  subpartition P2,
  subpartition P3,
  subpartition P4)
  (partition P0 values less than (100),
  partition P1 values less than (200))
  1 row in set
  

  Insert data into the t2_m_rh subpartitioned table.

  INSERT INTO t2_m_rh VALUES (100,'01');
  

  View the t2_m_rh subpartitioned table.

  obclient> SELECT * FROM t2_m_rh partition(p1);
  +------+------+
  | COL1 | COL2 |
  +------+------+
  |  100 | 01   |
  +------+------+
  1 row in set
  

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

  obclient> CREATE TABLE t2_m_lr(col1 NUMBER,col2 varchar2(50))
         PARTITION BY LIST(col2)
         SUBPARTITION BY RANGE(col1)
         SUBPARTITION TEMPLATE
          (SUBPARTITION mp0 VALUES LESS THAN(100),
           SUBPARTITION mp1 VALUES LESS THAN(200),
           SUBPARTITION mp2 VALUES LESS THAN(300)
           )
          (PARTITION p0 VALUES('01'),
           PARTITION p1 VALUES('02')
           );
  Query OK, 0 rows affected
  

  View the t2_m_lr subpartitioned table.

  obclient> SHOW CREATE TABLE t2_m_lr\G
  *************************** 1. row ***************************
         TABLE: T2_M_LR
  CREATE TABLE: CREATE TABLE "T2_M_LR" (
    "COL1" NUMBER(38),
    "COL2" VARCHAR2(50)
  ) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
   partition by list(col2) subpartition by range(col1) subpartition template (
  subpartition MP0 values less than (100),
  subpartition MP1 values less than (200),
  subpartition MP2 values less than (300))
  (partition P0 values  ('01'),
  partition P1 values  ('02'))
  1 row in set
  

  Insert data into the t2_m_lr subpartitioned table.

  obclient> INSERT INTO t2_m_lr VALUES (100,'01');
  Query OK, 1 row affected
  

  View the t2_m_lh subpartitioned table.

  obclient> SELECT * FROM t2_m_lr partition(p0);
  +------+------+
  | COL1 | COL2 |
  +------+------+
  |  100 | 01   |
  +------+------+
  1 row in set
  

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

  obclient> CREATE TABLE t2_m_ll(col1 NUMBER,col2 varchar2(50))
         PARTITION BY LIST(col1)
         SUBPARTITION BY LIST(col2)
         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
  

  View the t2_m_ll subpartitioned table.

  obclient> SHOW CREATE TABLE t2_m_ll\G
  *************************** 1. row ***************************
         TABLE: T2_M_LL
  CREATE TABLE: CREATE TABLE "T2_M_LL" (
    "COL1" NUMBER,
    "COL2" VARCHAR2(50)
  ) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
   partition by list(col1) subpartition by list(col2) subpartition template (
  subpartition MP0 values  ('A'),
  subpartition MP1 values  ('B'),
  subpartition MP2 values  ('C'))
  (partition P0 values  (1),
  partition P1 values  (2))
  1 row in set
  

  Insert data into the t2_m_ll subpartitioned table.

  obclient> INSERT INTO t2_m_ll VALUES (1,'A');
  Query OK, 1 row affected
  

  View the t2_m_ll subpartitioned table.

  obclient> SELECT * FROM t2_m_ll partition(p0);
  +------+------+
  | COL1 | COL2 |
  +------+------+
  |   1  |  A   |
  +------+------+
  1 row in set
  

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

  obclient> CREATE TABLE t2_m_lh(col1 NUMBER,col2 VARCHAR2(50))
         PARTITION BY LIST(col1)
         SUBPARTITION BY HASH(col2) SUBPARTITIONS 5
          (PARTITION p0 VALUES('01'),
           PARTITION p1 VALUES('02')
           );
  Query OK, 0 rows affected
  

  View the t2_m_lh subpartitioned table.

  obclient> SHOW CREATE TABLE t2_m_lh\G
  *************************** 1. row ***************************
         TABLE: T2_M_LH
  CREATE TABLE: CREATE TABLE "T2_M_LH" (
    "COL1" NUMBER,
    "COL2" VARCHAR2(50)
  ) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
   partition by list(col1) subpartition by hash(col2) subpartition template (
  subpartition P0,
  subpartition P1,
  subpartition P2,
  subpartition P3,
  subpartition P4)
  (partition P0 values (1),
  partition P1 values (2))
  1 row in set
  

  Insert data into the t2_m_lh subpartitioned table.

  obclient> INSERT INTO t2_m_lh VALUES (1,'A');
  Query OK, 1 row affected
  

  View the t2_m_lh subpartitioned table.

  obclient> SELECT * FROM t2_m_lh partition(p0);
  +------+------+
  | COL1 | COL2 |
  +------+------+
  |    1 | A    |
  +------+------+
  1 row in set
  

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

  obclient> CREATE TABLE tbl2_m_hr(col1 NUMBER,col2 NUMBER,col3 NUMBER)
         PARTITION BY HASH(col1)
         SUBPARTITION BY RANGE(col2)
         SUBPARTITION TEMPLATE
          (SUBPARTITION sp0 VALUES LESS THAN(100),
           SUBPARTITION sp1 VALUES LESS THAN(200),
           SUBPARTITION sp2 VALUES LESS THAN(300)
          )
          PARTITIONS 5;
  Query OK, 0 rows affected
  

  View the created subpartitioned table tbl2_m_hr.

  obclient> SHOW CREATE TABLE tbl2_m_hr\G
  *************************** 1. row ***************************
         TABLE: TBL2_M_HR
  CREATE TABLE: CREATE TABLE "TBL2_M_HR" (
    "COL1" NUMBER,
    "COL2" NUMBER,
    "COL3" NUMBER
  ) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
   partition by hash(col1) subpartition by range(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,
  partition P3,
  partition P4)
  1 row in set
  

  Insert data into the tbl2_m_hr table.

  obclient> INSERT INTO tbl2_m_hr VALUES (100,1,1);
  Query OK, 1 row affected
  

  View the tbl2_m_hr subpartitioned table.

  obclient> SELECT * FROM tbl2_m_hr partition(p1);
  +------+------+------+
  | COL1 | COL2 | COL3 |
  +------+------+------+
  |  100 |    1 |    1 |
  +------+------+------+
  1 row in set
  

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

  obclient> CREATE TABLE tbl2_m_hl(col1 NUMBER,col2 NUMBER,col3 NUMBER)
         PARTITION BY HASH(col1)
         SUBPARTITION BY LIST(col2)
         SUBPARTITION TEMPLATE
          (SUBPARTITION sp0 VALUES(100),
           SUBPARTITION sp1 VALUES(200),
           SUBPARTITION sp2 VALUES(300)
           )
          PARTITIONS 5;
  Query OK, 0 rows affected
  

  View the created subpartitioned table tbl2_m_hl.

  obclient> SHOW CREATE TABLE tbl2_m_hl\G
  *************************** 1. row ***************************
         TABLE: TBL2_M_HL
  CREATE TABLE: CREATE TABLE "TBL2_M_HL" (
    "COL1" NUMBER,
    "COL2" NUMBER,
    "COL3" NUMBER
  ) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
   partition by hash(col1) subpartition by list(col2) subpartition template (
  subpartition SP0 values  (100),
  subpartition SP1 values  (200),
  subpartition SP2 values  (300))
  (partition P0,
  partition P1,
  partition P2,
  partition P3,
  partition P4)
  1 row in set
  

  Insert data into the tbl2_m_hl subpartitioned table.

  obclient> INSERT INTO tbl2_m_hl VALUES (100,100,100);
  Query OK, 1 row affected
  

  View the tbl2_m_hl subpartitioned table.

  obclient>  SELECT * FROM tbl2_m_hl partition(P1);
  +------+------+------+
  | COL1 | COL2 | COL3 |
  +------+------+------+
  |  100 |  100 |  100 |
  +------+------+------+
  1 row in set
  

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

  obclient> CREATE TABLE tbl2_m_hh(col1 NUMBER,col2 NUMBER,col3 NUMBER)
         PARTITION BY HASH(col1)
         SUBPARTITION BY HASH(col2)
          SUBPARTITIONS 3
          PARTITIONS 5;
  Query OK, 0 rows affected
  

  View the created subpartitioned table tbl2_m_hh.

  obclient> SHOW CREATE TABLE tbl2_m_hh\G
  *************************** 1. row ***************************
         TABLE: TBL2_M_HH
  CREATE TABLE: CREATE TABLE "TBL2_M_HH" (
    "COL1" NUMBER,
    "COL2" NUMBER,
    "COL3" NUMBER
  ) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
   partition by hash(col1) subpartition by hash(col2) subpartition template (
  subpartition P0,
  subpartition P1,
  subpartition P2)
  (partition P0,
  partition P1,
  partition P2,
  partition P3,
  partition P4)
  1 row in set
  

  Insert data into the tbl2_m_hh table.

  obclient> INSERT INTO tbl2_m_hh VALUES (100,100,100);
  Query OK, 1 row affected
  

  View the tbl2_m_hh subpartitioned table.

  obclient> SELECT * FROM tbl2_m_hh partition(P1);
  +------+------+------+
  | COL1 | COL2 | COL3 |
  +------+------+------+
  |  100 |  100 |  100 |
  +------+------+------+
  1 row in set
  

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 NUMBER,col2 NUMBER)
         PARTITION BY RANGE(col1)
         SUBPARTITION BY RANGE(col2)
          (PARTITION p0 VALUES LESS THAN(100)
            (SUBPARTITION sp0 VALUES LESS THAN(2020),
             SUBPARTITION sp1 VALUES LESS THAN(2021)
            ),
           PARTITION p1 VALUES LESS THAN(200)
            (SUBPARTITION sp2 VALUES LESS THAN(2020),
             SUBPARTITION sp3 VALUES LESS THAN(2021),
             SUBPARTITION sp4 VALUES LESS THAN(2022)
             )
           );
  Query OK, 0 rows affected
  

  View the t2_f_rr subpartitioned table.

  obclient>  SHOW CREATE TABLE t2_f_rr\G
  *************************** 1. row ***************************
         TABLE: T2_F_RR
  CREATE TABLE: CREATE TABLE "T2_F_RR" (
    "COL1" NUMBER,
    "COL2" NUMBER
  ) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
   partition by range(col1) subpartition by range(col2)
  (partition P0 values less than (100) (
  subpartition SP0 values less than (2020),
  subpartition SP1 values less than (2021)),
  partition P1 values less than (200) (
  subpartition SP2 values less than (2020),
  subpartition SP3 values less than (2021),
  subpartition SP4 values less than (2022)))
  1 row in set
  

  Insert data into the t2_f_rr table.

  obclient> INSERT INTO t2_f_rr VALUES (100,100);
  Query OK, 1 row affected
  

  View the t2_f_rr subpartitioned table.

  obclient> SELECT * FROM t2_f_rr partition(P1);
  +------+------+
  | COL1 | COL2 |
  +------+------+
  |  100 |  100 |
  +------+------+
  1 row in set
  

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

  obclient> CREATE TABLE t2_f_rl(col1 NUMBER,col2 VARCHAR2(50))
         PARTITION BY RANGE(col1)
         SUBPARTITION BY LIST(col2)
         (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
  

  View the t2_f_rl subpartitioned table.

  obclient> SHOW CREATE TABLE t2_f_rl\G
  *************************** 1. row ***************************
         TABLE: T2_F_RL
  CREATE TABLE: CREATE TABLE "T2_F_RL" (
    "COL1" NUMBER,
    "COL2" VARCHAR2(50)
  ) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
   partition by range(col1) subpartition by list(col2)
  (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')))
  1 row in set
  

  Insert data into the t2_f_rl subpartitioned table.

  obclient> INSERT INTO t2_f_rl VALUES (99,'01'),(99,'02'),(199,'01'),(199,'02');
  Query OK, 2 rows affected
  Records: 2  Duplicates: 0  Warnings: 0
  

  View the t2_f_rl subpartitioned table.

  obclient> SELECT * FROM t2_f_rl;
  +------+------+
  | COL1 | COL2 |
  +------+------+
  |   99 | 01   |
  |   99 | 02   |
  |  199 | 01   |
  |  199 | 02   |
  +------+------+
  4 rows in set
  

• Create a non-template-based RANGE-HASH-subpartitioned table.

  obclient> CREATE TABLE t2_f_rh(col1 NUMBER,col2 VARCHAR2(50))
         PARTITION BY RANGE(col1)
         SUBPARTITION BY HASH(col2)
         (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
  

  View the t2_f_rh subpartitioned table.

  obclient> SHOW CREATE TABLE t2_f_rh\G
  *************************** 1. row ***************************
         TABLE: T2_F_RH
  CREATE TABLE: CREATE TABLE "T2_F_RH" (
    "COL1" NUMBER,
    "COL2" VARCHAR2(50)
  ) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
   partition by range(col1) subpartition by hash(col2)
  (partition P0 values less than (100) (
  subpartition SP0,
  subpartition SP1),
  partition P1 values less than (200) (
  subpartition SP2,
  subpartition SP3,
  subpartition SP4))
  1 row in set
  

  Insert data into the t2_f_rh subpartitioned table.

  obclient> INSERT INTO t2_f_rh VALUES (199,'02'),(299,'02');
  Query OK, 2 rows affected
  Records: 2  Duplicates: 0  Warnings: 0
  

  View the t2_f_rh subpartitioned table.

  obclient> SELECT * FROM t2_f_rh;
  +------+------+
  | COL1 | COL2 |
  +------+------+
  |  199 | 02   |
  |  299 | 02   |
  +------+------+
  2 rows in set
  

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

  obclient> CREATE TABLE t2_f_lr(col1 INT,col2 VARCHAR2(50))
         PARTITION BY LIST(col2)
         SUBPARTITION BY RANGE(col1)
         (PARTITION p0 VALUES('01')
           (SUBPARTITION sp0 VALUES LESS THAN(100),
            SUBPARTITION sp1 VALUES LESS THAN(200)
            ),
          PARTITION p1 VALUES('02')
           (SUBPARTITION sp2 VALUES LESS THAN(100),
            SUBPARTITION sp3 VALUES LESS THAN(200),
            SUBPARTITION sp4 VALUES LESS THAN(300)
            )
          );
  Query OK, 0 rows affected
  

  View the t2_f_lr subpartitioned table.

  obclient> SHOW CREATE TABLE t2_f_lr\G
  *************************** 1. row ***************************
         TABLE: T2_F_LR
  CREATE TABLE: CREATE TABLE "T2_F_LR" (
    "COL1" NUMBER(38),
    "COL2" VARCHAR2(50)
  ) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
   partition by list(col2) subpartition by range(col1)
  (partition P0 values  ('01') (
  subpartition SP0 values less than (100),
  subpartition SP1 values less than (200)),
  partition P1 values  ('02') (
  subpartition SP2 values less than (100),
  subpartition SP3 values less than (200),
  subpartition SP4 values less than (300)))
  1 row in set
  

  Insert data into the t2_f_lr subpartitioned table.

  obclient> INSERT INTO t2_f_lr VALUES (199,'02'),(299,'02');
  Query OK, 1 row affected
  

  View the t2_f_lr subpartitioned table.

  obclient> SELECT * FROM t2_f_lr;
  +------+------+
  | COL1 | COL2 |
  +------+------+
  |  199 | 02   |
  |  299 | 02   |
  +------+------+
  2 rows in set
  

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

  obclient> CREATE TABLE t2_f_ll(col1 NUMBER,col2 varchar2(50))
         PARTITION BY LIST(col1)
         SUBPARTITION BY LIST(col2)
         (PARTITION p0 VALUES ('01', '02')
           (SUBPARTITION sp0 VALUES ('A'),
            SUBPARTITION sp1 VALUES ('B'),
            SUBPARTITION sp2 VALUES ('C')
            ),
          PARTITION p1 VALUES ('03', '04')
           (SUBPARTITION sp3 VALUES ('A'),
            SUBPARTITION sp4 VALUES ('B'),
            SUBPARTITION sp5 VALUES ('C')
            )
          );
  Query OK, 0 rows affected
  

  View the t2_f_ll subpartitioned table.

  obclient> SHOW CREATE TABLE t2_f_ll\G
  *************************** 1. row ***************************
         TABLE: T2_F_LL
  CREATE TABLE: CREATE TABLE "T2_F_LL" (
    "COL1" NUMBER,
    "COL2" VARCHAR2(50)
  ) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
   partition by list(col1) subpartition by list(col2)
  (partition P0 values  (1,2) (
  subpartition SP0 values  ('A'),
  subpartition SP1 values  ('B'),
  subpartition SP2 values  ('C')),
  partition P1 values  (3,4) (
  subpartition SP3 values  ('A'),
  subpartition SP4 values  ('B'),
  subpartition SP5 values  ('C')))
  1 row in set
  

  Insert data into the t2_f_ll subpartitioned table.

  obclient> INSERT INTO t2_f_ll VALUES (1,'A');
  Query OK, 1 row affected
  

  View the t2_f_ll subpartitioned table.

  obclient> SELECT * FROM t2_f_ll;
  +------+------+
  | COL1 | COL2 |
  +------+------+
  |    1 | A    |
  +------+------+
  1 row in set
  

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

  obclient> CREATE TABLE t2_f_lh(col1 NUMBER,col2 VARCHAR2(50))
         PARTITION BY LIST(col1)
         SUBPARTITION BY HASH(col2)
         (PARTITION p0 VALUES('01')
           (SUBPARTITION sp0,
            SUBPARTITION sp1
            ),
          PARTITION p1 VALUES('02')
           (SUBPARTITION sp2,
            SUBPARTITION sp3,
            SUBPARTITION sp4
            )
          );
  Query OK, 0 rows affected
  

  View the t2_f_lh subpartitioned table.

  obclient> SHOW CREATE TABLE t2_f_lh\G
  *************************** 1. row ***************************
         TABLE: T2_F_LH
  CREATE TABLE: CREATE TABLE "T2_F_LH" (
    "COL1" NUMBER,
    "COL2" VARCHAR2(50)
  ) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
   partition by list(col1) subpartition by hash(col2)
  (partition P0 values  (1) (
  subpartition SP0,
  subpartition SP1),
  partition P1 values  (2) (
  subpartition SP2,
  subpartition SP3,
  subpartition SP4))
  1 row in set
  

  Insert data into the t2_f_lh subpartitioned table.

  obclient> INSERT INTO t2_f_lh VALUES (1,'A');
  Query OK, 1 row affected
  

  View the t2_f_lh subpartitioned table.

  obclient> SELECT * FROM t2_f_lh;
  +------+------+
  | COL1 | COL2 |
  +------+------+
  |    1 | A    |
  +------+------+
  1 row in set
  

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

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

  View the tbl2_f_hr subpartitioned table.

  obclient> SHOW CREATE TABLE tbl2_f_hr\G
  *************************** 1. row ***************************
         TABLE: TBL2_F_HR
  CREATE TABLE: CREATE TABLE "TBL2_F_HR" (
    "COL1" NUMBER,
    "COL2" NUMBER,
    "COL3" NUMBER
  ) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
   partition by hash(col1) subpartition by range(col2)
  (partition P0 (
  subpartition SP0 values less than (100),
  subpartition SP1 values less than (200)),
  partition P1 (
  subpartition SP2 values less than (100),
  subpartition SP3 values less than (200)))
  1 row in set
  

  Insert data into the tbl2_f_hr subpartitioned table.

  obclient> INSERT INTO tbl2_f_hr VALUES (1,1,1);
  Query OK, 1 row affected
  

  View the tbl2_f_hr subpartitioned table.

  obclient> SELECT * FROM tbl2_f_hr;
  +------+------+------+
  | COL1 | COL2 | COL3 |
  +------+------+------+
  |    1 |    1 |    1 |
  +------+------+------+
  1 row in set
  

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

  obclient> CREATE TABLE t2_f_hl(col1 NUMBER,col2 NUMBER,col3 NUMBER)
         PARTITION BY HASH(col1)
         SUBPARTITION BY LIST(col2)
          (PARTITION p0
            (SUBPARTITION sp0 VALUES(1,3),
             SUBPARTITION sp1 VALUES(4,7)
            ),
           PARTITION p1
            (SUBPARTITION sp2 VALUES(1,3),
             SUBPARTITION sp3 VALUES(4,7)
             )
          );
  Query OK, 0 rows affected
  

  View the created subpartitioned table t2_f_hl.

  obclient> SHOW CREATE TABLE t2_f_hl\G
  *************************** 1. row ***************************
         TABLE: T2_F_HL
  CREATE TABLE: CREATE TABLE "T2_F_HL" (
    "COL1" NUMBER,
    "COL2" NUMBER,
    "COL3" NUMBER
  ) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
   partition by hash(col1) subpartition by list(col2)
  (partition P0 (
  subpartition SP0 values (1,3),
  subpartition SP1 values (4,7)),
  partition P1 (
  subpartition SP2 values (1,3),
  subpartition SP3 values (4,7)))
  1 row in set
  

  Insert data into the t2_f_hl table.

  obclient> INSERT INTO t2_f_hl VALUES (199,1,1);
  Query OK, 1 row affected
  

  View the t2_f_hl subpartitioned table.

  obclient> SELECT * FROM t2_f_hl;
  +------+------+------+
  | COL1 | COL2 | COL3 |
  +------+------+------+
  |  199 |    1 |    1 |
  +------+------+------+
  1 row in set
  

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

  obclient> CREATE TABLE t2_f_hh(col1 NUMBER,col2 NUMBER,col3 NUMBER)
         PARTITION BY HASH(col1)
         SUBPARTITION BY HASH(col2)
          (PARTITION p0
            (SUBPARTITION sp0,
             SUBPARTITION sp1
             ),
           PARTITION p1
            (SUBPARTITION sp2,
             SUBPARTITION sp3
             )
          );
  Query OK, 0 rows affected
  

  View the t2_f_hh subpartitioned table.

  obclient> SHOW CREATE TABLE t2_f_hh\G
  *************************** 1. row ***************************
         TABLE: T2_F_HH
  CREATE TABLE: CREATE TABLE "T2_F_HH" (
    "COL1" NUMBER,
    "COL2" NUMBER,
    "COL3" NUMBER
  ) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
   partition by hash(col1) subpartition by hash(col2)
  (partition P0 (
  subpartition SP0,
  subpartition SP1),
  partition P1 (
  subpartition SP2,
  subpartition SP3))
  1 row in set
  

  Insert data into the t2_f_hh subpartitioned table.

  obclient> INSERT INTO t2_f_hh VALUES (199,1,1);
  Query OK, 1 row affected
  

  View the t2_f_hh subpartitioned table.

  obclient> SELECT * FROM t2_f_hh;
  +------+------+------+
  | COL1 | COL2 | COL3 |
  +------+------+------+
  |  199 |    1 |    1 |
  +------+------+------+
  1 row in set