#updateindexes更新所有索引，global或者local，而当使用updateindexes更新globalindex时，不能指定子句，#updateglobalindexes只能用于globalindexesTEST@czhpdb1>altertabletest_partdroppartitionp2updateindexes;Tablealtered.#可以看到索引并没有失效，但是NUM_ROWS并未发生改变，也就是索引并未被实时更新TEST@czhpdb1>selecttable_name,index_name,status,num_rowsfromuser_indexeswheretable_name='TEST_PART';TABLE_NAMEINDEX_NAMESTATUSNUM_ROWS--------------------------------------------------------------------------------------------------------------TEST_PARTIDX_TEST_PARTVALID5001#可以看到产生了延迟GLOBALINDEXMAINTAINE的定时任务，默认凌晨两点开始自动执行维护globalindexcoljob_namefora50colLAST_START_DATEfora40colNEXT_RUN_DATEfora40selectJOB_NAME,LAST_START_DATE,NEXT_RUN_DATEfromDBA_SCHEDULER_JOBSwhereJOB_NAME='PMO_DEFERRED_GIDX_MAINT_JOB';JOB_NAMELAST_START_DATENEXT_RUN_DATE----------------------------------------------------------------------------------------------------------------------------------PMO_DEFERRED_GIDX_MAINT_JOB06-JAN-2003.06.37.673278AMPST8PDT07-JAN-2002.00.00.679539AMPST8PDT#user_indexes视图orphaned_entries字段标识globalindex是否包含索引延迟维护的过期条目TEST@czhpdb1>selecttable_name,index_name,status,num_rows,orphaned_entriesfromuser_indexeswheretable_name='TEST_PART';TABLE_NAMEINDEX_NAMESTATUSNUM_ROWSORPHANED_-----------------------------------------------------------------------------------------------------------------------TEST_PARTIDX_TEST_PARTVALID5001YES（4）手工维护延迟索引维护global index

如果需要，也可以通过如下四种办法手工维护延迟维护global index。

方法1：DBMS_PART.CLEANUP_GIDX

#通过调用系统包DBMS_PART.CLEANUP_GIDXcoljob_namefora50colLAST_START_DATEfora40colNEXT_RUN_DATEfora40selectJOB_NAME,LAST_START_DATE,NEXT_RUN_DATEfromDBA_SCHEDULER_JOBSwhereJOB_NAME='PMO_DEFERRED_GIDX_MAINT_JOB';JOB_NAMELAST_START_DATENEXT_RUN_DATE----------------------------------------------------------------------------------------------------------------------------------PMO_DEFERRED_GIDX_MAINT_JOB06-JAN-2003.06.37.673278AMPST8PDT07-JAN-2002.00.00.679539AMPST8PDTSYS@czh29c>execDBMS_PART.CLEANUP_GIDX('TEST','TEST_PART');PL/SQLproceduresuccessfullycompleted.TEST@czhpdb1>selecttable_name,index_name,status,num_rows,orphaned_entriesfromuser_indexeswheretable_name='TEST_PART';TABLE_NAMEINDEX_NAMESTATUSNUM_ROWSORPHANED_-----------------------------------------------------------------------------------------------------------------------TEST_PARTIDX_TEST_PARTVALID5001NO

方法2：dbms_scheduler.run_job

#使用sys连接数据库时，一定要注意切换pdb，只有切换到正确的pdb，才能正确的执行调度定时任务完成相应维护操作SYS@czh29c>altersessionsetcontainer=czhpdb1;Sessionaltered.SYS@czh29c>showpdbs;CON_IDCON_NAMEOPENMODERESTRICTED------------------------------------------------------------------------------------------3CZHPDB1READWRITENO#查询DBA_SCHEDULER_JOBS，查看调度任务SYS@czh29c>coljob_namefora50SYS@czh29c>colLAST_START_DATEfora40SYS@czh29c>colNEXT_RUN_DATEfora40SYS@czh29c>selectJOB_NAME,LAST_START_DATE,NEXT_RUN_DATEfromDBA_SCHEDULER_JOBSwhereJOB_NAME='PMO_DEFERRED_GIDX_MAINT_JOB';JOB_NAMELAST_START_DATENEXT_RUN_DATE----------------------------------------------------------------------------------------------------------------------------------PMO_DEFERRED_GIDX_MAINT_JOB06-JAN-2003.06.37.606301AMPST8PDT07-JAN-2002.00.00.609298AMPST8PDT#手工执行调度任务SYS@czh29c>execdbms_scheduler.run_job('PMO_DEFERRED_GIDX_MAINT_JOB');PL/SQLproceduresuccessfullycompleted.#查看globalindex状态，已经被维护TEST@czhpdb1>selecttable_name,index_name,status,num_rows,orphaned_entriesfromuser_indexeswheretable_name='TEST_PART';TABLE_NAMEINDEX_NAMESTATUSNUM_ROWSORPHANED_-----------------------------------------------------------------------------------------------------------------------TEST_PARTIDX_TEST_PARTVALID5001NO

方法3：重建索引

#可以rebuild启用并行parallel，rebuild完毕之后，使用alterindexnoparalle，关闭并行度TEST@czhpdb1>alterindexidx_test_partrebuildonline;Indexaltered.TEST@czhpdb1>selecttable_name,index_name,status,num_rows,orphaned_entriesfromuser_indexeswheretable_name='TEST_PART';TABLE_NAMEINDEX_NAMESTATUSNUM_ROWSORPHANED_-----------------------------------------------------------------------------------------------------------------------TEST_PARTIDX_TEST_PARTVALID10003NO

方法四：coalesce cleanup

#COALESCE#Specifythisclausetomergethecontentsofindexpartitionblockswherepossible#tofreeblocksforreuse.#CLEANUP#SpecifyCLEANUPtoremoveorphanedindexentriesforrecordsthatwerepreviously#droppedortruncatedbyatablepartitionmaintenanceoperation.#Todeterminewhetheranindexpartitioncontainsorphanedindexentries,youcan#querytheORPHANED_ENTRIEScolumnoftheUSER_,DBA_,ALL_PART_INDEXES#datadictionaryviews.RefertoOracleDatabaseReferenceformoreinformationTEST@czhpdb1>alterindexidx_test_partcoalescecleanup;Indexaltered.TEST@czhpdb1>selecttable_name,index_name,status,num_rows,orphaned_entriesfromuser_indexeswheretable_name='TEST_PART';TABLE_NAMEINDEX_NAMESTATUSNUM_ROWSORPHANED_-----------------------------------------------------------------------------------------------------------------------TEST_PARTIDX_TEST_PARTVALID10003NO（5）收集索引统计信息

#手工维护索引之后，索引统计信息可能不准确，建议手动收集索引统计信息TEST@czhpdb1>execdbms_stats.gather_index_stats('TEST','IDX_TEST_PART');PL/SQLproceduresuccessfullycompleted.TEST@czhpdb1>selecttable_name,index_name,status,num_rows,orphaned_entriesfromuser_indexeswheretable_name='TEST_PART';TABLE_NAMEINDEX_NAMESTATUSNUM_ROWSORPHANED_-----------------------------------------------------------------------------------------------------------------------TEST_PARTIDX_TEST_PARTVALID9003NO