PolarDB:ALTER TABLE...ADD PARTITION

How it works

ALTER TABLE ... ADD PARTITION is a metadata operation, but its performance and concurrency impact are primarily determined by the table's index configuration.

• Lock behavior: When you run ADD PARTITION, the database acquires an exclusive lock on the target table. This lock blocks all concurrent read and write operations (SELECT, INSERT, UPDATE, and DELETE) on the table. The lock duration depends mainly on the time required to create indexes on the new partition.

• Index maintenance

  • Local index: The command automatically creates a corresponding index partition on the new partition. If the table has multiple local indexes, the system creates a new partition for each one. It's the main reason for the operation's duration.

  • Global index: The structure remains unchanged, requiring no additional maintenance, as it automatically covers data inserted into the new partition.

• Operation duration

  • Tables without indexes: The operation is nearly instantaneous as it only updates the table's metadata.

  • Tables with indexes: The operation's duration is proportional to the time required to create local indexes on the new, empty partition.

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 for the new partition must reference the same partition key columns as defined for the table.

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

• Partition value restrictions

  • MAXVALUE and DEFAULT partition

    • Limitation: You cannot add a new partition to a range-partitioned table that has a MAXVALUE partition or to a list-partitioned table that has a DEFAULT partition. This is because MAXVALUE and DEFAULT partitions logically cover all unspecified values, leaving no room for a new partition.

    • Solution: Use ALTER TABLE ... SPLIT PARTITION to split the MAXVALUE or DEFAULT partition. This creates a new boundary, allowing you to add a new partition.

    • Note

      This operation may move data, which can result in significant I/O and locking overhead. Perform this operation during off-peak hours or within a maintenance window.

      -- Example: Split the MAXVALUE partition to add a partition for 2024
      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 requirement For a new RANGE partition, the VALUES LESS THAN value must be greater than the upper bound of the highest existing partition. Otherwise, the operation fails.

  • LIST partition value uniqueness

    The values of a new LIST partition cannot overlap with those in any existing partition. Otherwise, the operation fails with an error like ERROR: partition "xx" would overlap partition "xxx".

• Privilege requirements

  To execute ALTER TABLE ... ADD PARTITION, you must be the table owner or use a privileged account.

Best practices

• Execute in a maintenance window: Because ADD PARTITION acquires an exclusive lock on the table, it blocks DML and DDL operations. For large tables with local indexes, the index creation process can be lengthy, causing prolonged service interruptions. We recommend performing this operation during off-peak hours or a scheduled maintenance window to avoid blocking your online services.

• Monitor lock waits: During the operation, monitor the database for lock waits. If the lock wait time is excessive, you may need to terminate the operation and reschedule it.

• Recommended number of partitions: Although there is no physical limit to the number of partitions a table can have, we recommend keeping the total number of partitions for a single table under 1,000 for management and performance reasons. An excessive number of partitions increases the parsing cost for the query optimizer, which can degrade query performance.

Syntax

ALTER TABLE table_name ADD PARTITION partition_spec;

Add a partition to a list-partitioned table

Use this operation to add a new data category to a table partitioned by a list of discrete values, such as countries or status codes.

1. Create a sample list-partitioned table. The following sales_list table is partitioned by the country column.

   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. Use the ALTER TABLE ... ADD PARTITION command to add a new partition named east_asia to store data for 'CHINA' and 'KOREA'.

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

3. (Optional) Verify that the new partition was added successfully.

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

   The query result should include the newly added east_asia partition.

Add a partition to a range-partitioned table

Use this operation to add a new time period or numerical range to a table partitioned by a range of continuous values, such as dates or IDs.

1. Create a sample range-partitioned table. The following sales_range table is partitioned by the sale_date column.

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

   Note

   The example uses the TO_DATE function to ensure date format clarity. In practice, the string format must match the database's NLS_DATE_FORMAT setting.

2. Use ALTER TABLE ... ADD PARTITION to add a new partition named q1_2024. The range of the new partition must be greater than the upper bound of all existing partitions.

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

3. (Optional) Verify that the new partition was added successfully.

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

   The query result should include the newly added q1_2024 partition at the end of the partition list.

Add a partition to a composite partitioned table

For a composite partitioned table, such as RANGE-LIST, ADD PARTITION lets you define subpartitions at the same time as you add a main partition.

1. Create a sample RANGE-LIST composite partitioned 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 a new primary partition named p_2024 and define two subpartitions: north and south.

   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 that the new subpartitions were created successfully.

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

FAQ