Create a partitioned table|V4.3.3|OceanBase Database|docs|OceanBase Documentations

You can create a partitioned or subpartitioned table as needed.

In OceanBase Database, tables are divided into partitioned tables and non-partitioned tables. For a partitioned table, a table is split into multiple smaller parts that are easier to manage.

Partitioning types

The Oracle mode of OceanBase Database supports RANGE, LIST, and HASH partitioning.

In the Oracle mode of OceanBase Database, a combination of any two partitioning types is supported as the subpartitioning type. The following table describes the supported subpartitioning types.

Subpartitioning type	Create a subpartitioned table by using a template	Create a subpartitioned table without using a template
RANGE-RANGE	Supported	Supported
RANGE-LIST	Supported	Supported
RANGE-HASH	Supported	Supported
LIST-RANGE	Supported	Supported
LIST-LIST	Supported	Supported
LIST-HASH	Supported	Supported
HASH-RANGE	Supported	Supported
HASH-LIST	Supported	Supported
HASH-HASH	Supported	Supported

Create a RANGE-partitioned table

Syntax

CREATE TABLE table_name (column_name column_type[, column_name column_type])
  PARTITION BY RANGE (column_name[, column_name])
    (PARTITION partition_name VALUES LESS THAN(expr)
     [, PARTITION partition_name VALUES LESS THAN (expr )...]
     [, PARTITION partition_name VALUES LESS THAN (MAXVALUE)]
     );

When you create a RANGE-partitioned table, comply with the following rules:

A VALUES LESS THAN clause must be specified for each partition. This clause specifies a non-inclusive upper bound for the partition. Values of the partitioning key equal to or higher than this upper bound are added to the next higher partition.
You cannot use an expression as the partitioning key for RANGE partitioning.
All partitions, except the first one, have an implicit lower bound, which is the upper bound of the previous partition.
A MAXVALUE literal can be defined only for the last partition. MAXVALUE represents a virtual infinite value that is always greater than other possible values for the partitioning key, including the NULL value. If MAXVALUE is specified for the last RANGE partition, you cannot add a new partition.

Parameters

Parameter	Description
table_name	The table name.
column_name	The column name.
column_type	The data type of the column.
partition_name	The partition name.

Example

Create a RANGE-partitioned table named tbl1_log_r.

obclient> CREATE TABLE tbl1_log_r(log_id INT,log_date DATE NOT NULL DEFAULT SYSDATE)
       PARTITION BY RANGE(log_date)
        (PARTITION M202001 VALUES LESS THAN(TO_DATE('2020/02/01','YYYY/MM/DD'))
         , PARTITION M202002 VALUES LESS THAN(TO_DATE('2020/03/01','YYYY/MM/DD'))
         , PARTITION M202003 VALUES LESS THAN(TO_DATE('2020/04/01','YYYY/MM/DD'))
         , PARTITION M202004 VALUES LESS THAN(TO_DATE('2020/05/01','YYYY/MM/DD'))
         , PARTITION M202005 VALUES LESS THAN(TO_DATE('2020/06/01','YYYY/MM/DD'))
         , PARTITION M202006 VALUES LESS THAN(TO_DATE('2020/07/01','YYYY/MM/DD'))
         , PARTITION M202007 VALUES LESS THAN(TO_DATE('2020/08/01','YYYY/MM/DD'))
         , PARTITION M202008 VALUES LESS THAN(TO_DATE('2020/09/01','YYYY/MM/DD'))
         , PARTITION M202009 VALUES LESS THAN(TO_DATE('2020/10/01','YYYY/MM/DD'))
         , PARTITION M202010 VALUES LESS THAN(TO_DATE('2020/11/01','YYYY/MM/DD'))
         , PARTITION M202011 VALUES LESS THAN(TO_DATE('2020/12/01','YYYY/MM/DD'))
         , PARTITION M202012 VALUES LESS THAN(TO_DATE('2021/01/01','YYYY/MM/DD'))
         , PARTITION MMAX VALUES LESS THAN (MAXVALUE)
         );
Query OK, 0 rows affected

Create a LIST-partitioned table

Syntax

CREATE TABLE table_name (column_name column_type[,column_name column_type])
  PARTITION BY LIST (column_name[, column_name])
    (PARTITION partition_name VALUES ( v01 [, v0N])
     [,PARTITION partition_name VALUES ( vN1 [, vNN])]
     [,PARTITION partition_name VALUES (DEFAULT)]
    );

Note

The partition key of a List partition cannot be an expression.
If DEFAULT is specified for the last LIST partition, you cannot add more partitions.

Parameters

Parameter	Description
table_name	The table name.
column_name	The column name.
column_type	The data type of the column.
partition_name	The partition name.
DEFAULT	Only the last partition can have this parameter, which is an indefinite value that is greater than the upper bound of any other partitions, including NULLs.

Example

Create a LIST-partitioned table named tbl1_log_l.

obclient> CREATE TABLE tbl1_log_l(log_id INT,log_value VARCHAR2(20))
       PARTITION BY LIST(log_value)
        (PARTITION P01 VALUES ('A'),
         PARTITION P02 VALUES ( 'B' ),
         PARTITION P03 VALUES ( 'C' )
        );
Query OK, 0 rows affected

Create a HASH-partitioned table

Syntax

CREATE TABLE table_name (column_name column_type[,column_name column_type])
PARTITION BY HASH(column_name[, column_name]) PARTITIONS partition_count;

Parameters

Parameter	Description
table_name	The table name.
column_name	The column name.
column_type	The data type of the column.
partition_count	The number of partitions.

Note

For a HASH-partitioned table, if no partition names are specified when the table is created, the system generates the names based on the following naming conventions: p0, p1, ..., and pn.

Example

Create a HASH-partitioned table named tbl1_h.

obclient> CREATE TABLE tbl1_h(col1 INT,col2 VARCHAR(50))
       PARTITION BY HASH(col1) PARTITIONS 60;
Query OK, 0 rows affected

Create a subpartitioned table by using a template

The SQL syntax for creating a subpartitioned table by using a template in the Oracle mode of OceanBase Database is as follows:

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_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 of each partition. In this case, subpartitions of 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 example, in the t_range_range table created by using the following statement, the p0 partition 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

obclient> SELECT table_name,partition_name,subpartition_name FROM USER_TAB_SUBPARTITIONS;
+---------------+----------------+-------------------+
| TABLE_NAME    | PARTITION_NAME | SUBPARTITION_NAME |
+---------------+----------------+-------------------+
| T_RANGE_RANGE | P0             | P0SMP1            |
| T_RANGE_RANGE | P0             | P0SMP2            |
| T_RANGE_RANGE | P0             | P0SMP3            |
| T_RANGE_RANGE | P1             | P1SMP1            |
| T_RANGE_RANGE | P1             | P1SMP2            |
| T_RANGE_RANGE | P1             | P1SMP3            |
| T_RANGE_RANGE | P2             | P2SMP1            |
| T_RANGE_RANGE | P2             | P2SMP2            |
| T_RANGE_RANGE | P2             | P2SMP3            |
+---------------+----------------+-------------------+
9 rows in set

The following table describes the parameters.

Parameter	Description
table_name	The table name.
column_name	The column name.
column_type	The data type of the column.
partition_name	The partition name.
subpartition_name	The subpartition name.
INT_VALUE	The number of HASH subpartitions.

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

Here is an example:

obclient> CREATE TABLE t2_m_rr(col1 INT,col2 INT)
       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

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

Here is an example:

obclient> CREATE TABLE t2_m_rl(col1 INT,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

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

Here is an example:

obclient> CREATE TABLE t2_m_rh(col1 INT,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

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

Here is an example:

obclient> CREATE TABLE t2_m_lr(col1 INT,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

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

Here is an example:

obclient> CREATE TABLE t2_m_ll(col1 INT,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

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

Here is an example:

obclient> CREATE TABLE t2_m_lh(col1 INT,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

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

Here is an example:

obclient> CREATE TABLE tbl2_m_hr(col1 INT,col2 INT,col3 INT)
       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

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

Here is an example:

obclient> CREATE TABLE tbl2_m_hl(col1 INT,col2 INT,col3 INT)
       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

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

Here is an example:

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

Create a subpartitioned table without using a template

The SQL syntax for creating a subpartitioned table without using a template in the Oracle mode of OceanBase Database is as follows:

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.

The following table describes the parameters.

Parameter	Description
table_name	The table name.
column_name	The column name.
column_type	The data type of the column.
partition_name	The partition name.
subpartition_name	The subpartition name.

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

Here is an example:

obclient> CREATE TABLE t2_f_rr(col1 INT,col2 INT)
       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

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

Here is an example:

obclient> CREATE TABLE t2_f_rl(col1 INT,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

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

Here is an example:

obclient> CREATE TABLE t2_f_rh(col1 INT,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

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

Here is an example:

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

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

Here is an example:

obclient> CREATE TABLE t2_f_ll(col1 INT,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

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

Here is an example:

obclient> CREATE TABLE t2_f_lh(col1 INT,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

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

Here is an example:

obclient> CREATE TABLE tbl2_f_hr(col1 INT,col2 INT,col3 INT)
       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

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

Here is an example:

obclient> CREATE TABLE t2_f_hl(col1 INT,col2 INT,col3 INT)
       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

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

Here is an example:

obclient> CREATE TABLE t2_f_hh(col1 INT,col2 INT,col3 INT)
       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