溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
# 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進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
