Partitioned tables

Create a RANGE-partitioned table

Basic syntax for RANGE partitioning:

CREATE TABLE table_name (
    column_name1        column_type
    [, column_nameN     column_type]
) PARTITION BY RANGE ( expr(column_name1)  | column_name1)
(
    PARTITION   p0      VALUES LESS THAN ( expr )
    [, PARTITION pN     VALUES LESS THAN (expr ) ]
 [, PARTITION pX    VALUES LESS THAN (MAXVALUE) ]
);

When you create RANGE partitions, you must follow the following rules:

• In the PARTITION BY RANGE ( expr )clause, the result of the expr expression must be of the integer type.

• 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 literal are added to the next higher partition.

• All partitions, except the first, have an implicit lower bound, which is the upper bound of the previous partition.

• Only the last partition can have an upper bound of MAXVALUE, which is an indefinite value that is greater than any other partitioning key value, including the NULL value.

Example

• The following statement creates a RANGE-partitioned table in MySQL mode:

  obclient>CREATE TABLE t_log_part_by_range (
      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'))
  );
  

• The following statement creates a RANGE-partitioned table in Oracle mode:

  obclient> CREATE TABLE t_log_part_by_range (
      log_id      number NOT NULL 
      , log_value varchar2(50)
      , 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)
  );
  

You can add or delete a RANGE partition. If MAXVALUE is specified for the last RANGE partition, then you cannot add a new partition.

In MySQL mode, the result of an expression for a RANGE partitioning key must be of the integer type. To use a date or time for partitioning, you must use the TIMESTAMP data type and use the UNIX_TIMESTAMP function to convert it to a number. You can also use RANGE COLUMNS partitioning for the same result. In this case, the result of the expression for the RANGE partitioning key does not have to be of the integer type.

Create a RANGE COLUMNS-partitioned table

OceanBase Database only supports RANGE COLUMNS partitioning in MySQL mode.

Basic syntax for RANGE COLUMNS partitioning:

CREATE TABLE table_name (
    column_name1        column_type
    [, column_nameN     column_type]
) PARTITION BY RANGE ( column_name1 [, column_name2] )
(
    PARTITION   p0      VALUES LESS THAN ( expr )
    [, PARTITION pN     VALUES LESS THAN (expr ) ]
[, PARTITION pX VALUES LESS THAN (maxvalue) ]
);

Example: Creating a RANGE COLUMNS-partitioned table

obclient>CREATE TABLE t_log_part_by_range_columns (
    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
);

Create a LIST-partitioned table

Basic syntax for LIST partitioning:

CREATE TABLE table_name (
    column_name1        column_type
    [, column_nameN     column_type]
) PARTITION BY LIST ( expr(column_name1)  | column_name1)
(
    PARTITION   p0      VALUES IN ( v01 [, v0N] )
    [, PARTITION pN     VALUES IN ( vN1 [, vNN] ) ]
 [, PARTITION pX    VALUES IN (default) ]
);

When you create LIST partitions, comply with the following rules:

• The result of the partition expression must be of the integer type.

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

Examples

• Create a LIST-partitioned table in MySQL mode:

  obclient>CREATE TABLE t_part_by_list ( 
  c1 BIGINT PRIMARY KEY
  , c2 VARCHAR(50)
  ) PARTITION BY list(c1) 
  ( 
  PARTITION p0    VALUES IN (1, 2, 3)
  , PARTITION p1  VALUES IN (5, 6)
  , PARTITION p2  VALUES IN (DEFAULT)
  );
  

• Create a LIST-partitioned table in Oracle mode:

  obclient> CREATE TABLE t_log_part_by_list (
      log_id      number NOT NULL 
      , log_value varchar2(50)
      , log_date  date NOT NULL DEFAULT sysdate
      , PRIMARY key(log_id, log_value)
  ) PARTITION BY list(log_value) 
  (
      PARTITION P01 VALUES ( '01' )
      , PARTITION P02 VALUES ( '02' )
      , PARTITION P03 VALUES ( '03' )
      , PARTITION P04 VALUES ( '04' )
      , PARTITION P05 VALUES ( '05' )
  );
  Query OK, 0 rows affected (0.10 sec)
  
  obclient> INSERT INTO t_log_part_by_list(log_id, log_value) values(1,'01'),(2,'02'),(3,'03'),(4,'04'),(5,'05'),(6,'01');
  Query OK, 6 rows affected (0.01 sec)
  Records: 6  Duplicates: 0  Warnings: 0
  

Create a LIST COLUMNS-partitioned table

OceanBase Database only supports LIST COLUMNS partitioning in MySQL mode.

Basic syntax for LIST COLUMNS partitioning:

CREATE TABLE table_name (
    column_name1        column_type
    [, column_nameN     column_type]
) PARTITION BY LIST COLUMNS ( column_name1 [, column_nameN ] )
(
    PARTITION   p0      VALUES IN ( v01 [, v0N] )
    [, PARTITION pN     VALUES IN ( vN1 [, vNN] ) ]
 [, PARTITION pX    VALUES IN (default) ]
);

Example: Creating a LIST COLUMNS-partitioned table

obclient>CREATE TABLE t2 (
id varchar(64),
type varchar(16),
info varchar(512),
gmt_create datetime(6),
gmt_modified datetime(6),
partition_id varchar(2) GENERATED ALWAYS AS (substr(`id`,19,20)) VIRTUAL,
PRIMARY KEY (id)
) partition by list columns(partition_id)
(partition p0 values in ('00','01'),
partition p1 values in ('02','03'),
partition p2 values in (default));

Create a HASH-partitioned table

Example: Creating a HASH-partitioned table in MySQL or Oracle mode:

obclient>CREATE TABLE ware(
w_id int
, w_ytd number(12,2)
, w_tax number(4,4)
, w_name varchar(10)
, w_street_1 varchar(20)
, w_street_2 varchar(20)
, w_city varchar(20)
, w_state char(2)
, w_zip char(9)
, primary key(w_id)
) PARTITION by hash(w_id) partitions 60;

Create a KEY-partitioned table

OceanBase Database only supports KEY partitioning in MySQL mode.

Example: Creating the t_log_part_by_key table and use the id and gmt_create columns as partitioning keys to split the table into three partitions by using the KEY partitioning method

obclient>CREATE TABLE t_log_part_by_key(
id INT, 
gmt_create DATETIME, 
info VARCHAR(20))
PARTITION BY KEY(id, gmt_create)
PARTITIONS 3;