Impact of a Partition Maintenance Operation on a Partitioned Table with Local Indexes

When a partition maintenance operation takes place, Oracle Database locks the affected table partitions for any DML operation, except in the case of an ONLINE MOVE. Data in the affected partitions, except a DROP or TRUNCATE operation, is still fully accessible for any SELECT operation. Because local indexes are logically coupled with the table (data) partitions, only the local index partitions of the affected table partitions have to be maintained as part of a partition maintenance operation, enabling optimal processing for the index maintenance.

For example, when you move an older partition from a high-end storage tier to a low-cost storage tier using the ALTER TABLE MOVE ONLINE functionality, the data and the index are always available for SELECT and DML operations; the necessary index maintenance is to update the existing index partition to reflect the new physical location of the data. If you drop an older partition after you have archived it, then its local index partitions get dropped as well, and for global indexes the orphaned entries for the removed partitions get marked, enabling a split-second partition maintenance operation that affects only the data dictionary.