在12C之前的版本,对分区表进行删除分区或者TRUNCATE分区,合并或者分裂分区,MOVE分区等DDL操作时,分区表上的全局索引会失效,通常要加上UPDATE GLOBAL INDEXES或者ONLINE关键字,可是加上这些关键字之后,本来很快的DDL操作可能就要花费很长的时间,而且还要面临锁的问题。
Oracle 12C推出了分区表全局索引异步维护特性,这个特性有效的解决了这个问题,在对分区表进行上述DDL操作时,既能快速完成操作,也能保证全局索引有效,然后通过调度JOB在固定的时候对全局索引进行维护。
其实在这个新特性实现之前,我们对分区表进行DDL操作,通常也是会选择一个业务相对比较空闲的时间来做,通常是后半夜加班来弄,有了这个新特性,就可以在白天选择一个业务相对空闲的时间来做,然后在夜间业务比较空闲的时候,让ORACLE通过JOB统一来维护全局索引。
下面是对这个特性的一些测试和验证。T_TEST表为本文测试表。
1 |
SQL> select TABLE_NAME,PARTITION_NAME,NUM_ROWS from user_tab_partitions where TABLE_NAME='T_TEST'; |
3 |
TABLE_NAME PARTITION_NAME NUM_ROWS |
4 |
-------------------- -------------------- ---------- |
上面是这张表的数据分布情况,下面为这张表创建全局索引(GLOBAL INDEX)。
1 |
SQL> create index IX_CST_ID_01 on T_TEST01(CST_ID) GLOBAL INDEXING FULL; |
4 |
SQL> select TABLE_NAME,INDEX_NAME,STATUS,NUM_ROWS from user_indexes where TABLE_NAME='T_TEST'; |
6 |
TABLE_NAME INDEX_NAME STATUS NUM_ROWS |
7 |
-------------------- -------------------- -------- ---------- |
8 |
T_TEST IX_CST_ID VALID 23566474 |
GLOBAL INDEXING FULL这种写法也是ORACLE 12C的新特性,叫做分区表局部分区索引,就是只在指定的个别分区上创建索引,其他分区不创建索引,具体可以看我BLOG的上一篇文章。
下面先删掉一个分区,不指定UPDATE INDEXES。
01 |
SQL> alter table T_TEST drop partition p3; |
05 |
SQL> select TABLE_NAME,INDEX_NAME,STATUS,NUM_ROWS from user_indexes where TABLE_NAME='T_TEST'; |
07 |
TABLE_NAME INDEX_NAME STATUS NUM_ROWS |
08 |
-------------------- -------------------- -------- ---------- |
09 |
T_TEST IX_CST_ID UNUSABLE 23566474 |
11 |
SQL> alter index IX_CST_ID rebuild; |
15 |
SQL> select TABLE_NAME,INDEX_NAME,STATUS,NUM_ROWS from user_indexes where TABLE_NAME='T_TEST'; |
17 |
TABLE_NAME INDEX_NAME STATUS NUM_ROWS |
18 |
-------------------- -------------------- -------- ---------- |
19 |
T_TEST IX_CST_ID VALID 13682650 |
上面的实验可以看出,在不指定UPDATE INDEXES的情况下删除分区,全局索引立马失效。这和之前的版本没人什么区别,下面再看一下指定UPDATE INDEXES的情况下,删除分区是什么情况。
1 |
SQL> alter table T_TEST drop partition p2 update indexes; |
5 |
SQL> select TABLE_NAME,INDEX_NAME,STATUS,NUM_ROWS from user_indexes where TABLE_NAME='T_TEST'; |
7 |
TABLE_NAME INDEX_NAME STATUS NUM_ROWS |
8 |
-------------------- -------------------- -------- ---------- |
9 |
T_TEST IX_CST_ID VALID 13682650 |
可能在这里也看不出什么问题,因为指定了UPDATE INDEXES,索引没有失效这很正常,可是这个索引并没有被更新。首先,这个操作很快就完成了,可以通过时间来判断,还有就是索引的记录数没有变化,还有就是查询索引的大小会更直观的看到,索引的大小没变。
这就是这个特性的功能,索引没更新,但是索引还有效。在DBA_INDEXES、USER_INDEXES里的ORPHANED_ENTRIES字段,记录了这个索引是否被标记成待维护状态。
1 |
SQL> select TABLE_NAME,INDEX_NAME,STATUS,NUM_ROWS,ORPHANED_ENTRIES from user_indexes where TABLE_NAME='T_TEST'; |
3 |
TABLE_NAME INDEX_NAME STATUS NUM_ROWS ORPHANED_ENTRIES |
4 |
-------------------- -------------------- -------- ---------- -------------------- |
5 |
T_TEST IX_CST_ID VALID 13682650 YES |
ORPHANED_ENTRIES字段为YES表示这个全局索引需要维护,默认在凌晨两点钟,会执行JOB对ORPHANED_ENTRIES为YES的全局索引进行统一的维护。可以从DBA_SCHEDULER_JOBS里查看到这个JOB的信息。
1 |
SQL> select JOB_NAME,LAST_START_DATE,NEXT_RUN_DATE from DBA_SCHEDULER_JOBS where JOB_NAME='PMO_DEFERRED_GIDX_MAINT_JOB'; |
3 |
JOB_NAME LAST_START_DATE NEXT_RUN_DATE |
4 |
--------------------------- -------------------------------- -------------------------------- |
5 |
PMO_DEFERRED_GIDX_MAINT_JOB 16-MAY-18 02.00.02.514339 AM PRC 17-MAY-18 02.00.00.515395 AM PRC |
这个时间可以根据数据库的空闲时段进行调整,默认是每天的凌晨两点钟执行。如果想要立即对这个索引进行维护,可以通过以下的方法立即对全局索引进行维护。
方法1:
1 |
SQL> exec DBMS_PART.CLEANUP_GIDX('DBDREAM','T_TEST'); |
3 |
PL/SQL procedure successfully completed. |
方法2:
1 |
SQL> exec dbms_scheduler.run_job('PMO_DEFERRED_GIDX_MAINT_JOB'); |
3 |
PL/SQL procedure successfully completed. |
方法3:
1 |
SQL> alter index IX_CST_ID_01 COALESCE CLEANUP; |
方法4:
1 |
SQL> alter index IX_CST_ID_01 rebuild; |
手动触发全局索引维护后,ORPHANED_ENTRIES字段会变成NO,这样在PMO_DEFERRED_GIDX_MAINT_JOB执行的时候,就不会再去维护这个全局索引了。
1 |
SQL> select TABLE_NAME,INDEX_NAME,STATUS,NUM_ROWS,ORPHANED_ENTRIES from user_indexes where TABLE_NAME='T_TEST'; |
3 |
TABLE_NAME INDEX_NAME STATUS NUM_ROWS ORPHANED_ENTRIES |
4 |
-------------------- -------------------- -------- ---------- -------------------- |
5 |
T_TEST IX_CST_ID VALID 13682650 NO |
但是通过上面的方法去维护全局索引的时候,可能索引维护了,但是索引的统计信息并没有被收集,这时候最好是手动收集一下。
2 |
SQL> exec dbms_stats.gather_index_stats('DBDREAM','IX_CST_ID'); |
4 |
PL/SQL procedure successfully completed. |
5 |
SQL> select TABLE_NAME,INDEX_NAME,STATUS,NUM_ROWS,ORPHANED_ENTRIES from user_indexes where TABLE_NAME='T_TEST'; |
7 |
TABLE_NAME INDEX_NAME STATUS NUM_ROWS ORPHANED_ENTRIES |
8 |
-------------------- -------------------- -------- ---------- -------------------- |
9 |
T_TEST IX_CST_ID VALID 6136934 NO |