Asynchronous Global Index Maintenance 12c - Part II

In part I we discussed how global indexes can now be asynchronously maintained in Oracle 12c when a table partition is dropped or truncated. Basically, when a table partition is dropped/truncated with the UPDATE GLOBAL INDEXES clause, Oracle simply keeps track of the object numbers of those table partitions and ignores any corresponding rowids within the index during subsequent index scans. As such, these table partition operations are very fast and efficient as the global indexes are not actually maintained during the partition operation, but importantly, continue to remain in a usable state

So prior to Oracle 12c, to update global indexes on the fly was a relatively expensive operation as it required all the associated index entries to be deleted from the global indexes.

With Oracle 12c, There are a number of possible ways to cleanup out the orphaned index entries.

#1 - One simple way is to simply rebuild the global index (or index partitions)

alter index t_idx rebuild;


#2 - another possible alternative is to use CLEANUP coaleasce clause.

alter index t_idx coalesce cleanup;

#3 - Yet another possible option is to simply wait for the PMO_DEFERRED_GIDX_MAINT_JOB job to run (scheduled by default during the 2am maintenance window) to clean out orphaned index entries from all currently impacted global indexes

rajesh@PDB1>
rajesh@PDB1> select job_name , start_date,enabled,state,comments
  2  from dba_scheduler_jobs
  3  where job_name ='PMO_DEFERRED_GIDX_MAINT_JOB';
JOB_NAME             START_DATE           ENABL STATE           COMMENTS
-------------------- -------------------- ----- --------------- --------------------
PMO_DEFERRED_GIDX_MA 29-JUN-13 02.00.00.6 TRUE  SCHEDULED       Oracle defined autom
INT_JOB              00000 AM US/CENTRAL                        atic index cleanup f
                                                                or partition mainten
                                                                ance operations with
                                                                 deferred global ind
                                                                ex maintenance

1 row selected.
rajesh@PDB1>

#4 - Yet another alternative is to manually run the dbms_part.cleanup_gidx procedure which is in turn called by this job.

exec dbms_part.cleanup_gidx ;

So with the new asynchronous global index maintenance capabilities of the Oracle 12c database, we can perform a much faster and more efficient drop/truncate table partition operation while keeping our global indexes in a usable state and leave the tidying up of the resultant orphaned index entries to another time and method of our convenience