Performance Implications of Altering a Primary Index Using ALTER TABLE Requests
You should consider the following information about the immediate performance impact
of altering the primary index for a table to best schedule the operation in a way
that has the least impact on your production workload.
Altering an empty table is fairly quick.
Altering a primary‑indexed table to have a unique or non‑unique primary index is fairly
Altering a primary‑indexed table to change the primary index name is fairly quick.
The following cumulative performance issues are all concerned with dropping or adding
new ranges or partitions to a row‑partitioned table.
When dropping or adding new ranges or partitions for a populated table, the operation
can be fairly quick because rows that remain in the retained ranges and partitions
need not be processed or repartitioned.
There is an additional small overhead if dropped ranges and partitions are populated,
and still further overhead if any referential integrity constraints are defined on
There is additional overhead if new ranges are added and there are populated NO RANGE
[OR UNKNOWN) or UNKNOWN partitions or rows in dropped ranges that need to be moved
to the added ranges because rows must be processed to determine if any of them need
to be assigned to one of the new ranges and, if so, to move them to the correct range.
You must update any secondary, join, or hash indexes on the table.
Updating secondary, join, and hash indexes can be lengthy operations and depend 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
degree of this 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 request used to change its definition has no
In this case, rows from deleted partitions and rows whose partition number evaluates
to something other than 1 - 65,535, inclusive, for 2‑byte partitioning or something
other than 1 - 9,223,372,036,854,775,805, inclusive, 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.
See “Purpose and Behavior of the NO RANGE and UNKNOWN Partitions” on page 640 and “Rules For Altering the Partitioning Expression For a Table” on page 110 for additional information.