# 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
如果需要，也可以通过如下四种办法手工维护延迟维护global index。
方法1：DBMS_PART.CLEANUP_GIDX
# 通过调用系统包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@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 a50
SYS@czh29c > col LAST_START_DATE for a40
SYS@czh29c > col NEXT_RUN_DATE for a40
SYS@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 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
# 手工维护索引之后，索引统计信息可能不准确，建议手动收集索引统计信息
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
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
