Database 12c deferred global index maintenance

articles: 

Many people are terrified of global indexes, one reason being that partition DDLs on the table will either render them unusable, or take forever as they are updated. Deferred global index maintenance solves this, and should be an important driver for the 12c upgrade.

We all know the problem: you drop a partition and your global indexes have to be rebuilt. Unless you use the UPDATE INDEXES clause, in which case a DDL that should be near instantaneous takes hours as the index keys are deleted. Not any more.
Here's a sample script:

create table t1(c1 number,c2 number) partition by range (c2) 
(partition p1 values less than (2),partition p2 values less than (3));
insert into t1 select rownum,1 from dual connect by level < 1000000;
insert into t1 select rownum,2 from dual connect by level < 1000000;
create index i1 on t1(c1) global;
set timing on
alter table t1 drop partition p1 update indexes;
On my Sony laptop, an 11.2.0.3 database runs that DROP PARTITION command in 2.48 seconds. A 12.1.0.1 database ran it in 0.10 seconds. Not bad. But what is actually happening? See this:
orclz>
orclz> set autot on exp
orclz> select * from t1 where c1=1;

        C1         C2
---------- ----------
         1          2

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1428566563

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |      |     1 |    26 |     2  (50)| 00:00:01 |    |          |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| T1   |     1 |    26 |     2  (50)| 00:00:01 |  1 |        1 |
|*  2 |   INDEX RANGE SCAN                         | I1   |     1 |       |     3   (0)| 00:00:01 |    |          |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1"=1)
       filter(TBL$OR$IDX$PART$NUM("T1",0,8,0,"T1".ROWID)=1)

orclz>
See that extra filter? The index is usable, but keys referring to the dropped partition are filtered out. Remove the keys later:
orclz>
orclz> select status,orphaned_entries from user_indexes where index_name='I1';

STATUS   ORP
-------- ---
VALID    YES

orclz> select enabled,run_count from dba_scheduler_jobs where job_name='PMO_DEFERRED_GIDX_MAINT_JOB';

ENABL  RUN_COUNT
----- ----------
TRUE          31

orclz> exec dbms_scheduler.run_job('SYS.PMO_DEFERRED_GIDX_MAINT_JOB')

PL/SQL procedure successfully completed.

Commit complete.
orclz> select status,orphaned_entries from user_indexes where index_name='I1';

STATUS   ORP
-------- ---
VALID    NO

orclz> set autot on exp
orclz> select * from t1 where c1=1;

        C1         C2
---------- ----------
         1          2


Execution Plan
----------------------------------------------------------
Plan hash value: 1428566563

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |      |     1 |    26 |     2  (50)| 00:00:01 |    |          |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| T1   |     1 |    26 |     2  (50)| 00:00:01 |  1 |        1 |
|*  2 |   INDEX RANGE SCAN                         | I1   |     1 |       |     3   (0)| 00:00:01 |    |          |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1"=1)

orclz>
The column DBA_INDEXES.ORPHANED_ENTRIES shows that Oracle is aware that the index may have keys referring to dropped partitions. There is a pre-seeded daily job that tidies them up; by default, it runs every night from 22:00. In the example above, I'm running it manually. Alternatively, you can execute the DBMS_PART.CLEANUP_GIDX procedure. And then the extra filter isn't needed any more.
There is nothing magical about any of this, and my first thought was "why wasn't this possible several releases ago?" Well, it is possible now. And very useful, too.
--
John Watson
Oracle Certified Master DBA
http://skillbuilders.com