This new feature in Oracle 12C is as default always on. Each TRUNCATE or DROP commands performed on a partition automatically triggers asynchronous global index maintenance. It means that you don’t need to wait for global INDEX maintenance :).
For backward compatibility you still need to specify UPDATE INDEXES clause.
Limitations of asynchronous global index maintenance:
- Only performed on heap tables
- No support for tables with object types
- No support for tables with domain indexes
- Not performed for the user SYS
Asynchronous cleanup orphans in an index can be done:
- by Oracle job SYS.PMO_DEFERRED_GIDX_MAINT_JOB
- just run above job SYS.PMO_DEFERRED_GIDX_MAINT_JOB
- run procedure DBMS_PART.CLEANUP_GIDX
- run sql statement ALTER INDEX REBUILD [PARTITION] – the same like in previous releases
- run sql statement ALTER INDEX [PARTITION] COALESCE CLEANUP
I have created simple table test_tbl partitioned by RANGE
create table test_tbl ( id1 number, id2 number ) partition by range(id1) ( partition p1 values less than(100000), partition p2 values less than(200000), partition p3 values less than(300000), partition p4 values less than(400000), partition p5 values less than(500000), partition pm values less than(MAXVALUE) );
with 600k records
insert /*+ APPEND */ into test_tbl select level, level from dual connect by level < 600001; commit; select count(*) from test_tbl; COUNT(*) ---------- 600000
Now it’s time to add index. Of course new index hasn’t got any orphans.
create index test_tbl_idx on test_tbl(id1, id2) parallel 6; select orphaned_entries from user_indexes where index_name='TEST_TBL_IDX'; ORPHANED_ENTRIES ---------------- NO
As you can see TRUNCATE is very fast and global index is still valid.
set timing on alter table test_tbl truncate partition p1 update indexes; table TEST_TBL altered. Elapsed: 00:00:00.047 select index_name, status from user_indexes where index_name='TEST_TBL_IDX'; INDEX_NAME STATUS ------------- -------- TEST_TBL_IDX VALID
Of course our index still has got orphans which will be cleaned automatically or you can clean the orphans manually.
select orphaned_entries from user_indexes where index_name='TEST_TBL_IDX'; ORPHANED_ENTRIES ---------------- YES
Automatic cleanup orphans in index
Job SYS.PMO_DEFERRED_GIDX_MAINT_JOB is scheduled by default about 2:00 AM on daily basis. Of course you can change schedule for this job or trigger it manually. Oracle recommends that you do not drop the job.
select job_name, comments from dba_scheduler_jobs where job_name='PMO_DEFERRED_GIDX_MAINT_JOB'; COMMENTS ---------------------------------------- Oracle defined automatic index cleanup for partition maintenance operations with deferred global index maintenance
Manual cleanup orphans in index
You can also force cleanup of an index needing maintenance using one of the following options:
- DBMS_PART.CLEANUP_GIDX – this PL/SQL 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.
--definition dbms_part.cleanup_gidx ( schema_name_in IN VARCHAR2 DEFAULT NULL, table_name_in IN VARCHAR2 DEFAULT NULL ); --so you can run it on --database level dbms_part.cleanup_gidx --schema level exec dbms_part.cleanup_gidx(<schema_name>); --table level dbms_part.cleanup_gidx(<schema_name>, <table_name>);
set timing on begin SYS.dbms_part.cleanup_gidx(user, 'TEST_TBL'); end; / anonymous block completed Elapsed: 00:00:01.532
Next rerun raise exception that no orphans are found
begin SYS.dbms_part.cleanup_gidx(user, 'TEST_TBL'); end; Error report: ORA-20000: No gloabl index segments were cleaned
- ALTER INDEX REBUILD [PARTITION] – this SQL statement rebuilds the entire index or index partition as is done in releases previous to Oracle Database 12c Release 1 (12.1). The resulting index (partition) does not contain any stale entries.
- ALTER INDEX [PARTITION] COALESCE CLEANUP – this SQL statement cleans up any orphaned entries in index blocks.
Have a fun 🙂