Shulou(Shulou.com)06/01 Report--

1. Official documentation of the new feature (3) use the new feature update indexes clause # update indexes to update all indexes, global or local, but when updating global index with update indexes, no clause can be specified, # update global indexes can only be used for global indexesTEST@czhpdb1 > alter table test_part drop partition p2 update indexes Table altered.# can see that the index has not failed, but the NUM_ROWS has not changed, that is, the index has not been updated in real time 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 500 scheduled tasks with delayed GLOBAL INDEX MAINTAINE can be seen Automatic maintenance 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' starts at 2: 00 am by default 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 View orphaned_entries field Identify whether global index contains expired entries for deferred index maintenance 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) Manual maintenance deferred index maintenance global index

If necessary, you can also maintain deferred maintenance global index manually in four ways.

Method 1:DBMS_PART.CLEANUP_GIDX

# by calling the system package 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

Method 2:dbms_scheduler.run_job

# when using sys to connect to the database, be sure to switch pdb. Only by switching to the correct pdb can you correctly perform scheduled tasks and complete the corresponding maintenance operations 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 # query DBA_SCHEDULER_JOBS View scheduling tasks 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# manually executes scheduling task SYS@ Czh29c > exec dbms_scheduler.run_job ('PMO_DEFERRED_GIDX_MAINT_JOB') PL/SQL procedure successfully completed.# checks global index status, which has been maintained by 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

Method 3: rebuild the index

# you can rebuild. After enabling parallel parallel,rebuild, use alter index no paralle and turn off parallelism 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

Method 4: 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) collect index statistics # after manual maintenance of the index Index statistics may be inaccurate. It is recommended to collect index statistics manually 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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.