Create a partitioned table

This topic describes the method of creating a partitioned table and other related items.

Partitioning types

OceanBase Database in MySQL mode supports the following partitioning types:

• RANGE and RANGE COLUMNS

• LIST and LIST COLUMNS

• HASH and KEY

Create a RANGE- or RANGE COLUMNS-partitioned table

Syntax

CREATE TABLE table_name (column_name column_type[, column_name column_type])
  PARTITION BY { RANGE (expr(column_name) | column_name)
                | RANGE COLUMNS(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)]
     );

Note

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

• In MySQL mode, the result of the expr expression in the PARTITION BY RANGE ( expr ) clause must be an integer. To use temporal columns for RANGE partitioning, ensure that values in the columns are of the TIMESTAMP data type and use the UNIX_TIMESTAMP function to convert a temporal value to a numerical value. You can also use RANGE COLUMNS partitioning for the same result. In this case, the result of the expression for the partition key does not have to be an integer.

• 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.

• 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

Examples

• Create a RANGE-partitioned table tbl1_log_r.

  obclient> CREATE TABLE tbl1_r (log_id BIGINT NOT NULL,log_value VARCHAR(50),log_date TIMESTAMP NOT NULL)
         PARTITION BY RANGE(UNIX_TIMESTAMP(log_date))
          (PARTITION M202001 VALUES LESS THAN(UNIX_TIMESTAMP('2020/02/01'))
         , PARTITION M202002 VALUES LESS THAN(UNIX_TIMESTAMP('2020/03/01'))
         , PARTITION M202003 VALUES LESS THAN(UNIX_TIMESTAMP('2020/04/01'))
         , PARTITION M202004 VALUES LESS THAN(UNIX_TIMESTAMP('2020/05/01'))
         , PARTITION M202005 VALUES LESS THAN(UNIX_TIMESTAMP('2020/06/01'))
         , PARTITION M202006 VALUES LESS THAN(UNIX_TIMESTAMP('2020/07/01'))
         , PARTITION M202007 VALUES LESS THAN(UNIX_TIMESTAMP('2020/08/01'))
         , PARTITION M202008 VALUES LESS THAN(UNIX_TIMESTAMP('2020/09/01'))
         , PARTITION M202009 VALUES LESS THAN(UNIX_TIMESTAMP('2020/10/01'))
         , PARTITION M202010 VALUES LESS THAN(UNIX_TIMESTAMP('2020/11/01'))
         , PARTITION M202011 VALUES LESS THAN(UNIX_TIMESTAMP('2020/12/01'))
         , PARTITION M202012 VALUES LESS THAN(UNIX_TIMESTAMP('2021/01/01'))
          );
  Query OK, 0 rows affected
  

• Create a RANGE COLUMNS-partitioned table tbl1_log_rc.

  obclient> CREATE TABLE tbl1_log_rc (log_id BIGINT NOT NULL,log_value VARCHAR(50),log_date DATE NOT NULL)
         PARTITION BY RANGE COLUMNS(log_date)
          (PARTITION M202001 VALUES LESS THAN('2020/02/01')
         , PARTITION M202002 VALUES LESS THAN('2020/03/01')
         , PARTITION M202003 VALUES LESS THAN('2020/04/01')
         , PARTITION M202004 VALUES LESS THAN('2020/05/01')
         , PARTITION M202005 VALUES LESS THAN('2020/06/01')
         , PARTITION M202006 VALUES LESS THAN('2020/07/01')
         , PARTITION M202007 VALUES LESS THAN('2020/08/01')
         , PARTITION M202008 VALUES LESS THAN('2020/09/01')
         , PARTITION M202009 VALUES LESS THAN('2020/10/01')
         , PARTITION M202010 VALUES LESS THAN('2020/11/01')
         , PARTITION M202011 VALUES LESS THAN('2020/12/01')
         , PARTITION M202012 VALUES LESS THAN('2021/01/01')
         , PARTITION MMAX VALUES LESS THAN MAXVALUE
          );
  Query OK, 0 rows affected
  

Create a LIST- or LIST COLUMNS-partitioned table

Syntax

CREATE TABLE table_name (column_name column_type[,column_name column_type])
  PARTITION BY { LIST ( expr(column_name) | column_name )
                | LIST COLUMNS ( column_name [,column_name])
                }
    (PARTITION partition_name VALUES IN ( v01 [, v0N])
     [,PARTITION partition_name VALUES IN ( vN1 [, vNN])]
     [,PARTITION partition_name VALUES IN (DEFAULT)]
    );

Notice

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

• The result of the partitioning expression must be an integer.

• The partitioning expression can reference only one column, instead of a list of multiple columns (column vectors).

Parameters

Examples

• Create a LIST-partitioned table named tbl1_l.

  obclient> CREATE TABLE tbl1_l (col1 BIGINT PRIMARY KEY,col2 VARCHAR(50))
         PARTITION BY LIST(col1)
          (PARTITION p0 VALUES IN (1, 2, 3),
           PARTITION p1 VALUES IN (5, 6),
           PARTITION p2 VALUES IN (DEFAULT)
          );
  Query OK, 0 rows affected
  

• Create a LIST COLUMNS-partitioned table.

  obclient> CREATE TABLE tbl1_lc (id INT,partition_id VARCHAR(2))
         PARTITION BY LIST COLUMNS(partition_id)
          (PARTITION p0 VALUES IN ('00','01'),
           PARTITION p1 VALUES IN ('02','03'),
           PARTITION p2 VALUES IN (DEFAULT)
          );
  Query OK, 0 rows affected
  

Create a HASH- or KEY-partitioned table

Syntax

CREATE TABLE table_name (column_name column_type[,column_name column_type])
  PARTITION BY { HASH(expr) | KEY([column_name_list]) }
    PARTITIONS partition_count;

Parameters

Note

For a HASH- or KEY-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.

Examples

• 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 KEY-partitioned table named tbl1_k.

  obclient> CREATE TABLE tbl1_k(id INT,gmt_create DATETIME,info VARCHAR(20))
         PARTITION BY KEY(id,gmt_create) PARTITIONS 10;
  Query OK, 0 rows affected
  

What to do next

After creating a partition, you can add, drop, or truncate the partition. For information about partition management, see Manage a partitioned table.