Schedule operations that alter table partitions and partition ranges to avoid impact on your production workload. When dropping or adding new ranges or partitions to a row-partitioned table, refer to the following performance considerations:
- When dropping or adding new ranges or partitions in a populated table, the operation can be fairly quick because the rows remain in the retained ranges and partitions.
- There is a small amount of overhead if dropped ranges and partitions are populated, and still further overhead if any referential integrity constraints are defined on the table.
- There is additional overhead if new ranges are added which are populated with NO RANGE or UNKNOWN partitions or rows in dropped ranges that must be moved to the added ranges.
- You must update any secondary, join, or hash indexes on the table.
Updating secondary, join, and hash indexes can be lengthy operations, depending on several factors, including the size of the table and indexes and the number of rows deleted or moved.
- There is additional overhead if the deleted rows are inserted into a save table. The amount of overhead depends on the number of rows that must be inserted into the save table and the other standard performance issues associated with inserting rows into a table.
- If a table is defined with a NO RANGE partition, specifying a WITH DELETE or WITH INSERT INTO clause in an ALTER TABLE statement has no effect.
Rows from deleted partitions and rows whose partition number evaluates to a value other than 1 through 65,535 for 2-byte partitioning or 1 through 9,223,372,036,854,775,805 for 8-byte partitioning are retained in the NO RANGE partition rather than being moved to the target table specified in the WITH DELETE or WITH INSERT INTO clause.
For additional information, see Purpose and Behavior of the NO RANGE and UNKNOWN Partitions and Rules For Altering a Partitioning For a Table.