By default, partition table maintenance such as drop/truncate partitions invalidate corresponding global index which mark them as
UNUSABLE. User must rebuild the corresponding indexes. Database lets user override the default behavior by specifying
update indexes clause. With this option, database will update the indexes at the same time it executes the maintenance DDL statements, and not mark them as
Prior to 12c, update indexes is a time consuming operation, DBA must wait for the index rebuild complete.
As of 12c, a new feature is supported by Oracle database — Asynchronous Global Index Maintenance.
The partition maintenance operations
PARTITIONare optimized by making the index maintenance for metadata only.
Asynchronous global index maintenance for
TRUNCATEis performed by default; however, the
INDEXESclause is still required for backward compatibility.
Only performed on the heap tables
No support for tables with object types
No support for tables with domain indexes
Not performed for the user SYS
There's an automatically maintenance scheduler job
SYS.PMO_DEFERRED_GIDX_MAINT_JOB to clean up all global indexes. This job is schedule at 2:00AM by defaul. You can run this job at any time by calling
DBMS_SCHEDULER.RUN_JOB,also, DBA can modify scheduler window for running this job.
-- query the maintenance window
If we truncate/drop a partition tables with
update indexes, we can maintenance index manually:
Check the index status
SELECT table_name, index_name,
ORDER BY 1;
Execute one of the following SQLs
/* This PL/SQL package procedure gathers
the list of global indexes in the system that
may require cleanup and runs the operations necessary
to restore the indexes to a clean state.
exec DBMS_PART.CLEANUP_GIDX('USERNAME','INDEX_NAME'); -- specific index
exec dbms_part.cleanup_gidx(<schema_name>, <table_name>);
-- this SQL statement rebuilds the entire index
-- or index partition as was done prior to Oracle Database 12.1 releases
ALTER INDEX INDEX_NAME REBUILD;
--This SQL statement cleans up any orphaned entries in index blocks
ALTER INDEX INDEX_NAME COALESCE CLEANUP;
Check the index status again