Asynchronous Global Index Maintenance for Dropping and Truncating Partitions
The partition maintenance operations DROP PARTITION and TRUNCATE PARTITION are optimized by making the index maintenance for metadata only.
Asynchronous global index maintenance for DROP and TRUNCATE is performed by default; however, the UPDATE INDEXES clause is still required for backward compatibility.
The following list summarizes the 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

* Maintenance operations on indexes can be performed with the automatic scheduler job SYS.PMO_DEFERRED_GIDX_MAINT_JOB to clean up all global indexes. This job is scheduled to run at 2:00 A.M. on a daily basis by default. You can run this job at any time using DBMS_SCHEDULER.RUN_JOB if you want to proactively clean up the indexes. You can also modify the job to run with a different schedule based on your specific requirements. However, Oracle recommends that you do not drop the job.
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.
* 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.

# 创建测试表
TEST@czhpdb1 > create table test_part (id number,name varchar2(30))
  2  partition by range(id)
  3  (
  4  partition p1 values less than (1000),
  5  partition p2 values less than (2000),
  6  partition p3 values less than (3000),
  7  partition p4 values less than (4000),
  8  partition p5 values less than (maxvalue)
  9  );
# 插入测试数据
TEST@czhpdb1 > declare
id number;
begin
for i in 1..6000 loop
    id:=i;
    insert into test_part values(id,'nihaoma');
end loop;
commit;
end;
/
TEST@czhpdb1 > select count(*) from test_part;
                                COUNT(*)
----------------------------------------
                                    6000
# 创建global index
                                    
TEST@czhpdb1 > create index idx_test_part on test_part(id) global indexing full;
Index created.
                                 
# 查询global index状态                                    
TEST@czhpdb1 > select table_name,index_name,status,num_rows from user_indexes where table_name='TEST_PART';
TABLE_NAME      INDEX_NAME           STATUS                                     NUM_ROWS
--------------- -------------------- ---------- ----------------------------------------
TEST_PART       IDX_TEST_PART        VALID                                          6000
# 收集表统计信息
TEST@czhpdb1 > exec dbms_stats.gather_table_stats(ownname => 'TEST',tabname => 'TEST_PART');
PL/SQL procedure successfully completed.
# 查看表分区状态
TEST@czhpdb1 > select table_name,partition_name,num_rows,blocks from user_tab_partitions where table_name='TEST_PART';
TABLE_NAME      PARTITION_NAME                                  NUM_ROWS      BLOCKS
--------------- --------------- ---------------------------------------- -----------
TEST_PART       P1                                                   999        1006
TEST_PART       P2                                                  1000        1006
TEST_PART       P3                                                  1000        1006
TEST_PART       P4                                                  1000        1006
TEST_PART       P5                                                  2001        1006

# update indexes更新所有索引，global 或者local，而当使用update indexes更新global index时，不能指定子句，
# update global indexes只能用于global indexes
TEST@czhpdb1 > alter table test_part drop partition p2 update indexes;      
Table altered.
# 可以看到索引并没有失效，但是NUM_ROWS并未发生改变，也就是索引并未被实时更新
TEST@czhpdb1 > select table_name,index_name,status,num_rows from user_indexes where table_name='TEST_PART';
TABLE_NAME                               INDEX_NAME           STATUS                                     NUM_ROWS
---------------------------------------- -------------------- ---------- ----------------------------------------
TEST_PART                                IDX_TEST_PART        VALID                                          5001
# 可以看到产生了延迟GLOBAL INDEX MAINTAINE的定时任务，默认凌晨两点开始自动执行维护global index
col job_name for a50
col LAST_START_DATE for a40
col NEXT_RUN_DATE for a40
select JOB_NAME,LAST_START_DATE,NEXT_RUN_DATE from DBA_SCHEDULER_JOBS where JOB_NAME='PMO_DEFERRED_GIDX_MAINT_JOB';
JOB_NAME                                           LAST_START_DATE                          NEXT_RUN_DATE
-------------------------------------------------- ---------------------------------------- ----------------------------------------
PMO_DEFERRED_GIDX_MAINT_JOB                        06-JAN-20 03.06.37.673278 AM PST8PDT     07-JAN-20 02.00.00.679539 AM PST8PDT   
# user_indexes视图orphaned_entries字段标识global index是否包含索引延迟维护的过期条目
TEST@czhpdb1 > select table_name,index_name,status,num_rows,orphaned_entries from user_indexes where table_name='TEST_PART';
TABLE_NAME                               INDEX_NAME           STATUS                                     NUM_ROWS ORPHANED_
---------------------------------------- -------------------- ---------- ---------------------------------------- ---------
TEST_PART                                IDX_TEST_PART        VALID                                          5001 YES

# 通过调用系统包DBMS_PART.CLEANUP_GIDX
col job_name for a50
col LAST_START_DATE for a40
col NEXT_RUN_DATE for a40
select JOB_NAME,LAST_START_DATE,NEXT_RUN_DATE from DBA_SCHEDULER_JOBS where JOB_NAME='PMO_DEFERRED_GIDX_MAINT_JOB';
JOB_NAME                                           LAST_START_DATE                          NEXT_RUN_DATE
-------------------------------------------------- ---------------------------------------- ----------------------------------------
PMO_DEFERRED_GIDX_MAINT_JOB                        06-JAN-20 03.06.37.673278 AM PST8PDT     07-JAN-20 02.00.00.679539 AM PST8PDT   
SYS@czh19c > exec DBMS_PART.CLEANUP_GIDX('TEST','TEST_PART');
PL/SQL procedure successfully completed.
TEST@czhpdb1 > select table_name,index_name,status,num_rows,orphaned_entries from user_indexes where table_name='TEST_PART';
TABLE_NAME                               INDEX_NAME           STATUS                                     NUM_ROWS ORPHANED_
---------------------------------------- -------------------- ---------- ---------------------------------------- ---------
TEST_PART                                IDX_TEST_PART        VALID                                          5001 NO

# 使用sys连接数据库时，一定要注意切换pdb，只有切换到正确的pdb，才能正确的执行调度定时任务完成相应维护操作
SYS@czh19c > alter session set container=czhpdb1;
Session altered.
SYS@czh19c > show pdbs;
                                  CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------------------------------------- ------------------------------ ---------- ----------
                                       3 CZHPDB1                        READ WRITE NO
                                       
# 查询DBA_SCHEDULER_JOBS，查看调度任务
SYS@czh19c > col job_name for a50
SYS@czh19c > col LAST_START_DATE for a40
SYS@czh19c > col NEXT_RUN_DATE for a40
SYS@czh19c > select JOB_NAME,LAST_START_DATE,NEXT_RUN_DATE from DBA_SCHEDULER_JOBS where JOB_NAME='PMO_DEFERRED_GIDX_MAINT_JOB';
JOB_NAME                                           LAST_START_DATE                          NEXT_RUN_DATE
-------------------------------------------------- ---------------------------------------- ----------------------------------------
PMO_DEFERRED_GIDX_MAINT_JOB                        06-JAN-20 03.06.37.606301 AM PST8PDT     07-JAN-20 02.00.00.609298 AM PST8PDT
# 手工执行调度任务
SYS@czh19c > exec dbms_scheduler.run_job('PMO_DEFERRED_GIDX_MAINT_JOB');
PL/SQL procedure successfully completed.
# 查看global index状态，已经被维护
TEST@czhpdb1 > select table_name,index_name,status,num_rows,orphaned_entries from user_indexes where table_name='TEST_PART';
TABLE_NAME                               INDEX_NAME           STATUS                                     NUM_ROWS ORPHANED_
---------------------------------------- -------------------- ---------- ---------------------------------------- ---------
TEST_PART                                IDX_TEST_PART        VALID                                          5001 NO

# 可以rebuild启用并行parallel，rebuild完毕之后，使用alter index no paralle，关闭并行度
TEST@czhpdb1 > alter index idx_test_part rebuild online;
Index altered.
TEST@czhpdb1 > select table_name,index_name,status,num_rows,orphaned_entries from user_indexes where table_name='TEST_PART';
TABLE_NAME                               INDEX_NAME           STATUS                                     NUM_ROWS ORPHANED_
---------------------------------------- -------------------- ---------- ---------------------------------------- ---------
TEST_PART                                IDX_TEST_PART        VALID                                         10003 NO

# COALESCE
# Specify this clause to merge the contents of index partition blocks where possible # to free blocks for reuse.
# CLEANUP
# Specify CLEANUP to remove orphaned index entries for records that were previously
# dropped or truncated by a table partition maintenance operation.
# To determine whether an index partition contains orphaned index entries, you can
# query the ORPHANED_ENTRIES column of the USER_, DBA_, ALL_PART_INDEXES
# data dictionary views. Refer to Oracle Database Reference for more information
TEST@czhpdb1 > alter index idx_test_part coalesce cleanup;
Index altered.
TEST@czhpdb1 > select table_name,index_name,status,num_rows,orphaned_entries from user_indexes where table_name='TEST_PART';
TABLE_NAME                               INDEX_NAME           STATUS                                     NUM_ROWS ORPHANED_
---------------------------------------- -------------------- ---------- ---------------------------------------- ---------
TEST_PART                                IDX_TEST_PART        VALID                                         10003 NO