Manage a partitioned table

After you create a partitioned table, you can add a partition to the table, drop a partition from the table, or truncate a partition in the table.

Support for partition management operations

Add a partition

Syntax

ALTER TABLE table_name ADD partition_option;

partition_option:
  range_partition_option | list_partition_option

range_partition_option:
  PARTITION partition_name VALUES LESS THAN partition_expr

list_partition_option:
  PARTITION partition_name VALUES partition_expr

Note

• For a RANGE-partitioned table, you can add a partition only after the last partition. You cannot add a partition after the first partition or in the middle of the range. If a table has a MAXVALUE partition, you cannot add new partitions to the table.

• For a LIST-partitioned table, you cannot add a partition that conflicts with existing partitions. If Default Partition is specified for a LIST-partitioned table, you cannot add more partitions to the table.

• Adding a partition to a RANGE- or LIST-partitioned table does not affect the use of global and local indexes.

Examples

• Add a partition to a RANGE-partitioned table. Create a RANGE-partitioned table named tbl1_r and add a partition named M202006 to the table.

  obclient> CREATE TABLE tbl1_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'))
  );
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE tbl1_r ADD PARTITION
         M202006 VALUES LESS THAN(TO_DATE('2020/07/01','YYYY/MM/DD'));
  Query OK, 0 rows affected
  

• Add a partition to a LIST-partitioned table. Create a LIST-partitioned table named tbl1_l and add a partition named p4 to the table.

  obclient> CREATE TABLE tbl1_l(log_id INT,log_value VARCHAR2(20))
         PARTITION BY LIST(log_value)
          (PARTITION p1 VALUES ('A'),
           PARTITION p2 VALUES ( 'B' ),
           PARTITION p3 VALUES ( 'C' )
          );
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE tbl1_l ADD PARTITION p4 VALUES('D');
  Query OK, 0 rows affected
  

Drop a partition

Syntax

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

partition_name_list:
  partition_name [, partition_name ...]

Note

• You can drop multiple partitions at a time, but you cannot drop all partitions in a table.

• Before dropping a partition, ensure that no active transaction or query exists in this partition. Otherwise, SQL statement errors or exceptions may occur.

• When you drop a partition, the data in the partition is also dropped. If you want to drop only the data, use the TRUNCATE statement.

• In Oracle mode, for a partitioned table with global indexes, when you drop a partition, you must 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 partitioned table become unavailable after the partition is dropped.

Examples

Drop the M202005 and M202006 partitions from the tbl1_r partitioned table.

obclient> ALTER TABLE tbl1_r DROP PARTITION M202005,M202006;
Query OK, 0 rows affected

Truncate a partition

Syntax

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

partition_name_list:
  partition_name [, partition_name ...]

Note

• You can clear the data in one or more partitions by using the TRUNCATE statement.

• Before truncating a partition, ensure that no active transaction or query exists in this partition. Otherwise, SQL statement errors or exceptions may occur.

• In Oracle mode, for a partitioned table with global indexes, when you truncate a partition, you must 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 partitioned table become unavailable after the partition is truncated.

Examples

Clear the data in the partitions M202001 and M202002 of the partitioned table tbl1_r, and update the global indexes.

obclient> ALTER TABLE tbl1_r TRUNCATE PARTITION M202001,M202002 UPDATE GLOBAL INDEXES;
Query OK, 0 rows affected