Richard Foote's Oracle Blog

12c Asynchronous Global Index Maintenance Part III (Re-Make/Re-Model) August 7, 2013

As I discussed previously in Part I, the space occupied by orphaned row entries associated with asynchronously maintained global indexes is not automatically reclaimed by subsequent DML operations within the index. Hence the need to clean out these orphaned index entries via the various options discussed in Part II.

However, a good question by Jason Bucata asked what about Unique indexes. “If the index entries aren’t marked deleted but are truly still “there” in the structure, does that mean you can’t use this feature if any global indexes are unique” ?

So now I need a Part III to answer this question 🙂

So same demo setup as before but this time with a Unique index on the ID column:

SQL> create table muse (id number, code number, name varchar2(30)) partition by range (id)
(partition muse1 values less than (1000001), partition muse2 values less than (2000001), partition muse3 values less than (maxvalue));

Table created.

SQL> insert into muse select rownum, mod(rownum,100000), 'DAVID BOWIE' from dual connect by level <= 3000000;

3000000 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'MUSE', estimate_percent=>null, method_opt=>'FOR ALL COLUMNS SIZE 1');

PL/SQL procedure successfully completed.

SQL> create unique index muse_id_i on muse(id);

Index created.

Let’s now drop a table partition and confirm we indeed do have orphaned unique index entries:

SQL> alter table muse drop partition muse1 update global indexes;

Table altered.

SQL> select index_name, num_rows, s.blocks, leaf_blocks, status, orphaned_entries
from dba_indexes i, dba_segments s where i.index_name = s.segment_name and table_name='MUSE';

INDEX_NAME       NUM_ROWS     BLOCKS LEAF_BLOCKS STATUS   ORP
-------------- ---------- ---------- ----------- -------- ---
MUSE_ID_I         3000000      11264        8216 VALID    YES

SQL> analyze index muse_id_i validate structure;

Index analyzed.

SQL> select name, blocks, lf_rows, del_lf_rows from index_stats;

NAME                     BLOCKS    LF_ROWS DEL_LF_ROWS
-------------------- ---------- ---------- -----------
MUSE_ID_I                  9216    3000000     1000000

If we take a look at a partial block dump of the first (left-most) index leaf block at this stage:

Block header dump: 0x018076dc
Object id on Block? Y
seg/obj: 0x16c11 csc: 0x00.29239b itc: 2 flg: E typ: 2 – INDEX
brn: 0 bdba: 0x18076d8 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
0x02 0xffff.000.00000000 0x00000000.0000.00 C— 0 scn 0x0000.0029239b
Leaf block dump
===============
header address 360728164=0x15804664
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 1
kdxcosdc 0
kdxconro 404
kdxcofbo 844=0x34c
kdxcofeo 1675=0x68b
kdxcoavs 831
kdxlespl 0
kdxlende 0
kdxlenxt 25196253=0x18076dd
kdxleprv 0=0x0
kdxledsz 10
kdxlebksz 8036
row#0[8021] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 00
col 0; len 2; (2): c1 02
row#1[8006] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 01
col 0; len 2; (2): c1 03
row#2[7991] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 02
col 0; len 2; (2): c1 04
row#3[7976] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 03
col 0; len 2; (2): c1 05
row#4[7961] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 04
col 0; len 2; (2): c1 06
row#5[7946] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 05
col 0; len 2; (2): c1 07
row#6[7931] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 06
col 0; len 2; (2): c1 08
row#7[7916] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 07
col 0; len 2; (2): c1 09
row#8[7901] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 08
col 0; len 2; (2): c1 0a
row#9[7886] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 09
col 0; len 2; (2): c1 0b
row#10[7871] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 0a
col 0; len 2; (2): c1 0c
row#11[7856] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 0b
col 0; len 2; (2): c1 0d
row#12[7841] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 0c
col 0; len 2; (2): c1 0e
row#13[7826] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 0d
col 0; len 2; (2): c1 0f
row#14[7811] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 0e
col 0; len 2; (2): c1 10
row#15[7796] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 0f
col 0; len 2; (2): c1 11

As discussed previously, the index leaf block remains “untouched” after the drop table partition operation and has no index entries actually marked as “deleted”. However, just take a note of the rowid values of the first 10 rows. I’m now going to reinsert new rows with an ID between 1 and 10 that were previously deleted as part of dropping the table partition …

SQL> insert into muse select rownum, 42, 'ZIGGY STARDUST' from dual connect by level <= 10;

10 rows created.

SQL> commit;

Commit complete.

SQL> analyze index muse_id_i validate structure;

Index analyzed.

SQL> select name, blocks, lf_rows, del_lf_rows from index_stats;

NAME                     BLOCKS    LF_ROWS DEL_LF_ROWS
-------------------- ---------- ---------- -----------
MUSE_ID_I                  9216    3000000      999990

We can see that the number of so-called deleted leaf rows is now only 999990 and has decreased by the 10 rows we’ve inserted.

If we take a look now at the first index leaf block again:

Block header dump: 0x018076dc
Object id on Block? Y
seg/obj: 0x16c11 csc: 0x00.29239b itc: 2 flg: E typ: 2 – INDEX
brn: 0 bdba: 0x18076d8 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
0x02 0x0004.012.0000079d 0x014045c7.0122.44 –U- 10 fsc 0x0000.00292503
Leaf block dump
===============
header address 360612452=0x157e8264
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 1
kdxcosdc 0
kdxconro 404
kdxcofbo 844=0x34c
kdxcofeo 1675=0x68b
kdxcoavs 831
kdxlespl 0
kdxlende 0
kdxlenxt 25196253=0x18076dd
kdxleprv 0=0x0
kdxledsz 10
kdxlebksz 8036
row#0[8021] flag: ——-, lock: 2, len=15, data:(10): 00 01 6c 0f 01 80 91 2a 00 86
col 0; len 2; (2): c1 02
row#1[8006] flag: ——-, lock: 2, len=15, data:(10): 00 01 6c 0f 01 80 91 2a 00 87
col 0; len 2; (2): c1 03
row#2[7991] flag: ——-, lock: 2, len=15, data:(10): 00 01 6c 0f 01 80 91 2a 00 88
col 0; len 2; (2): c1 04
row#3[7976] flag: ——-, lock: 2, len=15, data:(10): 00 01 6c 0f 01 80 91 2a 00 89
col 0; len 2; (2): c1 05
row#4[7961] flag: ——-, lock: 2, len=15, data:(10): 00 01 6c 0f 01 80 91 2a 00 8a
col 0; len 2; (2): c1 06
row#5[7946] flag: ——-, lock: 2, len=15, data:(10): 00 01 6c 0f 01 80 91 2a 00 8b
col 0; len 2; (2): c1 07
row#6[7931] flag: ——-, lock: 2, len=15, data:(10): 00 01 6c 0f 01 80 91 2a 00 8c
col 0; len 2; (2): c1 08
row#7[7916] flag: ——-, lock: 2, len=15, data:(10): 00 01 6c 0f 01 80 91 2a 00 8d
col 0; len 2; (2): c1 09
row#8[7901] flag: ——-, lock: 2, len=15, data:(10): 00 01 6c 0f 01 80 91 2a 00 8e
col 0; len 2; (2): c1 0a
row#9[7886] flag: ——-, lock: 2, len=15, data:(10): 00 01 6c 0f 01 80 91 2a 00 8f
col 0; len 2; (2): c1 0b
row#10[7871] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 0a
col 0; len 2; (2): c1 0c
row#11[7856] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 0b
col 0; len 2; (2): c1 0d
row#12[7841] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 0c
col 0; len 2; (2): c1 0e
row#13[7826] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 0d
col 0; len 2; (2): c1 0f
row#14[7811] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 0e
col 0; len 2; (2): c1 10
row#15[7796] flag: ——-, lock: 0, len=15, data:(10): 00 01 6c 0e 01 80 11 30 00 0f
col 0; len 2; (2): c1 11

We notice that the first 10 index entries now have different rowids from the previous block dump.

So this is an exception to the rule. With a Unique index, Oracle will indeed reuse the storage occupied by the orphaned Unique index entry if the same unique value as an orphaned value is subsequently re-inserted. This is not the case with Non-Unique indexes, even if such Non-Unique indexes are used to police either a PK or Unique Key constraint.

So the new valid index entries and any existing orphaned entries can be read and/or ignored as necessary:

SQL> select * from muse where id between 1 and 100;

ID       CODE NAME
---------- ---------- --------------------
1         42 ZIGGY STARDUST
2         42 ZIGGY STARDUST
3         42 ZIGGY STARDUST
4         42 ZIGGY STARDUST
5         42 ZIGGY STARDUST
6         42 ZIGGY STARDUST
7         42 ZIGGY STARDUST
8         42 ZIGGY STARDUST
9         42 ZIGGY STARDUST
10        42 ZIGGY STARDUST

10 rows selected.

Execution Plan
----------------------------------------------------------

Plan hash value: 2515419874

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |           |     1 |    23 |     4   (0)| 00:00:01 |       |       |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| MUSE      |     1 |    23 |     4   (0)| 00:00:01 |     1 |     1 |
|*  2 |   INDEX RANGE SCAN                         | MUSE_ID_I |   100 |       |     3   (0)| 00:00:01 |       |       |
------------------------------------------------------------------------------------------------------------------------

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

2 - access("ID">=1 AND "ID"<=100)

filter(TBL$OR$IDX$PART$NUM("MUSE",0,8,0,"MUSE".ROWID)=1)

Statistics
----------------------------------------------------------

5  recursive calls
0  db block gets
7  consistent gets
0  physical reads
0  redo size
974  bytes sent via SQL*Net to client
544  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
1  sorts (memory)
0  sorts (disk)
10  rows processed

However, if new unique values are inserted into the table but with ID values that didn’t previously exist:

SQL> insert into muse select rownum+3000000, 42, 'ZIGGY STARDUST'
from dual connect by level <= 1000000;

1000000 rows created.

SQL> commit;

Commit complete.

SQL> analyze index muse_id_i validate structure;

Index analyzed.

SQL> select name, blocks, lf_rows, del_lf_rows from index_stats;

NAME                     BLOCKS    LF_ROWS DEL_LF_ROWS
-------------------- ---------- ---------- -----------
MUSE_ID_I                 11264    4000000      999990

We notice that the number of so-called deleted leaf entries remains the same after inserting the 1M new rows.

So in this scenario, the effectively “empty” leaf blocks containing nothing but orphaned unique index entries are not re-cycled and reused by subsequent index block splits as they would have been had they contained nothing but deleted index entries.

So Unique indexes in the unlikely event that such unique values are subsequently reinserted are an exception to the general rule of orphaned global index entries having to be “cleaned out”.