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:

• Automatically
  • by Oracle job SYS.PMO_DEFERRED_GIDX_MAINT_JOB
• Manually
  • 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

Example

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>);

Example

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 🙂

Tomasz