About Moving Partitions and Subpartitions

Use the MOVE PARTITION clause of the ALTER TABLE statement to:

  • Re-cluster data and reduce fragmentation

  • Move a partition to another tablespace

  • Modify create-time attributes

  • Store the data in compressed format using table compression

Typically, you can change the physical storage attributes of a partition in a single step using an ALTER TABLE/INDEX MODIFY PARTITION statement. However, there are some physical attributes, such as TABLESPACE, that you cannot modify using MODIFY PARTITION. In these cases, use the MOVE PARTITION clause. Modifying some other attributes, such as table compression, affects only future storage, but not existing data.

If the partition being moved contains any data, then indexes may be marked UNUSABLE according to the following table:

Table Type Index Behavior

Regular (Heap)

Unless you specify UPDATE INDEXES as part of the ALTER TABLE statement:

  • The matching partition in each local index is marked UNUSABLE. You must rebuild these index partitions after issuing MOVE PARTITION.

  • Any global indexes, or all partitions of partitioned global indexes, are marked UNUSABLE.

Index-organized

Any local or global indexes defined for the partition being moved remain usable because they are primary-key based logical rowids. However, the guess information for these rowids becomes incorrect.

This section contains the following topics:

see Also: