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.