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

You can add a partition to a template-based or non-template-based subpartitioned table.

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

  ALTER TABLE table_name ADD PARTITION partition_options;
  
  partition_option:
    range_partition_option | list_partition_option
  
  range_partition_option:
    partition_name VALUES LESS THAN partition_expr
  
  list_partition_option:
    partition_name VALUES 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(subpartition_option);
  
  partition_option:
    range_partition_option | list_partition_option
  
  range_partition_option:
    partition_name VALUES LESS THAN partition_expr
  
  list_partition_option:
    partition_name VALUES partition_expr
  
  tg_subpartition_option:
    {SUBPARTITION subpartition_name VALUES LESS THAN range_partition_expr, ...}
   |{SUBPARTITION subpartition_name VALUES 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 the partition p2 to the template-based RANGE-RANGE-subpartitioned table t2_m_rr.

  obclient> ALTER TABLE t2_m_rr ADD PARTITION p2 VALUES LESS THAN(300);
  Query OK, 0 rows affected
  

  View the p2 partition added to 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),
  partition P2 values less than (300))
  1 row in set
  

  Insert data into the p2 partition of the t2_m_rr table.

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

  View the p2 partition of the t2_m_rr subpartitioned table.

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

• Add the partition p2 to the non-template-based RANGE-RANGE-subpartitioned table t2_f_rr.

  obclient> ALTER TABLE t2_f_rr ADD PARTITION p2 VALUES LESS THAN(300)
         (SUBPARTITION sp5 VALUES LESS THAN(2020),
          SUBPARTITION sp6 VALUES LESS THAN(2021),
          SUBPARTITION sp7 VALUES LESS THAN(2022)
          );
  Query OK, 0 rows affected
  

  View the p2 partition added to 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)),
  partition P2 values less than (300) (
  subpartition SP5 values less than (2020),
  subpartition SP6 values less than (2021),
  subpartition SP7 values less than (2022)))
  1 row in set
  

  Insert data into the p2 partition of the t2_f_rr table.

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

  View the p2 partition of the t2_f_rr subpartitioned table.

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

Add a subpartition

Syntax

ALTER TABLE table_name MODIFY PARTITION partition_name
  ADD SUBPARTITION subpartition_option;

subpartition_option:
  {SUBPARTITION subpartition_name VALUES LESS THAN range_partition_expr, ...}
 |{SUBPARTITION subpartition_name VALUES list_partition_expr, ...}

Parameters

Examples

Add the subpartitions sp8 and sp9 to the partition p1 of the non-template-based RANGE-RANGE-subpartitioned table t2_f_rr.

obclient> ALTER TABLE t2_f_rr MODIFY PARTITION p1 ADD
       SUBPARTITION sp8 VALUES LESS THAN(2023),
       SUBPARTITION sp9 VALUES LESS THAN(2024);
Query OK, 0 rows affected

View the sp8 and sp9 subpartitions added to 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),
subpartition SP8 values less than (2023),
subpartition SP9 values less than (2024)),
partition P2 values less than (300) (
subpartition SP5 values less than (2020),
subpartition SP6 values less than (2021),
subpartition SP7 values less than (2022)))
1 row in set

Insert data into the sp8 and sp9 subpartitions of the t2_f_rr table.

obclient> INSERT INTO t2_f_rr VALUES (188,2023),(188,2022);
Query OK, 2 rows affected
Records: 2  Duplicates: 0  Warnings: 0

View the sp8 and sp9 subpartitions of the created t2_f_rr subpartitioned table.

obclient> SELECT * FROM t2_f_rr partition(sp8,sp9);
+------+------+
| COL1 | COL2 |
+------+------+
|  188 | 2022 |
|  188 | 2023 |
+------+------+
2 rows in set

Drop a partition

Syntax

ALTER TABLE table_name DROP PARTITION partition_name_list [ UPDATE GLOBAL INDEXES ];

partition_name_list:
  partition_name[, partition_name ...]

Note

• Before you drop a partition from a subpartitioned table, ensure that no active transaction or query exists in this partition. Otherwise, SQL statement errors or exceptions may occur. In 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.
• When you drop a partition from a subpartitioned table with global indexes in Oracle mode, you need to add the UPDATE GLOBAL INDEXES keyword to the ALTER TABLE statement to update the global indexes. If the UPDATE GLOBAL INDEXES keyword is not added, the global indexes of this subpartitioned table become unavailable after the partition is dropped.

Examples

1. View the information about 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),
   subpartition SP8 values less than (2023),
   subpartition SP9 values less than (2024)),
   partition P2 values less than (300) (
   subpartition SP5 values less than (2020),
   subpartition SP6 values less than (2021),
   subpartition SP7 values less than (2022)))
   1 row in set
   

2. Drop the partition p0 from the subpartitioned table t2_f_rr.

   obclient> ALTER TABLE t2_f_rr DROP PARTITION p0;
   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" 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 P1 values less than (200) (
   subpartition SP2 values less than (2020),
   subpartition SP3 values less than (2021),
   subpartition SP4 values less than (2022),
   subpartition SP8 values less than (2023),
   subpartition SP9 values less than (2024)),
   partition P2 values less than (300) (
   subpartition SP5 values less than (2020),
   subpartition SP6 values less than (2021),
   subpartition SP7 values less than (2022)))
   1 row in set
   

Drop a subpartition

Syntax

ALTER TABLE table_name DROP SUBPARTITION subpartition_name_list [ UPDATE GLOBAL INDEXES ];

subpartition_name_list:
  subpartition_name[, subpartition_name ...]

Note

• Before you drop a subpartition from a subpartitioned table, ensure that no active transaction or query exists in this subpartition. Otherwise, SQL statement errors or exceptions may occur. In 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.
• When you drop a subpartition from a subpartitioned table with global indexes in Oracle mode, you need to add the UPDATE GLOBAL INDEXES keyword to the ALTER TABLE statement to update the global indexes. If the UPDATE GLOBAL INDEXES keyword is not added, the global indexes of this subpartitioned table become unavailable after the subpartition is dropped.

Examples

Drop the subpartitions sp8 and sp9 from the partition p1 of the subpartitioned table t2_f_rr, and update the global indexes.

1. View the t2_f_rr 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 P1 values less than (200) (
   subpartition SP2 values less than (2020),
   subpartition SP3 values less than (2021),
   subpartition SP4 values less than (2022),
   subpartition SP8 values less than (2023),
   subpartition SP9 values less than (2024)),
   partition P2 values less than (300) (
   subpartition SP5 values less than (2020),
   subpartition SP6 values less than (2021),
   subpartition SP7 values less than (2022)))
   1 row in set
   

2. Drop the subpartitions sp8 and sp9 from the partition p1 of the subpartitioned table t2_f_rr, and update the global indexes.

   obclient> ALTER TABLE t2_f_rr DROP SUBPARTITION sp8,sp9 UPDATE GLOBAL INDEXES;
   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" 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 P1 values less than (200) (
   subpartition SP2 values less than (2020),
   subpartition SP3 values less than (2021),
   subpartition SP4 values less than (2022)),
   partition P2 values less than (300) (
   subpartition SP5 values less than (2020),
   subpartition SP6 values less than (2021),
   subpartition SP7 values less than (2022)))
   1 row in set
   

Truncate a partition

Syntax

ALTER TABLE table_name TRUNCATE PARTITION partition_name_list[ UPDATE GLOBAL INDEXES ];

partition_name_list:
  partition_name[, partition_name ...]

Note

• OceanBase Database allows you to truncate one or more partitions in a subpartitioned table of the RANGE or LIST partitioning method to clear all data in the subpartitions corresponding to one or more partitions. Supported subpartitioning methods of RANGE or LIST are RANGE-RANGE, RANGE-LIST, LIST-RANGE, and LIST-LIST.
• Before you truncate a partition from a subpartitioned table, ensure that no active transaction or query exists in this partition. Otherwise, SQL statement errors or exceptions may occur. In the sys tenant, you can query the context status of unfinished transactions through the __all_virtual_trans_stat table.
• When you truncate a partition from a subpartitioned table with global indexes in Oracle mode, you need to add the UPDATE GLOBAL INDEXES keyword to the ALTER TABLE statement to update the global indexes. If the UPDATE GLOBAL INDEXES keyword is not added, the global indexes of this subpartitioned table become unavailable after the partition is truncated.

Examples

1. View the p0 and p1 partitions of the t2_f_rl table.

   obclient> SELECT * FROM t2_f_rl partition(p0,p1);
   +------+------+
   | COL1 | COL2 |
   +------+------+
   |   99 | 01   |
   |   99 | 02   |
   |  199 | 01   |
   |  199 | 02   |
   +------+------+
   4 rows in set
   

2. Truncate the partitions p0 and p1 of the RANGE-LIST-subpartitioned table t2_f_rl.

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

3. View the p0 and p1 partitions of the t2_f_rl table.

   obclient> SELECT * FROM t2_f_rl partition(p0,p1);
   Empty set
   

Truncate a subpartition

Syntax

ALTER TABLE table_name TRUNCATE SUBPARTITION subpartition_name_list[ UPDATE GLOBAL INDEXES ];

subpartition_name_list:
  subpartition_name[, subpartition_name ...]

Note

• Before you truncate a subpartition from a subpartitioned table, ensure that no active transaction or query exists in this subpartition. Otherwise, SQL statement errors or exceptions may occur. In 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 in a subpartitioned table of the RANGE or LIST partitioning method to clear all data in one or more subpartitions. Supported subpartitioning methods of RANGE or LIST are RANGE-RANGE, RANGE-LIST, LIST-RANGE, and LIST-LIST.
• When you truncate a subpartition from a subpartitioned table with global indexes in Oracle mode, you need to add the UPDATE GLOBAL INDEXES keyword to the ALTER TABLE statement to update the global indexes. If the UPDATE GLOBAL INDEXES keyword is not added, the global indexes of this subpartitioned table become unavailable after the subpartition is truncated.

Examples

Truncate the subpartitions sp3 and sp4 from the partition p1 of the subpartitioned table t2_f_lr, and then update the global indexes.

1. View the sp3 and sp4 subpartitions in the p1 partition of the t2_f_lr table.

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

2. Truncate the subpartitions sp3 and sp4 from the partition p1 of the subpartitioned table t2_f_lr, and then update the global indexes.

   obclient> ALTER TABLE t2_f_lr TRUNCATE SUBPARTITION sp3,sp4 UPDATE GLOBAL INDEXES;
   Query OK, 0 rows affected
   

3. View the sp3 and sp4 subpartitions in the p1 partition of the t2_f_lr table.

   obclient> SELECT * FROM t2_f_lr;
   Empty set