Manage a partitioned table

After you create a partitioned table, you can add, drop, or truncate partitions in the partitioned 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 the M197006 partition to the table.

  CREATE TABLE tbl1_r(
    log_id NUMBER,
    log_date DATE NOT NULL DEFAULT SYSDATE
  ) PARTITION BY RANGE(log_date) (
    PARTITION M197001
    VALUES
      LESS THAN(TO_DATE('1970/02/01', 'YYYY/MM/DD')),
      PARTITION M197002
    VALUES
      LESS THAN(TO_DATE('1970/03/01', 'YYYY/MM/DD')),
      PARTITION M197003
    VALUES
      LESS THAN(TO_DATE('1970/04/01', 'YYYY/MM/DD')),
      PARTITION M197004
    VALUES
      LESS THAN(TO_DATE('1970/05/01', 'YYYY/MM/DD')),
      PARTITION M197005
    VALUES
      LESS THAN(TO_DATE('1970/06/01', 'YYYY/MM/DD'))
  );
  

  Add the M197106 partition to the tbl1_r table.

  obclient> ALTER TABLE tbl1_r ADD PARTITION
   M197006 VALUES LESS THAN(TO_DATE('1970/07/01','YYYY/MM/DD'));
  Query OK, 0 rows affected
  

  View the added M197106 partition in the tbl1_r table.

  obclient> SHOW CREATE TABLE tbl1_r\G
  *************************** 1. row ***************************
         TABLE: TBL1_R
  CREATE TABLE: CREATE TABLE "TBL1_R" (
    "LOG_ID" NUMBER,
    "LOG_DATE" DATE DEFAULT sysdate CONSTRAINT "TBL1_R_OBNOTNULL_1650855716241488" NOT NULL ENABLE
  ) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
   partition by range(log_date)
  (partition M197001 values less than (TO_DATE(' 1970-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  partition M197002 values less than (TO_DATE(' 1970-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  partition M197003 values less than (TO_DATE(' 1970-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  partition M197004 values less than (TO_DATE(' 1970-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  partition M197005 values less than (TO_DATE(' 1970-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
  partition M197006 values less than (TO_DATE(' 1970-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')))
  1 row in set
  

  Insert data into the M197106 partition of the tbl1_r table.

  obclient> INSERT INTO tbl1_r VALUES (1,date'1970-06-02');
  Query OK, 1 row affected
  

  View the M197106 partition into which data is inserted.

  obclient> SELECT * FROM tbl1_r partition(M197006);
  +--------+-----------+
  | LOG_ID | LOG_DATE  |
  +--------+-----------+
  |      1 | 02-JUN-70 |
  +--------+-----------+
  1 row in set
  

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

   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' )
          );
  

  Add a partition named p4 to the tbl1_l table.

  obclient> ALTER TABLE tbl1_l ADD PARTITION p4 VALUES('D');
  Query OK, 0 rows affected
  

  View the p4 partition added to the tbl1_l table.

  obclient> SHOW CREATE TABLE tbl1_l\G
  *************************** 1. row ***************************
         TABLE: TBL1_L
  CREATE TABLE: CREATE TABLE "TBL1_L" (
    "LOG_ID" NUMBER(38),
    "LOG_VALUE" VARCHAR2(20)
  ) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
   partition by list(log_value)
  (partition P1 values  ('A'),
  partition P2 values  ('B'),
  partition P3 values  ('C'),
  partition P4 values  ('D'))
  1 row in set
  

  Insert data into the p4 partition of the tbl1_l table.

  obclient> INSERT INTO tbl1_l VALUES(8,'A');
  Query OK, 1 row affected
  

  View the data inserted into the p2 and p3 partitions.

  obclient> SELECT * FROM tbl1_l partition(p1);
  +--------+-----------+
  | LOG_ID | LOG_VALUE |
  +--------+-----------+
  |      8 | A         |
  +--------+-----------+
  1 row 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

• You can drop multiple partitions at a time, but you cannot drop all the partitions in a table.
• Before dropping a partition, ensure that there are no active transactions or queries 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.
• You can drop multiple partitions at a time, but you cannot drop all the partitions in a table.
• Before dropping a partition, ensure that there are no active transactions or queries 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.

Examples

Drop partitions M197105 and M197106 from the partitioned table tbl1_r.

1. View the tbl1_r partitioned table.

   obclient> SHOW CREATE TABLE tbl1_r\G
   *************************** 1. row ***************************
          TABLE: TBL1_R
   CREATE TABLE: CREATE TABLE "TBL1_R" (
     "LOG_ID" NUMBER,
     "LOG_DATE" DATE DEFAULT sysdate CONSTRAINT "TBL1_R_OBNOTNULL_1650855716241488" NOT NULL ENABLE
   ) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
    partition by range(log_date)
   (partition M197001 values less than (TO_DATE(' 1970-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
   partition M197002 values less than (TO_DATE(' 1970-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
   partition M197003 values less than (TO_DATE(' 1970-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
   partition M197004 values less than (TO_DATE(' 1970-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
   partition M197005 values less than (TO_DATE(' 1970-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
   partition M197006 values less than (TO_DATE(' 1970-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')))
   1 row in set
   

2. Drop partitions M197105 and M197106 from the partitioned table tbl1_r.

   obclient> ALTER TABLE tbl1_r DROP PARTITION M197105,M197106;
   Query OK, 0 rows affected
   

3. View the results.

   obclient>SHOW CREATE TABLE tbl1_r\G
   *************************** 1. row ***************************
          TABLE: TBL1_R
   CREATE TABLE: CREATE TABLE "TBL1_R" (
     "LOG_ID" NUMBER,
     "LOG_DATE" DATE DEFAULT sysdate CONSTRAINT "TBL1_R_OBNOTNULL_1650855716241488" NOT NULL ENABLE
   ) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
    partition by range(log_date)
   (partition M197001 values less than (TO_DATE(' 1970-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
   partition M197002 values less than (TO_DATE(' 1970-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
   partition M197003 values less than (TO_DATE(' 1970-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
   partition M197004 values less than (TO_DATE(' 1970-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')))
   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

• You can clear the data in one or more partitions by using the TRUNCATE statement.
• Before truncating a partition, ensure that there are no active transactions or queries 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.
• You can clear the data in one or more partitions by using the TRUNCATE statement.
• Before truncating a partition, ensure that there are no active transactions or queries in this partition. Otherwise, SQL statement errors or exceptions may occur.

Examples

Clear the data in the partitions M197001 and M197002 of the partitioned table tbl1_r, and update the global indexes.

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