PolarDB:ALTER TABLE...ADD PARTITION

How it works

ALTER TABLE ... ADD PARTITION is a metadata operation, but its duration and concurrency impact depend almost entirely on the table's index configuration.

When you run the command, the database acquires an exclusive lock on the target table, blocking all concurrent SELECT, INSERT, UPDATE, and DELETE operations. The lock is held until the command completes.

Index behavior after `ADD PARTITION`

Operation duration

• Tables without indexes: nearly instantaneous; only table metadata is updated.

• Tables with indexes: proportional to the time required to create local indexes on the new, empty partition.

Prerequisites

Before you begin, ensure that you have:

• An existing partitioned table (LIST or RANGE)

• Table owner privileges or a privileged account

Limitations

Basic definition rules

1. Partition type consistency: The new partition must match the table's existing partition type (LIST or RANGE).

2. Partition key consistency: The partitioning rule must reference the same partition key columns defined for the table.

3. Unique partition name: The name must be unique across all partitions and subpartitions of the table.

Partition value restrictions

• MAXVALUE and DEFAULT partitions: You cannot add a partition to a RANGE-partitioned table that already has a MAXVALUE partition, or to a LIST-partitioned table that already has a DEFAULT partition. These catch-all partitions logically cover all unspecified values, leaving no room for a new one. Use ALTER TABLE ... SPLIT PARTITION to split the MAXVALUE or DEFAULT partition first, then add the new partition.

  Splitting a MAXVALUE or DEFAULT partition may move data, which can cause significant I/O and locking overhead. Perform this operation during off-peak hours.

  -- Example: split the MAXVALUE partition to make room for 2024 data
  ALTER TABLE sales SPLIT PARTITION max_partition AT (TO_DATE('2025-01-01', 'YYYY-MM-DD')) INTO (PARTITION p_2024, PARTITION max_partition);

• RANGE partition order: The VALUES LESS THAN value of the new partition must be greater than the upper bound of the current highest partition. You cannot insert a partition between or before existing partitions.

• LIST partition value uniqueness: Values in the new partition cannot overlap with values in any existing partition. Overlapping values produce the error ERROR: partition "xx" would overlap partition "xxx".

Privilege requirements

You must be the table owner or use a privileged account.

Syntax

ALTER TABLE table_name ADD PARTITION partition_spec;

partition_spec — for a LIST partition:

PARTITION partition_name VALUES (value_list)
  [TABLESPACE tablespace_name]
  [(subpartition_spec, ...)]

partition_spec — for a RANGE partition:

PARTITION partition_name VALUES LESS THAN (value_list)
  [TABLESPACE tablespace_name]
  [(subpartition_spec, ...)]

subpartition_spec — for a LIST subpartition:

SUBPARTITION subpartition_name VALUES (value_list)
  [TABLESPACE tablespace_name]

subpartition_spec — for a RANGE subpartition:

SUBPARTITION subpartition_name VALUES LESS THAN (value_list)
  [TABLESPACE tablespace_name]

Parameters

Add a partition to a LIST-partitioned table

This example adds a new geographic region to a table partitioned by country.

1. Create the sample sales_list table, partitioned by country.

   CREATE TABLE sales_list (
       dept_no     NUMBER,
       part_no     VARCHAR2(50),
       country     VARCHAR2(20),
       sale_date   DATE,
       amount      NUMBER
   )
   PARTITION BY LIST(country) (
       PARTITION europe   VALUES ('FRANCE', 'ITALY'),
       PARTITION asia     VALUES ('INDIA', 'PAKISTAN'),
       PARTITION americas VALUES ('US', 'CANADA')
   );

2. Add a new east_asia partition for 'CHINA' and 'KOREA'.

   ALTER TABLE sales_list ADD PARTITION east_asia VALUES ('CHINA', 'KOREA');

3. (Optional) Verify the new partition was created.

   SELECT partition_name, high_value FROM ALL_TAB_PARTITIONS WHERE table_name = 'sales_list';

   The result includes the east_asia partition.

Add a partition to a RANGE-partitioned table

This example extends a date-partitioned table with a new quarterly partition.

1. Create the sample sales_range table, partitioned by sale_date.

   The TO_DATE function ensures date format clarity. In practice, the string format must match the database's NLS_DATE_FORMAT setting.

   CREATE TABLE sales_range (
       dept_no     NUMBER,
       part_no     VARCHAR2(50),
       country     VARCHAR2(20),
       sale_date   DATE,
       amount      NUMBER
   )
   PARTITION BY RANGE(sale_date) (
       PARTITION q1_2023 VALUES LESS THAN (TO_DATE('2023-04-01', 'YYYY-MM-DD')),
       PARTITION q2_2023 VALUES LESS THAN (TO_DATE('2023-07-01', 'YYYY-MM-DD')),
       PARTITION q3_2023 VALUES LESS THAN (TO_DATE('2023-10-01', 'YYYY-MM-DD')),
       PARTITION q4_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))
   );

2. Add the q1_2024 partition. Its upper bound must be greater than 2024-01-01, the current highest boundary.

   ALTER TABLE sales_range ADD PARTITION q1_2024 VALUES LESS THAN (TO_DATE('2024-04-01', 'YYYY-MM-DD'));

3. (Optional) Verify the new partition appears at the end of the partition list.

   SELECT partition_name, high_value FROM ALL_TAB_PARTITIONS WHERE table_name = 'sales_range';

Add a partition to a composite partitioned table

For a RANGE-LIST composite partitioned table, you can define subpartitions at the same time you add a top-level partition.

1. Create the sample composite_sales table.

   CREATE TABLE composite_sales (
       sale_id     NUMBER,
       sale_date   DATE,
       region      VARCHAR2(20)
   )
   PARTITION BY RANGE(sale_date)
   SUBPARTITION BY LIST(region) (
       PARTITION p_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')) (
           SUBPARTITION p_2023_north VALUES ('NORTH'),
           SUBPARTITION p_2023_south VALUES ('SOUTH')
       )
   );

2. Add the p_2024 partition with two subpartitions.

   ALTER TABLE composite_sales ADD PARTITION p_2024
       VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD')) (
           SUBPARTITION p_2024_north VALUES ('NORTH'),
           SUBPARTITION p_2024_south VALUES ('SOUTH')
       );

3. (Optional) Verify the subpartitions were created.

   SELECT partition_name, subpartition_name, high_value
   FROM ALL_TAB_SUBPARTITIONS
   WHERE table_name = 'composite_sales' AND partition_name = 'p_2024';

Best practices

Schedule during a maintenance window

ADD PARTITION holds an exclusive lock on the table for the duration of the operation, blocking all DML. For large tables with multiple local indexes, the lock can last several minutes. Schedule this operation during off-peak hours to avoid impacting online services.

Monitor lock waits

During the operation, monitor the database for lock waits. If the wait time becomes excessive, cancel the operation and reschedule it.

Estimate duration before running

For tables with local indexes, estimate the operation's duration by measuring how long it takes to create the same indexes on an empty table of the same schema.

Limit total partition count

Although there is no hard limit on the number of partitions per table, keep the total under 1,000. Excessive partitions increase the query optimizer's parsing cost and can degrade query performance.

FAQ

ALTER TABLE sales_list ADD PARTITION new_region VALUES ('FRANCE', 'SPAIN');

ALTER TABLE sales_list ADD PARTITION new_region VALUES ('SPAIN');

ALTER TABLE sales_range ADD PARTITION q1_2024 VALUES LESS THAN ('2024-04-01');