Oracle 12c 新特性 ---异步全局索引维护，用于删除和截断分区

概念

Global index maintenance is decoupled from the DROP and TRUNCATE partition maintenance operation without rendering a global index unusable. Index maintenance is done asynchronously and can be delayed to a later point-in-time.

全局索引维护与DROP和TRUNCATE分区维护操作分离，而不会使全局索引无法使用。索引维护是异步完成的，可以延迟到稍后的时间点。

Delaying the global index maintenance to off-peak times without impacting the index availability makes DROP and TRUNCATE partition and subpartition maintenance operations faster and less resource intensive at the point-in-time of the partition maintenance operation.
在不影响索引可用性的情况下，将全局索引维护延迟到非高峰时间，在分区维护操作的时间点上降低和截断分区和子分区维护操作的速度更快，资源更少。

当与更新索引子句相结合时，DROP分区和TRUNCATE分区命令将导致元数据索引维护。此功能仅用于堆表，不支持对象类型、域索引或由SYS拥有的表。
实际的索引维护是在稍后的时间执行的，由以下之一触发。

SYS.PMO_DEFERRED_GIDX_MAINT_JOB作业预定在每天02:00运行。
SYS.PMO_DEFERRED_GIDX_MAINT_JOB作业手动运行DBMS_SCHEDULER.RUN_JOB存储过程。
运行DBMS_PART.CLEANUP_GIDX存储过程。
运行ALTER INDEX REBUILD [PARTITION]]命令。
运行ALTER INDEX [PARTITION] COALESCE CLEANUP命令。
详细内容可参考官方文档：
http://docs.oracle.com/database/121/VLDBG/GUID-087B87A6-959A-40C6-82AF-36E401FD089B.htm#VLDBG14107

实验

1) 创建带有全局索引的分区表。
[leo@www.cndba.cn ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Thu Aug 17 23:30:39 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> alter pluggable database all open;

Pluggable database altered.

SQL> alter session set container=pdbcndba;

Session altered.

SQL> conn test/test@pdbcndba
Connected.
SQL> DROP TABLE t1 PURGE;

Table dropped.

SQL> CREATE TABLE t1
(id            NUMBER,
 description   VARCHAR2(50),
 created_date  DATE)
PARTITION BY RANGE (created_date)
(PARTITION part_2014 VALUES LESS THAN (TO_DATE('01/01/2015', 'DD/MM/YYYY')) TABLESPACE users,
 PARTITION part_2015 VALUES LESS THAN (TO_DATE('01/01/2016', 'DD/MM/YYYY')) TABLESPACE users); 

Table created.

SQL> ALTER TABLE t1 ADD CONSTRAINT t1_pk PRIMARY KEY (id);

Table altered.

SQL> CREATE INDEX t1_idx ON t1 (created_date);

Index created.

SQL> INSERT /*+ APPEND */ INTO t1
SELECT level,
       'Description for ' || level,
       CASE
         WHEN MOD(level,2) = 0 THEN TO_DATE('01/07/2014', 'DD/MM/YYYY')
         ELSE TO_DATE('01/07/2015', 'DD/MM/YYYY')
       END
FROM   dual
CONNECT BY level <= 10000;

10000 rows created.

SQL> COMMIT;

Commit complete.

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 't1');

PL/SQL procedure successfully completed.
2） 查看索引状态为VALID
SQL> COLUMN table_name FORMAT A20
SQL> COLUMN index_name FORMAT A20
SQL> SElECT table_name,
       index_name,
       status
FROM   user_indexes
ORDER BY 1,2; 

TABLE_NAME	           INDEX_NAME 	  STATUS
-------------------- -------------------- --------
T1		               T1_IDX		  VALID
T1		               T1_PK		  VALID
3）如果我们只是删除或截断了一个分区，那么全局索引将被标记为无效。在12c之前，使用UPDATE INDEXES子句可以作为操作的一部分重新构建，使整个操作更慢。在下面的例子中，我们截断了一个分区并检查了索引的状态。 
--truncate 分区
SQL> ALTER TABLE t1 TRUNCATE PARTITION part_2014 DROP STORAGE UPDATE INDEXES;

Table truncated.
4） 查看索引状态有效，USER_INDEXES视图中的ORPHANED_ENTRIES列显示索引维护还没有完成。
SQL> SElECT table_name,
       index_name,
       status
FROM   user_indexes
ORDER BY 1,2;

TABLE_NAME	             INDEX_NAME 	  STATUS
-------------------- -------------------- --------
T1		                   T1_IDX		  VALID
T1		                   T1_PK		  VALID

SQL> SELECT index_name,
       orphaned_entries
FROM   user_indexes
ORDER BY 1;  2    3    4  

INDEX_NAME	         ORP
-------------------- ---
T1_IDX		         YES
T1_PK		         YES
5） 如果我们手动触发索引维护，就可以看到ORPHANED_ENTRIES列中反映的变化。
SQL> EXEC DBMS_PART.cleanup_gidx(USER, 't1');

PL/SQL procedure successfully completed.

SQL> SELECT index_name,
       orphaned_entries
FROM   user_indexes
ORDER BY 1;  2    3    4  

INDEX_NAME	         ORP
-------------------- ---
T1_IDX		         NO
T1_PK		         NO

 

参考文档

http://docs.oracle.com/database/121/NEWFT/chapter12101.htm#NEWFT198 

http://docs.oracle.com/database/121/VLDBG/GUID-087B87A6-959A-40C6-82AF-36E401FD089B.htm#VLDBG14107

http://docs.oracle.com/database/121/VLDBG/GUID-087B87A6-959A-40C6-82AF-36E401FD089B.htm#VLDBG14107