Oracle 12c新特性维护表分区Global Index不失效

1.新特性官方文档说明

（3）使用新特性update indexes子句

# update indexes更新所有索引，global 或者local，而当使用update indexes更新global index时，不能指定子句，# update global indexes只能用于global indexesTEST@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 indexcol job_name for a50col LAST_START_DATE for a40col NEXT_RUN_DATE for a40select 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

（4）手工维护延迟索引维护global index

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

方法1：DBMS_PART.CLEANUP_GIDX

# 通过调用系统包DBMS_PART.CLEANUP_GIDXcol job_name for a50col LAST_START_DATE for a40col NEXT_RUN_DATE for a40select 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@czh29c > 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

方法2：dbms_scheduler.run_job

# 使用sys连接数据库时，一定要注意切换pdb，只有切换到正确的pdb，才能正确的执行调度定时任务完成相应维护操作SYS@czh29c > alter session set container=czhpdb1;Session altered.SYS@czh29c > show pdbs;                                  CON_ID CON_NAME                       OPEN MODE  RESTRICTED---------------------------------------- ------------------------------ ---------- ----------                                       3 CZHPDB1                        READ WRITE NO                                       # 查询DBA_SCHEDULER_JOBS，查看调度任务SYS@czh29c > col job_name for a50SYS@czh29c > col LAST_START_DATE for a40SYS@czh29c > col NEXT_RUN_DATE for a40SYS@czh29c > 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@czh29c > 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

方法3：重建索引

# 可以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 cleanup

# 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 informationTEST@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

（5）收集索引统计信息

# 手工维护索引之后，索引统计信息可能不准确，建议手动收集索引统计信息TEST@czhpdb1 > exec dbms_stats.gather_index_stats('TEST','IDX_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                                          9003 NO