Manage a subpartitioned table

After you create a subpartitioned table, you can add, drop, or truncate partitions and subpartitions in the subpartitioned table.

Add a partition

Syntax

The syntax that you can use to add a partition varies based on whether the subpartitioned table is a template-based subpartitioned table.

• Add a partition to a template-based subpartitioned table

  ALTER TABLE table_name ADD PARTITION partition_options;
  
  partition_options:
    {PARTITION partition_name VALUES LESS THAN range_partition_expr
       [,PARTITION partition_name VALUES LESS THAN range_partition_expr]... }
   |{PARTITION partition_name VALUES IN list_partition_expr
       [,PARTITION partition_name VALUES IN list_partition_expr]...}
  

  Note

  For a template-based subpartitioned table, you only need to specify the partition definition when you add a partition. The subpartition definition is automatically filled based on the template.

• Add a partition to a non-template-based subpartitioned table

  ALTER TABLE table_name ADD PARTITION partition_options;
  
  partition_options:
    {PARTITION partition_name VALUES LESS THAN range_partition_expr (subpartition_option)
       [,PARTITION partition_name VALUES LESS THAN range_partition_expr (subpartition_option)]... }
   |{PARTITION partition_name VALUES IN list_partition_expr (subpartition_option)
       [,PARTITION partition_name VALUES IN list_partition_expr (subpartition_option)]...}
  
  subpartition_option:
    {SUBPARTITION subpartition_name VALUES LESS THAN range_partition_expr, ...}
   |{SUBPARTITION subpartition_name VALUES IN list_partition_expr, ....}
   |{SUBPARTITION subpartition_name, ....}
  

  Note

  For a non-template-based subpartitioned table, you must specify both partition and subpartition definitions when you add a partition.

Examples

• Add partitions p3 and p4 to a template-based subpartitioned table t_m_rcrc for which the partitioning and subpartitioning strategies are both set to RANGE COLUMNS.

  obclient> ALTER TABLE t_m_rcrc ADD PARTITION
         (PARTITION p3 VALUES LESS THAN(400),
          PARTITION p4 VALUES LESS THAN(500)
         ); 
  Query OK, 0 rows affected
  

  View the partitions p3 and p4 added to the t_m_rcrc table.

  obclient> show create table t_m_rcrc\G
  *************************** 1. row ***************************
         Table: t_m_rcrc
  Create Table: CREATE TABLE `t_m_rcrc` (
    `col1` int(11) DEFAULT NULL,
    `col2` int(11) DEFAULT NULL
  ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
   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),
  partition p3 values less than (400),
  partition p4 values less than (500))
  1 row in set
  

  Insert data into the p3 and p4 partitions of the t_m_rcrc table.

  obclient> INSERT INTO t_m_rcrc VALUES (399,399),(499,499);
  Query OK, 2 row affected
  

  View the p3 and p4 partitions of the t_m_rcrc table.

  obclient>  SELECT * FROM t_m_rcrc partition(p3,p4);
  +------+------+
  | col1 | col2 |
  +------+------+
  |  399 |  399 |
  |  499 |  499 |
  +------+------+
  2 rows in set
  

• Add the partition p2 to a non-template-based subpartitioned table t2_f_rclc for which the partitioning and subpartitioning strategies are both set to RANGE.

  obclient> ALTER TABLE t2_f_rclc ADD PARTITION
         (PARTITION p2 VALUES LESS THAN(300)
           (SUBPARTITION sp6 VALUES IN(1,3),
            SUBPARTITION sp7 VALUES IN(4,6),
            SUBPARTITION sp8 VALUES IN(7,9))
         ); 
  Query OK, 0 rows affected
  

  View the p6, p7, and p8 partitions added to the t2_f_rclc table.

  obclient> show create table t2_f_rclc\G
  *************************** 1. row ***************************
         Table: t2_f_rclc
  Create Table: CREATE TABLE `t2_f_rclc` (
    `col1` int(11) DEFAULT NULL,
    `col2` int(11) DEFAULT NULL
  ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
   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)),
  partition p2 values less than (300) (
  subpartition sp6 values in (1,3),
  subpartition sp7 values in (4,6),
  subpartition sp8 values in (7,9)))
  1 row in set
  

  Insert data into the p6, p7, and p8 partitions of the t2_f_rclc table.

  obclient> INSERT INTO t2_f_rclc VALUES (299,3),(299,6),(299,9);
  Query OK, 1 row affected
  

  View the p6, p7, and p8 partitions of the t2_f_rclc table.

  obclient> SELECT * FROM t2_f_rclc partition(sp6,sp7,sp8);
  +------+------+
  | col1 | col2 |
  +------+------+
  |  299 |    3 |
  |  299 |    6 |
  |  299 |    9 |
  +------+------+
  3 rows in set
  

Add a subpartition

OceanBase Database in MySQL mode does not allow you to add subpartitions to a table.

Drop a partition

Syntax

ALTER TABLE table_name DROP PARTITION partition_name[, partition_name ...];

Note

• Before dropping a partition from a subpartitioned table, ensure that there are no active transactions or queries in this partition. Otherwise, SQL statement errors or exceptions may occur. Under the SYS tenant, you can query the context status of unfinished transactions through the __all_virtual_trans_stat table.

• If you drop a partition, its definition, data, and subpartitions are also dropped.

Examples

1. Drop partitions p3 and p4 from the template-based subpartitioned table t_m_rcrc for which the partitioning and subpartitioning strategies are both set to RANGE COLUMNS.

   1. View the t_m_rcrc subpartitioned table.

      obclient> show create table t_m_rcrc\G
      *************************** 1. row ***************************
             Table: t_m_rcrc
      Create Table: CREATE TABLE `t_m_rcrc` (
        `col1` int(11) DEFAULT NULL,
        `col2` int(11) DEFAULT NULL
      ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
       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),
      partition p3 values less than (400),
      partition p4 values less than (500))
      1 row in set (0.00 sec)
      

   2. Drop the p3 and p4 partitions from the t_m_rcrc table.

      obclient> ALTER TABLE t_m_rcrc DROP PARTITION p3,p4;
      Query OK, 0 rows affected
      

   3. View the results.

      obclient> show create table t_m_rcrc\G
      *************************** 1. row ***************************
             Table: t_m_rcrc
      Create Table: CREATE TABLE `t_m_rcrc` (
        `col1` int(11) DEFAULT NULL,
        `col2` int(11) DEFAULT NULL
      ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
       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),
      partition p3 values less than (400),
      partition p4 values less than (500))
      1 row in set (0.00 sec)
      

2. Drop the partition p2 from the non-template-based subpartitioned table t2_f_rr for which the partitioning and subpartitioning strategies are both set to RANGE.

   1. View the t_m_rcrc subpartitioned table.

      obclient> show create table t2_f_rr\G
      *************************** 1. row ***************************
             Table: t2_f_rr
      Create Table: CREATE TABLE `t2_f_rr` (
        `col1` int(11) DEFAULT NULL,
        `col2` timestamp NULL DEFAULT NULL
      ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
       partition by range(col1) subpartition by range(UNIX_TIMESTAMP(col2))
      (partition p0 values less than (100) (
      subpartition sp0 values less than (1617206400),
      subpartition sp1 values less than (1625068800),
      subpartition sp2 values less than (1633017600),
      subpartition sp3 values less than (1640966400)),
      partition p1 values less than (200) (
      subpartition sp4 values less than (1617206400),
      subpartition sp5 values less than (1625068800),
      subpartition sp6 values less than (1633017600),
      subpartition sp7 values less than (1643644800)),
      partition p2 values less than (300) (
      subpartition sp8 values less than (1617206400),
      subpartition sp9 values less than (1625068800),
      subpartition sp10 values less than (1633017600),
      subpartition sp11 values less than (1643644800)))
      1 row in set (0.00 sec)
      

   2. Drop the p2 partition from the t2_f_rr table.

      obclient> ALTER TABLE t2_f_rr DROP PARTITION P2;
      Query OK, 0 rows affected
      

   3. View the results.

      obclient> show create table t2_f_rr\G
      *************************** 1. row ***************************
             Table: t2_f_rr
      Create Table: CREATE TABLE `t2_f_rr` (
        `col1` int(11) DEFAULT NULL,
        `col2` timestamp NULL DEFAULT NULL
      ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
       partition by range(col1) subpartition by range(UNIX_TIMESTAMP(col2))
      (partition p0 values less than (100) (
      subpartition sp0 values less than (1617206400),
      subpartition sp1 values less than (1625068800),
      subpartition sp2 values less than (1633017600),
      subpartition sp3 values less than (1640966400)),
      partition p1 values less than (200) (
      subpartition sp4 values less than (1617206400),
      subpartition sp5 values less than (1625068800),
      subpartition sp6 values less than (1633017600),
      subpartition sp7 values less than (1643644800)))
      1 row in set (0.00 sec)
      

Drop a subpartition

Syntax

ALTER TABLE table_name DROP SUBPARTITION subpartition_name[, subpartition_name ...];

Note

• Before dropping a subpartition from a subpartitioned table, ensure that there are no active transactions or queries in this subpartition. Otherwise, SQL statement errors or exceptions may occur. Under the SYS tenant, you can query the context status of unfinished transactions through the __all_virtual_trans_stat table.

• If you drop a subpartition, its definition and data are also dropped.

• If you want to drop multiple subpartitions at a time, they must belong to the same partition.

Examples

Drop subpartitions sp6 and sp7 from the non-template-based subpartitioned table t2_f_rr for which the partitioning and subpartitioning strategies are both set to RANGE.

1. 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` int(11) DEFAULT NULL,
     `col2` timestamp NULL DEFAULT NULL
   ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
    partition by range(col1) subpartition by range(UNIX_TIMESTAMP(col2))
   (partition p0 values less than (100) (
   subpartition sp0 values less than (1617206400),
   subpartition sp1 values less than (1625068800),
   subpartition sp2 values less than (1633017600),
   subpartition sp3 values less than (1640966400)),
   partition p1 values less than (200) (
   subpartition sp4 values less than (1617206400),
   subpartition sp5 values less than (1625068800),
   subpartition sp6 values less than (1633017600),
   subpartition sp7 values less than (1643644800)))
   1 row in set (0.00 sec)
   

2. Drop the sp6 and sp7 subpartitions from the t2_f_rr table.

   obclient> ALTER TABLE t2_f_rr DROP SUBPARTITION sp6,sp7;
   Query OK, 0 rows affected
   

3. View the results.

obclient> show create table t2_f_rr\G
*************************** 1. row ***************************
       Table: t2_f_rr
Create Table: CREATE TABLE `t2_f_rr` (
  `col1` int(11) DEFAULT NULL,
  `col2` timestamp NULL DEFAULT NULL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
 partition by range(col1) subpartition by range(UNIX_TIMESTAMP(col2))
(partition p0 values less than (100) (
subpartition sp0 values less than (39283200),
subpartition sp1 values less than (47145600),
subpartition sp2 values less than (55094400),
subpartition sp3 values less than (63043200)),
partition p1 values less than (200) (
subpartition sp4 values less than (39283200),
subpartition sp5 values less than (47145600)))
1 row in set

Truncate a partition

Syntax

ALTER TABLE table_name TRUNCATE PARTITION partition_name[, partition_name ...];

Note

• Before truncating a partition of a subpartitioned table, ensure that there are no active transactions or queries in this partition. Otherwise, SQL statement errors or exceptions may occur. Under the SYS tenant, you can query the context status of unfinished transactions through the __all_virtual_trans_stat table.

• OceanBase Database allows you to truncate one or more partitions of a subpartitioned table that uses a combination of the following partitioning strategies to clear all data in the subpartitions under the partitions: RANGE, RANGE COLUMNS, LIST, and LIST COLUMNS.

Examples

Clear the data in the p0 partition of the subpartitioned table t2_f_rclc for which the partitioning and subpartitioning strategies are set to RANGE COLUMNS and LIST COLUMNS respectively.

1. Query the data in the p0 partition of the t2_f_rclc table.

   obclient> select * from t2_f_rclc partition(p0);
   +------+------+
   | col1 | col2 |
   +------+------+
   |    1 |    6 |
   +------+------+
   1 row in set (0.01 sec)
   

2. Delete the data from the p0 partition of the t2_f_rclc table.

   obclient> ALTER TABLE t2_f_rclc TRUNCATE PARTITION p0;
   Query OK, 0 rows affected
   

3. View the results.

   obclient> select * from t2_f_rclc partition(p0);
   Empty set (0.01 sec)
   

Truncate a subpartition

Syntax

ALTER TABLE table_name TRUNCATE SUBPARTITION subpartition_name[, subpartition_name ...];

Note

• Before truncating a subpartition of a subpartitioned table, ensure that there are no active transactions or queries in this subpartition. Otherwise, SQL statement errors or exceptions may occur. Under the SYS tenant, you can query the context status of unfinished transactions through the __all_virtual_trans_stat table.

• OceanBase Database allows you to truncate one or more subpartitions of a subpartitioned table that uses a combination of the following partitioning strategies to clear all data in the subpartitions: RANGE, RANGE COLUMNS, LIST, and LIST COLUMNS.

• If you want to truncate multiple subpartitions at a time, they must belong to the same partition.

Examples

Clear the data in subpartitions sp1 and sp2 of the subpartitioned table t2_f_rr for which the partitioning and subpartitioning strategies are both set to RANGE.

1. Query the data in the sp1 and sp2 subpartitions of the t2_f_rr table.

   obclient> select * from t2_f_rr partition(sp1,sp2);
   +------+---------------------+
   | col1 | col2                |
   +------+---------------------+
   |   10 | 2021-07-01 00:00:00 |
   |   11 | 2021-09-30 00:00:00 |
   +------+---------------------+
   2 rows in set (0.00 sec)
   

2. Clear the data in the sp1 and sp2 subpartitions of the t2_f_rr table.

   obclient> ALTER TABLE t2_f_rr TRUNCATE SUBPARTITION sp1,sp2;
   Query OK, 0 rows affected
   

3. View the results.

   obclient> select * from t2_f_rr partition(sp1,sp2);
   Empty set (0.01 sec)