Oracle Database 12c Deferred Global Index

Introduction

Before Oracle 12.1.0.1, whenever a table partition was dropped all the global indexes that were created on that table became UNUSABLE unless we specified the clause UPDATE INDEXES in the ALTER TABLE (…) DROP PARTITION sentence. If we didn’t specify the clause UPDATE INDEX then the index had to be rebuilt. Why? Look at the following picture:

In this picture we have a table with 2 columns, 5 rows. And an index created on that table, the index was created on the column “ID” of the table and its index entries include the row address (ROWID). The ROWID is an address to the physical location of a row (which datafile, which block, which position in a block).

Now, think what would happen if we dropped the table partition 2, which includes the values “Argentina” and “Colombia: It would result in something like the following:

We see that the values “Agentina” and “Colombia” don’t exist anymore because they were deleted when the partition was dropped. However, we see on the left side that the Index entries related to the values of the dropped table partition still exist into the index. Now they are orphaned index entries because the rows in the table where they are pointing don’t exist anymore. So in this situation what the index needs is: Maintenance.

For small indexes the performance overhead related to the index maintenance could be acceptable, but when we are using big tables and create global indexes on them, our indexes could also be also big (GBs). Rebuilding an index impacts performance since it uses several IO resources to scan the table and creates B-tree (or bitmap) branch and leaf nodes. The performance problem increases if the table is large, as we said previously. In the other hand, if we use the UPDATE INDEXES clause we get the following benefits:

• The indexes are updated with the base table operation. You are not required to update later or rebuild the indexes.
• The global indexes are more highly available, because they are not marked UNUSABLE. These indexes can be used with a small performance overhead while the index completes its maintenance (all the orphaned entries are fixed).
• You don’t have to determine which indexes are dependent on the table from which we are dropping the partition. All the dependent indexes get maintained automatically without DBA intervention.

There is a trick you can use to drop a table partition without making the global indexes unusable; however, that trick is not always possible. You can read about this trick in my article “The trick of dropping a table partition without impact the Global Index”.

Oracle has fixed this downside of performing the index maintenance immediately after to drop a table partition and in Oracle Database 12.1.0.1 has introduced the feature “Deferred Global Index”, which allows you to drop a table partition, to keep the dependent global indexes available and in USABLE state, and leave the maintenance for later. The DBA can decide when the index maintenance is performed via a job “PMO_DEFERRED_GIDX_MAINT_JOB” that exists by default. Oracle is able to use an index that has orphaned entries to recover rows from a table without compromising the reliability of the rows returned.

To explain how this works, I will drop a table partition in a database version 11.2.0.3 and we will see that when we specify UPDATE INDEXES, the index maintenance is performed immediately. For large indexes this could cause performance degradation in the database.

Dropping a table partition in Oracle Database 11.2.0.3:

Creating a database for testing purposes: 

SQL> create table dgomez.table1 (col1 varchar2(20))
partition by list (col1)
(partition dgomez_table1_p1 VALUES ('guatemala'),
partition dgomez_table1_p2 VALUES ('brasil'),
partition dgomez_table1_p3 VALUES ('colombia'));

Table created.

 Inserting some rows into the table:

SQL> insert into dgomez.table1 values ('guatemala');
SQL> insert into dgomez.table1 values ('brasil');
SQL> insert into dgomez.table1 values ('colombia');
SQL> commit;

Gathering stats so that we can query the right metadata:

SQL> exec dbms_stats.gather_table_stats('DGOMEZ','TABLE1');
PL/SQL procedure successfully completed.

Checking how many rows there are in each table partition. One row was created in one specific partition. The partition “DGOMEZ_TABLE_P1” has the value “guatemala”, the next partition the value “brasil”, and the last partition the value “colombia”.

SQL> select TABLE_NAME, PARTITION_NAME,NUM_ROWS from dba_tab_partitions where TABLE_OWNER='DGOMEZ' and table_name='TABLE1'

TABLE_NAME PARTITION_NAME       NUM_ROWS
---------- -------------------- ----------
TABLE1     DGOMEZ_TABLE1_P1     1
TABLE1     DGOMEZ_TABLE1_P2     1
TABLE1     DGOMEZ_TABLE1_P3     1

Creating a Global Index

SQL> create index dgomez.index1 on dgomez.table1 (col1) global;

Index created.

Looking at the index internals, we can see that the the values were successfully indexed.

Leaf block dump
===============
header address 140176766509668=0x7f7d725fa264
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 3
kdxcofbo 42=0x2a
kdxcofeo 7967=0x1f1f
kdxcoavs 7925
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
row#0[8012] flag: ------, lock: 0, len=20
col 0; len 6; (6): 62 72 61 73 69 6c -- brasil
col 1; len 10; (10): 00 01 2d f8 01 00 05 32 00 00
row#1[7990] flag: ------, lock: 0, len=22
col 0; len 8; (8): 63 6f 6c 6f 6d 62 69 61 -- colombia
col 1; len 10; (10): 00 01 2d f9 01 00 09 32 00 00
row#2[7967] flag: ------, lock: 0, len=23
col 0; len 9; (9): 67 75 61 74 65 6d 61 6c 61 -- guatemala
col 1; len 10; (10): 00 01 2d f7 01 00 01 32 00 00
----- end of leaf block dump -----

Now we will drop a table partition using the UPDATE INDEXES clause and we will review the result:

SQL> alter table dgomez.table1 drop partition dgomez_table1_p1 update indexes;
Table altered.

Looking at the Index internals after dropping a table partition allows us to see that the index maintenance was performed immediately. The index entry “guatemala” was marked with the tag “D”, which means the index entry was “deleted” and it can be reused by another index entry. If you want to know more about what this tag means you can read my presentation “Oracle Indexes: From the Concept to Internals”.

Leaf block dump
===============
header address 140655176049252=0x7fecd5cde264
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 3
kdxcofbo 42=0x2a
kdxcofeo 7967=0x1f1f
kdxcoavs 7925
kdxlespl 0
kdxlende 1
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
row#0[8012] flag: ------, lock: 0, len=20
col 0; len 6; (6): 62 72 61 73 69 6c -- brasil
col 1; len 10; (10): 00 01 2d f8 01 00 05 32 00 00
row#1[7990] flag: ------, lock: 0, len=22
col 0; len 8; (8): 63 6f 6c 6f 6d 62 69 61 -- colombia
col 1; len 10; (10): 00 01 2d f9 01 00 09 32 00 00
row#2[7967] flag: ---D--, lock: 2, len=23
col 0; len 9; (9): 67 75 61 74 65 6d 61 6c 61 -- guatemala
col 1; len 10; (10): 00 01 2d f7 01 00 01 32 00 00
----- end of leaf block dump -----

In this case the index maintenance had as a result only one index entry marked as “D”. But think about large indexes: How long would index maintenance take to fix orphaned indexes in a global index of 600GB? how many session would be impacted with the performance degradation of this operation? We will see how Oracle enhancements have fixed this situation in the next section.

Dropping a table partition in Oracle Database 12.1.0.2

Creating a database for testing purposes:

SQL> create table dgomez.table1 (col1 varchar2(20))
partition by list (col1)
(partition dgomez_table1_p1 VALUES ('guatemala'),
partition dgomez_table1_p2 VALUES ('brasil'),
partition dgomez_table1_p3 VALUES ('colombia'));

Table created.

Inserting some rows into the table: 

SQL> insert into dgomez.table1 values ('guatemala');
SQL> insert into dgomez.table1 values ('brasil');
SQL> insert into dgomez.table1 values ('colombia');
SQL> commit;

Gathering stats so that we can query the right metadata:

SQL> exec dbms_stats.gather_table_stats('DGOMEZ','TABLE1');

PL/SQL procedure successfully completed.

Checking how many rows there are in each table partition:

SQL> select TABLE_NAME, PARTITION_NAME,NUM_ROWS from dba_tab_partitions where TABLE_OWNER='DGOMEZ' and table_name='TABLE1'

TABLE_NAME PARTITION_NAME       NUM_ROWS
---------- -------------------- ----------
TABLE1     DGOMEZ_TABLE1_P1     1
TABLE1     DGOMEZ_TABLE1_P2     1
TABLE1     DGOMEZ_TABLE1_P3     1

Creating a Global Index:

SQL> create index dgomez.index1 on dgomez.table1 (col1) global ;
Index created.

Looking at the index internals. The three values were successfully indexed. 

Leaf block dump
===============
header address 140082935337572=0x7f6799999264
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 3
kdxcofbo 42=0x2a
kdxcofeo 7967=0x1f1f
kdxcoavs 7925
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
row#0[8012] flag: -------, lock: 0, len=20
col 0; len 6; (6): 62 72 61 73 69 6c -- brasil
col 1; len 10; (10): 00 02 11 a2 01 80 08 8e 00 00
row#1[7990] flag: -------, lock: 0, len=22
col 0; len 8; (8): 63 6f 6c 6f 6d 62 69 61 -- colombia
col 1; len 10; (10): 00 02 11 a3 01 80 0c 8e 00 00
row#2[7967] flag: -------, lock: 0, len=23
col 0; len 9; (9): 67 75 61 74 65 6d 61 6c 61 -- guatemala
col 1; len 10; (10): 00 02 11 a1 01 80 04 8e 00 00
----- end of leaf block Logical dump -----

I will enable autotrace on my session to see that the index is indeed used:

SQL> select * from dgomez.table1 where col1='brasil';

COL1
--------------------
brasil

Execution Plan
----------------------------------------------------------
Plan hash value: 1498168486

---------------------------------------------------------------------------
| Id | Operation        | Name   | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
| 0  | SELECT STATEMENT |        | 1    | 7     | 1 (0)      | 00:00:01 |
|* 1 | INDEX RANGE SCAN | INDEX1 | 1    | 7     | 1 (0)      | 00:00:01 |
---------------------------------------------------------------------------

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

1 - access("COL1"='brasil')

Look at the end of the output above, only one filter was used: COL1=’brasil’. It is important to note this because we will talk about it later.

Reviewing if the index has orphaned entries. In Oracle 12.1.0.2 the column ORPHANED_ENTRIES was introduced. This column says if an index has orphaned index entries, the index is marked as a candidate for maintenance on the next execution of the job “PMO_DEFERRED_GIDX_MAINT_JOB”. In this case the index doesn’t have orphaned index entries, as we can see with the following query:

SQL> select index_name, status,orphaned_entries from dba_indexes where owner='DGOMEZ' and index_name='INDEX1';

INDEX_NAME STATUS   ORP
---------- -------- ---
INDEX1     VALID    NO

Now I will proceed to drop a table partition using the UPDATE INDEXES clause:

SQL> alter table dgomez.table1 drop partition dgomez_table1_p1 update indexes;
Table altered.

Immediately after dropping the table partition, we can see that the indexes that depend on the table have orphaned index entries:

SQL> select index_name, status,orphaned_entries from dba_indexes where owner='DGOMEZ' and index_name='INDEX1';

INDEX_NAME STATUS   ORP
---------- -------- ---
INDEX1     VALID    YES

However, the behavior in Oracle 12.1.0.2 is different, because if we look at the index internals, the index entries have been not touched as they were in Oracle 11.2.0.3.

Leaf block dump
===============
header address 140533172339300=0x7fd06dd10264
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 3
kdxcofbo 42=0x2a
kdxcofeo 7967=0x1f1f
kdxcoavs 7925
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
row#0[8012] flag: -------, lock: 0, len=20
col 0; len 6; (6): 62 72 61 73 69 6c -- brasil
col 1; len 10; (10): 00 02 11 a2 01 80 08 8e 00 00
row#1[7990] flag: -------, lock: 0, len=22
col 0; len 8; (8): 63 6f 6c 6f 6d 62 69 61 -- colombia
col 1; len 10; (10): 00 02 11 a3 01 80 0c 8e 00 00
row#2[7967] flag: -------, lock: 0, len=23
col 0; len 9; (9): 67 75 61 74 65 6d 61 6c 61 -- guatemala
col 1; len 10; (10): 00 02 11 a1 01 80 04 8e 00 00
----- end of leaf block Logical dump -----

This means that Index maintenance was not performed immediately. This leads us to the question: Is the index usable if it has orphaned index entries? Yes, it is! I will run another query to confirm it:

SQL> select * from dgomez.table1 where col1='guatemala';

COL1
--------------------
guatemala


Execution Plan
----------------------------------------------------------
Plan hash value: 1498168486

---------------------------------------------------------------------------
| Id | Operation        | Name   | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
| 0  | SELECT STATEMENT |        | 1    | 7     | 1 (0)      | 00:00:01 |
|* 1 | INDEX RANGE SCAN | INDEX1 | 1    | 7     | 1 (0)      | 00:00:01 |
---------------------------------------------------------------------------

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

1 - access("COL1"='guatemala')
filter(TBL$OR$IDX$PART$NUM("DGOMEZ"."TABLE1",0,8,0,"TABLE1".ROWID)=1)

The query was able to use the index “INDEX1” even though the index has orphaned index entries and, more interestingly, the values were returned correctly. This confirms that an index with orphaned entries can be used to recover rows from a table without compromising the reliability of the data returned. But how? This is a good question; you can see at the end of AUTOTRACE that a new filter was used. This filter used the undocumented function called “TBL$OR$IDX$PART$NUM” which in fact is responsible for the magic here. Unfortunately, there is not much documentation on it, but I will provide some insights:

Definition:

TBL$OR$IDX$PART$NUMfunction is used to find which partition a particular row would belong to. This function is undocumented. It has the format TBL$OR$IDX$PART$NUM(PARTITIONED_TABLE_NAME,0,d#,p#,COLUMN_NAME) .

I tried to use the function without knowing how it worked, but it was unsuccessful:

SQL> select TBL$OR$IDX$PART$NUM("DGOMEZ"."TABLE1",0,8,0,'ROWID') from dual;
select TBL$OR$IDX$PART$NUM("DGOMEZ"."TABLE1",0,8,0,'ROWID') from dual
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [evapnum:dim8rowid], [], [], [], [],
[], [], [], [], [], [], []

What I saw from my tests is that the function change its functionality depending on the value of the parameter “d#”. In some cases of “d#”, such a function requires even more values. Unfortunately, which value is for which functionality is not documented.

With some traces I was able to capture the following SQL statements that we used while the ALTER TABLE (…) DROP PARTITON was executed with the clause UPDATE INDEXES:

select count(*) from SYS.INDEX_ORPHANED_ENTRY_V$ ioe

                 where ioe.index_object_id = :1 and ioe.type != 'H' and NOT EXISTS (select * from SYS.INDEX_ORPHANED_ENTRY_V$ ioe2

select text from view$ where rowid=:1

select u.name, o.name, o.namespace, o.type#, decode(bitand(i.property,1024),0,0,1), o.obj# from ind$ i,obj$ o,
user$ u where i.obj#=:1 and o.obj#=i.bo# and o.owner#=u.user#

select u.name, o.name, o.namespace, o.type#, decode(bitand(i.property,1024),0,0,1), o.obj# from ind$ i,obj$ o,
user$ u where i.obj#=:1 and o.obj#=i.bo# and o.owner#=u.user#

select u.name, o.name, o.namespace, o.type#, decode(bitand(i.property,1024),0,0,1), o.obj# from ind$ i,obj$ o,
user$ u where i.obj#=:1 and o.obj#=i.bo# and o.owner#=u.user#

delete from index_orphaned_entry$ where indexobj#=:1 and           tabpartdobj# = :2

delete from index_orphaned_entry$ where indexobj#=:1

delete from superobj$ where subobj# = :1

delete from tab_stats$ where obj#=:1

It seems the tables SYS.INDEX_ORPHANED_ENTRY_V$ and SYS.INDEX_ORPHANED_ENTRY$ are used to track which indexes have orphaned entries. What I was not able to see is how Oracle tracks exactly which index entries are orphaned. Somewhere Oracle has information about which index entries must be marked as “D” when it performs the index maintenance. 

Looking at the tables related to orphaned index entries:

SQL> desc SYS.INDEX_ORPHANED_ENTRY_V$
Name              Null? Type
----------------- -------- ----------------------------
INDEX_OWNER       NOT NULL VARCHAR2(128)
INDEX_NAME        NOT NULL VARCHAR2(128)
INDEX_SUBNAME              VARCHAR2(128)
INDEX_OBJECT_ID   NOT NULL NUMBER
TABLE_OWNER       NOT NULL VARCHAR2(128)
TABLE_NAME        NOT NULL VARCHAR2(128)
TABLE_SUBNAME              VARCHAR2(128)
TABLE_OBJECT_ID   NOT NULL NUMBER


SQL> select * from SYS.INDEX_ORPHANED_ENTRY_V$;

INDEX_OWNE INDEX_NAME INDEX_SUBN INDEX_OBJECT_ID TABLE_OWNE TABLE_NAME TABLE_SUBN TABLE_OBJECT_ID T
---------- ---------- ---------- --------------- ---------- ---------- ---------- --------------- -
DGOMEZ     INDEX1                136728          DGOMEZ     TABLE1                136724          O

SQL> desc sys.index_orphaned_entry$;
Name          Null? Type
------------- -------- --------------------
INDEXOBJ#     NOT NULL NUMBER
TABPARTDOBJ#  NOT NULL NUMBER
HIDDEN                 VARCHAR2(1)

SQL> select * from index_orphaned_entry$;

INDEXOBJ#  TABPARTDOBJ# H
---------- ------------ -
136728     136727       O

I will delete another table partition in order to see if the filter using the function TBL$OR$IDX$PART$NUM changes.

SQL> alter table dgomez.table1 drop partition dgomez_table1_p3 update indexes;
Table altered.

Executing a query with Autotrace:

SQL> select * from dgomez.table1 where col1='colombia';
no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 1498168486

---------------------------------------------------------------------------
| Id | Operation        | Name   | Rows  | Bytes      | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0  | SELECT STATEMENT |        | 1     | 8          | 1 (0)      | 00:00:01 |
|* 1 | INDEX RANGE SCAN | INDEX1 | 1     | 8          | 1 (0)      | 00:00:01 |
---------------------------------------------------------------------------

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

1 - access("COL1"='colombia')
filter(TBL$OR$IDX$PART$NUM("DGOMEZ"."TABLE1",0,8,0,"TABLE1".ROWID)=1)

We see that the filter didn’t change. This is even more interesting. We have confirmed that the filter doesn’t depend on how many table partitions we drop.

Reviewing when the next execution of the job that performs index maintenance will be:

SQL> select job_name, enabled,run_count, to_char(NEXT_RUN_DATE,'mm-dd-yyyy hh24:mi')
 from dba_scheduler_jobs where job_name='PMO_DEFERRED_GIDX_MAINT_JOB'

JOB_NAME                       ENABL RUN_COUNT TO_CHAR(NEXT_RUN
------------------------------ ----- ---------- ----------------
PMO_DEFERRED_GIDX_MAINT_JOB    TRUE  2          03-26-2017 02:00

Reviewing what the job executes:

SQL> select source, PROGRAM_OWNER, PROGRAM_NAME from dba_scheduler_jobs where job_name='PMO_DEFERRED_GIDX_MAINT_JOB'

SOURCE     PROGRAM_OWNER        PROGRAM_NAME
---------- -------------------- ------------------------------
           SYS                  PMO_DEFERRED_GIDX_MAINT

Looking at the procedure that is executed by the job:

SQL>select program_action from dba_SCHEDULER_PROGRAMS where program_name='PMO_DEFERRED_GIDX_MAINT'

PROGRAM_ACTION
--------------------------------------------------------------------------------
dbms_part.cleanup_gidx_internal(noop_okay_in => 1);

Checking the functions included in the package DBMS_PART:

SQL> DESC DBMS_PART
PROCEDURE CLEANUP_GIDX
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SCHEMA_NAME_IN     VARCHAR2 IN DEFAULT
TABLE_NAME_IN      VARCHAR2 IN DEFAULT
PROCEDURE CLEANUP_GIDX_INTERNAL
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SCHEMA_NAME_IN     VARCHAR2   IN DEFAULT
TABLE_NAME_IN      VARCHAR2   IN DEFAULT
ORPHANS_ONLY_IN    NUMBER(38) IN DEFAULT
NOOP_OKAY_IN NUMBER(38) IN DEFAULT
PROCEDURE CLEANUP_ONLINE_OP
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SCHEMA_NAME        VARCHAR2 IN DEFAULT
TABLE_NAME         VARCHAR2 IN DEFAULT
PARTITION_NAME     VARCHAR2 IN DEFAULT

Performing an index maintenance manually:

SQL> exec dbms_part.cleanup_gidx_internal(noop_okay_in => 1);
PL/SQL procedure successfully completed.

Checking out if the index has orphaned entries:

SQL> select index_name, status,orphaned_entries from dba_indexes where owner='DGOMEZ' and index_name='INDEX1'

INDEX_NAME           STATUS   ORP
-------------------- -------- ---
INDEX1               VALID    NO

We can see that after index maintenance, the index internals changed; now we have only 1 index entry. It is interesting to note that the two orphaned index entries were not marked as “D” as in 11.2.0.3, they were in fact deleted from the index.

Leaf block dump
===============
header address 140442250240612=0x7fbb426fe264
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 1
kdxcofbo 38=0x26
kdxcofeo 7967=0x1f1f
kdxcoavs 7974
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
row#0[8012] flag: -------, lock: 0, len=22
col 0; len 8; (8): 63 6f 6c 6f 6d 62 69 61 -- colombia
col 1; len 10; (10): 00 02 11 a3 01 80 0c 8e 00 00
----- end of leaf block Logical dump ---- 

Conclusion