12c新特性之全局索引drop、truncate操作的异步处理

在以往的oracle版本中，在对oracle分区表做drop一个分区或则truncate一个分区操作的时候，如果该分区表上有全局索引的话，会导致索引失效，所以在业务高峰期的时候，不会对分区表相应的操作（如果加上了update indexes操作可能还会带来更严重的后果）。从12.1.0.1.0开始，提供了一个叫做“Asynchronous Global Index Maintenance for DROP and TRUNCATE Partition”的新特性，该特性的主要作用是在对分区做drop或则truncate操作时，并不让全局索引立即失效，而是在凌晨通过特定的scheduler（PMO_DEFERRED_GIDX_MAINT_SCHED）来对该全局索引进行处理。
官方文档中的描述：

The partition maintenance operations DROP PARTITION and TRUNCATE PARTITION are optimized by making the index maintenance for metadata only.
Asynchronous global index maintenance for DROP and TRUNCATE is performed by default; however, 
the UPDATE INDEXES clause is still required for backward compatibility.
The following list summarizes the limitations of asynchronous global index maintenance:
Only performed on heap tables
No support for tables with object types
No support for tables with domain indexes
Not performed for the user SYS
Maintenance operations on indexes can be performed with the automatic scheduler job SYS.PMO_DEFERRED_GIDX_MAINT_JOB to clean up all global indexes. 
This job is scheduled to run at 2:00 A.M. on a daily basis by default. 
You can run this job at any time using DBMS_SCHEDULER.RUN_JOB if you want to proactively clean up the indexes. 
You can also modify the job to run with a different schedule based on your specific requirements. 
However, Oracle recommends that you do not drop the job.
You can also force cleanup of an index needing maintenance using one of the following options:
DBMS_PART.CLEANUP_GIDX - This PL/SQL procedure gathers the list of global indexes 
in the system that may require cleanup and runs the operations necessary to restore the indexes to a clean state.
ALTER INDEX REBUILD [PARTITION] – This SQL statement rebuilds the entire index or index partition as is done 
in releases previous to Oracle Database 12c Release 1 (12.1). The resulting index (partition) does not contain any stale entries.
ALTER INDEX [PARTITION] COALESCE CLEANUP – This SQL statement cleans up any orphaned entries in index blocks.

 
以下为测试内容：
1. 创建测试表以及全局索引

SQL> 
SQL> show user      
USER is "HCN"
SQL> 
SQL> 
SQL> create table test
  2  (
  3    id1 number,
  4    id2 number
  5  )
  6  partition by range(id1)
  7  (
  8    partition p1 values less than(100000),
  9    partition p2 values less than(200000),
 10    partition p3 values less than(300000),
 11    partition p4 values less than(400000),
 12    partition p5 values less than(500000),
 13    partition pm values less than(MAXVALUE)
 14  );

Table created.
SQL> SQL> 
SQL> 
SQL> 
SQL> insert /*+ APPEND */ into test
  2  select level, level from dual
  3  connect by level < 600001;
600000 rows created.
SQL> commit;
Commit complete.
SQL> 
SQL> 
SQL> select count(*) from test;
  COUNT(*)
----------
    600000
SQL> 
SQL> 

SQL> 
SQL> create index test_idx on test(id1, id2) parallel 6;
Index created.
SQL> 
SQL> select orphaned_entries 
  2  from user_indexes 
  3  where index_name='TEST_IDX';
ORP
---
NO
SQL>

其中orphaned_entries的描述如下：

ORPHANED_ENTRIES VARCHAR2(3) 
Indicates whether a global index contains stale entries because of deferred index maintenance during DROP/TRUNCATE PARTITION, 
or MODIFY PARTITION INDEXING OFF operations.
Possible values:
YES - The index contains orphaned entries
NO - The index does not contain orphaned entries

代表该索引是否已经进入到了全局索引延迟处理的状态（Yes代表已经是延迟处理状态）
2. 对分区表进行处理

SQL> 
SQL> set timing on
SQL> alter table test truncate partition p1 update indexes;
Table truncated.
Elapsed: 00:00:00.11
SQL> 
SQL> 
SQL> select index_name, status from user_indexes
  2  where index_name='TEST_IDX';
INDEX_NAME                               STATUS
---------------------------------------- --------
TEST_IDX                                 VALID
Elapsed: 00:00:00.00
SQL> 
----索引依然有效
SQL> 
SQL> select orphaned_entries 
  2  from user_indexes 
  3  where index_name='TEST_IDX';
ORP
---
YES
Elapsed: 00:00:00.01
SQL> 
----orphaned_entries状态变为YES

对分区表test进行truncate操作后，全局索引的状态依然为“VALID”，说明索引依然有效，不过此时orphaned_entries状态变为了“YES”，说明该全局索引已经进入了延迟维护阶段。
3. 查看进行延迟维护的scheduler

SQL> 
SQL> select owner,job_name,program_name,schedule_name from dba_scheduler_jobs where job_name='PMO_DEFERRED_GIDX_MAINT_JOB';
OWNER                          JOB_NAME                       PROGRAM_NAME                             SCHEDULE_NAME
------------------------------ ------------------------------ ---------------------------------------- ----------------------------------------
SYS                            PMO_DEFERRED_GIDX_MAINT_JOB    PMO_DEFERRED_GIDX_MAINT                  PMO_DEFERRED_GIDX_MAINT_SCHED
SQL> 
SQL> 
SQL> 
SQL> select owner,schedule_name,repeat_interval from dba_scheduler_schedules where schedule_name='PMO_DEFERRED_GIDX_MAINT_SCHED'
  2  /
OWNER                          SCHEDULE_NAME                            REPEAT_INTERVAL
------------------------------ ---------------------------------------- ----------------------------------------------------------------------------------------------------
SYS                            PMO_DEFERRED_GIDX_MAINT_SCHED            FREQ=DAILY; BYHOUR=02; BYMINUTE=0; BYSECOND=0
SQL> 
SQL> 
SQL> 
SQL> select job_name, comments 
  2  from dba_scheduler_jobs 
  3  where job_name='PMO_DEFERRED_GIDX_MAINT_JOB';
JOB_NAME                       COMMENTS
------------------------------ --------------------------------------------------
PMO_DEFERRED_GIDX_MAINT_JOB    Oracle defined automatic index cleanup for partiti
                               on maintenance operations with deferred global ind
                               ex maintenance

SQL>

SYS.PMO_DEFERRED_GIDX_MAINT_JOB 默认在每天凌晨2：00执行
4. 如何手工强制执行该工作
主要分为以下三个级别

--database level
dbms_part.cleanup_gidx
--schema level
exec dbms_part.cleanup_gidx(<schema_name>);
--table level
dbms_part.cleanup_gidx(<schema_name>, <table_name>);

在database级别手工执行：

SQL> 
SQL> exec dbms_part.cleanup_gidx;
PL/SQL procedure successfully completed.
SQL> 
SQL> select orphaned_entries 
  2  from user_indexes 
  3  where index_name='TEST_IDX';
ORP
---
NO
Elapsed: 00:00:00.00
SQL> 
SQL>

5. 如何关闭该scheduler

begin
  DBMS_SCHEDULER.DISABLE('PMO_DEFERRED_GIDX_MAINT_JOB');
end;