Asynchronous Global Index Maintenance
In Oracle 12c the commands DROP PARTITION and TRUNCATE PARTITION only affects metadata. The index maintenance is asynchronous now, thus it does not invalidate indexes on the partitions. You then use UPDATE INDEXES commands to synch indexes during maintenance hours. Limitations of asynchronous global index maintenance are:
- Only for heap tables.
- Object types in table not supported.
- Domain indexes not supported.
- Cannot be user SYS
SYS.PMO_DEFERRED_GIDX_MAINT_JOB –
Oracle 12c provides this Automatic scheduled job to execute at 0200 and it updates any indexes impacted by DROP PARTITION and TRUNCATE PARTITION commands. This job can be executed anytime by DBMS_SCHEDULER.RUN_JOB.
Partial Indexes –
In Oracle 12c you can now create local and global indexes on subsets of partitions. Partial indexes do not supported unique indexes and cannot enforce unique constraints. By default, indexes are created as FULL indexes. You create partial indexing using the INDEXING clause at the partition and sub-partition level.
ONLINE Move Partition –
In Oracle 12c, the ALTER TABLE … MOVE PARTITION allows for DML operations to take place while executing. During MOVE operation, global indexes are maintained and rebuilt later by scheduler job.
Larry Catt
OCP