Impact of a Partition Maintenance Operation on Global Indexes

Whenever a global index is defined on a partitioned or nonpartitioned table, there is no correlation between a distinct table partition and the index. Consequently, any partition maintenance operation affects all global indexes or index partitions.

The partitions of tables containing local indexes are locked to prevent DML operations against the affect table partitions, except for an ONLINE MOVE operation. However, unlike the index maintenance for local indexes, any global index is still fully available for DML operations and does not affect the online availability of the OLTP system.

Conceptually and technically, the index maintenance for global indexes for a partition maintenance operation is comparable to the index maintenance that would become necessary for a semantically identical DML operation, except for DROP and TRUNCATE where the global index maintenance gets delayed to a later point in time. For more information about managing global indexes, refer to "Management of Global Partitioned Indexes".

For example, dropping an old partition is semantically equivalent to deleting all the records of the old partition using the SQL DELETE statement. In the DML case, all index entries of the deleted data set have to be removed from any global index as a standard index maintenance operation, which does not affect the availability of an index for SELECT and DML operations.

The DROP PARTITION also does not affect the index availability, but enables you to decouple the necessary index maintenance from the initial data removal without affecting the availability of the global indexes. In this scenario, a drop operation represents the optimal approach: data is removed without the overhead of a conventional DELETE operation and the global indexes are maintained in a nonintrusive manner.