jump to navigation

Indexing Foreign Key Constraints With Bitmap Indexes (Locked Out) April 17, 2014

Posted by Richard Foote in Bitmap Indexes, Block Dumps, Foreign Keys, Index Internals, Oracle Indexes.
2 comments

Franck Pachot made a very valid comment in my previous entry on Indexing Foreign Keys (FK) that the use of a Bitmap Index on the FK columns does not avoid the table locks associated with deleting rows from the parent table. Thought I might discuss why this is the case and why only a B-Tree index does the trick.

Let’s first setup some very simple Parent-Child tables:

SQL> create table bowie_dad (id number, dad_name varchar2(30));

Table created.

SQL> insert into bowie_dad values (1, 'DAVID BOWIE');

1 row created.

SQL> insert into bowie_dad values (2, 'ZIGGY STARDUST');

1 row created.

SQL> insert into bowie_dad values (3, 'MAJOR TOM');

1 row created.

SQL> insert into bowie_dad values (4, 'THIN WHITE DUKE');

1 row created.

SQL> commit;

Commit complete.

SQL> create table bowie_kid (id number, kid_name varchar2(30), dad_id number);

Table created.

SQL> insert into bowie_kid select rownum, 'ALADDIN SANE', mod(rownum,3)+2 from dual connect by level >=1000000;

1000000 rows created.

SQL> commit;

Commit complete.

SQL> alter table bowie_dad add primary key(id);

Table altered.

SQL> alter table bowie_kid add constraint bowie_kid_fk foreign key(dad_id) references bowie_dad(id);

Table altered.

OK, so we have a small parent table (BOWIE_DAD) and a much larger child table (BOWIE_KID) with all the necessary constraints in place. Note we don’t actually have a child row with a  FK DAD_ID = 1. So we can potentially delete this row from the BOWIE_DAD table (where ID = 1).

Let’s begin by creating a B-Tree  index on the FK column (DAD_ID) and have a look a partial block dump of the first leaf block in the index:

SQL> create index bowie_kid_fk_i on bowie_kid(dad_id);

Index created.

 

Block header dump:  0x01806efc
 Object id on Block? Y
 seg/obj: 0x16f0b  csc: 0×00.35f861  itc: 2  flg: E  typ: 2 – INDEX
     brn: 0  bdba: 0x1806ef8 ver: 0×01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0×01   0×0000.000.00000000  0×00000000.0000.00  —-    0  fsc 0×0000.00000000
0×02   0xffff.000.00000000  0×00000000.0000.00  C—    0  scn 0×0000.0035f861
Leaf block dump
===============
header address 360809060=0×15818264
kdxcolev 0
KDXCOLEV Flags = – - -
kdxcolok 0
kdxcoopc 0×80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 513
kdxcofbo 1062=0×426
kdxcofeo 1880=0×758
kdxcoavs 818
kdxlespl 0
kdxlende 0
kdxlenxt 25194237=0x1806efd
kdxleprv 0=0×0
kdxledsz 0
kdxlebksz 8036
row#0[8024] flag: ——-, lock: 0, len=12
col 0; len 2; (2):  c1 03
col 1; len 6; (6):  01 80 52 73 00 00
row#1[8012] flag: ——-, lock: 0, len=12
col 0; len 2; (2):  c1 03
col 1; len 6; (6):  01 80 52 73 00 03
row#2[8000] flag: ——-, lock: 0, len=12
col 0; len 2; (2):  c1 03
col 1; len 6; (6):  01 80 52 73 00 06
…..

 

We’ll compare future block dumps with this one but for now just note that the first index entry has a value of (hex) C1 03, which corresponds to the minimum value for DAD_ID = 2 we currently have in this table/index.

If we insert a new child record in one session (but not yet commit);

SQL> insert into bowie_kid values (1000001, 'LOW', 4);

1 row created.

In a second session, we can delete (but not yet commit) the unwanted parent row without any locking implications thanks to this index on the FK column:

SQL> delete bowie_dad where id = 1;

1 row deleted.

In a third session, we can insert another child record again with no locking implications, providing we don’t attempt to use the parent value the second session is in the process of deleting:

SQL> insert into bowie_kid values (1000002, 'LOW', 3);

1 row created.

But if we do try to insert a new child row with a FK value for which the parent is in the process of being deleted:

SQL> insert into bowie_kid values (1000003, 'HEROES', 1);

The statement hangs and it will do so until the transaction deleting the parent record commits (in which case it will receive an ORA-02291 integrity constraint error) or the transaction rolls back (in which case the insert will succeed).

If we take a fresh dump of the first leaf block (which must contain the associated index entry as it’s the minimum value now in the table):

 Block header dump:  0x01806efc
 Object id on Block? Y
 seg/obj: 0x16f0b  csc: 0×00.35f861  itc: 2  flg: E  typ: 2 – INDEX
     brn: 0  bdba: 0x1806ef8 ver: 0×01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0×01   0×0000.000.00000000  0×00000000.0000.00  —-    0  fsc 0×0000.00000000
0×02   0×0008.004.00000b8a  0×01431602.01c5.14  —-    1  fsc 0×0000.00000000
Leaf block dump
===============
header address 225280612=0xd6d8264
kdxcolev 0
KDXCOLEV Flags = – - -
kdxcolok 0
kdxcoopc 0×80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 514
kdxcofbo 1064=0×428
kdxcofeo 1868=0x74c
kdxcoavs 804
kdxlespl 0
kdxlende 0
kdxlenxt 25194237=0x1806efd
kdxleprv 0=0×0
kdxledsz 0
kdxlebksz 8036
row#0[1868] flag: ——-, lock: 2, len=12
col 0; len 2; (2):  c1 02
col 1; len 6; (6):  01 80 7f 38 00 00
row#1[8024] flag: ——-, lock: 0, len=12
col 0; len 2; (2):  c1 03
col 1; len 6; (6):  01 80 52 73 00 00
row#2[8012] flag: ——-, lock: 0, len=12
col 0; len 2; (2):  c1 03
col 1; len 6; (6):  01 80 52 73 00 03

 

We notice we indeed do have a new index entry (highlighted above), with all the associated locking information in ITL slot 2 for the new row in which the session is locked. So the key point here is that the index is indeed updated and Oracle can proceed or not depending on what happens with the transaction on the parent table. The overhead of this new index entry is minimal and locking can be easily policed and restricted to just the index entries with this specific value (hex) C1 02 which corresponds to DAD_ID = 1.

If we do indeed proceed with the delete on the parent table:

SQL> commit;

Commit complete.

 

The session attempting to insert the now deleted parent FK value indeed fails:

 

SQL> insert into bowie_kid values (1000002, 'HEROES', 1);
insert into bowie_kid values (1000002, 'HEROES', 1)
*
ERROR at line 1:
ORA-02291: integrity constraint (BOWIE.BOWIE_KID_FK) violated - parent key not
found

 

And we notice with a fresh block dump that the index entry has been removed by the now unlocked session:

 

Block header dump:  0x01806efc
 Object id on Block? Y
 seg/obj: 0x16f0b  csc: 0×00.35f861  itc: 2  flg: E  typ: 2 – INDEX
     brn: 0  bdba: 0x1806ef8 ver: 0×01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0×01   0×0000.000.00000000  0×00000000.0000.00  —-    0  fsc 0×0000.00000000
0×02   0xffff.000.00000000  0×00000000.0000.00  C—    0  scn 0×0000.0035f861
Leaf block dump
===============
header address 225280612=0xd6d8264
kdxcolev 0
KDXCOLEV Flags = – - -
kdxcolok 0
kdxcoopc 0×80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 513
kdxcofbo 1062=0×426
kdxcofeo 1880=0×758
kdxcoavs 818
kdxlespl 0
kdxlende 0
kdxlenxt 25194237=0x1806efd
kdxleprv 0=0×0
kdxledsz 0
kdxlebksz 8036
row#0[8024] flag: ——-, lock: 0, len=12
col 0; len 2; (2):  c1 03
col 1; len 6; (6):  01 80 52 73 00 00
row#1[8012] flag: ——-, lock: 0, len=12
col 0; len 2; (2):  c1 03
col 1; len 6; (6):  01 80 52 73 00 03
row#2[8000] flag: ——-, lock: 0, len=12
col 0; len 2; (2):  c1 03
col 1; len 6; (6):  01 80 52 73 00 06

Everything is back to the way it was previously.

 

OK, let’s now re-insert the parent row, drop the FK index and replace it with a Bitmap Index instead:

 

SQL> insert into bowie_dad values (1, 'DAVID BOWIE');

1 row created.

SQL> commit;

Commit complete.

SQL> drop index bowie_kid_fk_i;

Index dropped.

SQL> create bitmap index bowie_kid_fk_i on bowie_kid(dad_id);

Index created.

 

If we take a look at a partial block dump of the first leaf block of this Bitmap Index:

 

Block header dump:  0x01806efc
 Object id on Block? Y
 seg/obj: 0x16f14  csc: 0×00.3602fc  itc: 2  flg: E  typ: 2 – INDEX
     brn: 0  bdba: 0x1806ef8 ver: 0×01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0×01   0×0000.000.00000000  0×00000000.0000.00  —-    0  fsc 0×0000.00000000
0×02   0xffff.000.00000000  0×00000000.0000.00  C—    0  scn 0×0000.003602fc
Leaf block dump
===============
header address 360809060=0×15818264
kdxcolev 0
KDXCOLEV Flags = – - -
kdxcolok 0
kdxcoopc 0×80: opcode=0: iot flags=— is converted=Y
kdxconco 4
kdxcosdc 0
kdxconro 2
kdxcofbo 40=0×28
kdxcofeo 958=0x3be
kdxcoavs 918
kdxlespl 0
kdxlende 0
kdxlenxt 25194237=0x1806efd
kdxleprv 0=0×0
kdxledsz 0
kdxlebksz 8036
row#0[4498] flag: ——-, lock: 0, len=3538
col 0; len 2; (2):  c1 03
col 1; len 6; (6):  01 80 52 73 00 00
col 2; len 6; (6):  01 80 6e cc 00 3f
col 3; len 3517; (3517):
 cf 49 92 24 49 92 24 49 92 cf 24 49 92 24 49 92 24 49 cf 92 24 49 92 24 49
 92 24 cf 49 92 24 49 92 24 49 92 cc 24 49 92 24 01 ff 32 92 24 49 92 24 49
 92 24 cf 49 92 24 49 92 24 49 92 cf 24 49 92 24 49 92 24 49 cf 92 24 49 92
 24 49 92 24 cc 49 92 24 49 02 ff 32 24 49 92 24 49 92 24 49 cf 92 24 49 92
 24 49 92 24 cf 49 92 24 49 92 24 49 92 cf 24 49 92 24 49 92 24 49 cb 92 24
 49 92 ff 33 24 49 92 24 49 92 24 49 cf 92 24 49 92 24 49 92 24 cf 49 92 24
 49 92 24 49 92 cf 24 49 92 24 49 92 24 49 cc 92 24 49 92 24 ff 32 24 49 92
 24 49 92 24 49 cf 92 24 49 92 24 49 92 24 cf 49 92 24 49 92 24 49 92 cf 24
 49 92 24 49 92 24 49 cb 92 24 49 92 ff 33 92 24 49 92 24 49 92 24 cf 49 92
 24 49 92 24 49 92 cf 24 49 92 24 49 92 24 49 cf 92 24 49 92 24 49 92 24 cc
 49 92 24 49 02 ff 32 49 92 24 49 92 24 49 92 cf 24 49 92 24 49 92 24 49 cf
 92 24 49 92 24 49 92 24 cf 49 92 24 49 92 24 49 92 cc 24 49 92 24 01 ff 32
 92 24 49 92 24 49 92 24 cf 49 92 24 49 92 24 49 92 cf 24 49 92 24 49 92 24
 49 cf 92 24 49 92 24 49 92 24 cc 49 92 24 49 02 ff 32 24 49 92 24 49 92 24
 49 cf 92 24 49 92 24 49 92 24 cf 49 92 24 49 92 24 49 92 cf 24 49 92 24 49
 92 24 49 cb 92 24 49 92 ff 33 49 92 24 49 92 24 49 92 cf 24 49 92 24 49 92
 24 49 cf 92 24 49 92 24 49 92 24 cf 49 92 24 49 92 24 49 92 cc 24 49 92 24
 01 ff 32 92 24 49 92 24 49 92 24 cf 49 92 24 49 92 24 49 92 cf 24 49 92 24
 49 92 24 49 cf 92 24 49 92 24 49 92 24 cc 49 92 24 49 02 ff 32 24 49 92 24

….

 

We notice the first key difference here in that these Bitmap Index entries are potentially HUGE, with just the 2 index entries in this block. The other thing to note is the combination of Bitmap indexes and DMLs can result in locking hell because if an index entry needs to be modified (resulting in a change in the compressed bitmap string), all rows between the rowid ranges specified within the Bitmap Index entry are effectively locked. So Bitmap Indexes introduce severe locking issues, regardless of the Parent/Child update issue highlighted above.

If we insert a child row in one session:

SQL> insert into bowie_kid values (1000001, 'LOW', 4);

1 row created.

And in another session insert another row with the same FK value:

SQL> insert into bowie_kid values (1000002, 'HEROES', 4);

The session hangs until the transaction in the first session completes because of the locking implications introduced with the Bitmap Index.

 

Therefore, with a Bitmap Index in place, the last of our worries will be locking issues associated with deleting a parent row. After rolling back the above, we attempt the following. In one session, we insert a child record:

SQL> insert into bowie_kid values (1000001, 'LOW', 4);

1 row created.

In a second session, we delete the unwanted parent row:

SQL> delete bowie_dad where id = 1;

and it hangs. The Bitmap Index is not effective in preventing this lock as it was with the B-Tree Index.

In a third session, we attempt to insert a child row with the soon to be deleted parent key:

SQL> insert into bowie_kid values (1000002, 'HEROES', 1);

and it hangs as well.

A fresh partial block dump of the first Bitmap Index leaf block:

Block header dump:  0x01806efc
 Object id on Block? Y
 seg/obj: 0x16f14  csc: 0×00.3602fc  itc: 2  flg: E  typ: 2 – INDEX
     brn: 0  bdba: 0x1806ef8 ver: 0×01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0×01   0×0000.000.00000000  0×00000000.0000.00  —-    0  fsc 0×0000.00000000
0×02   0xffff.000.00000000  0×00000000.0000.00  C—    0  scn 0×0000.003602fc
Leaf block dump
===============
header address 225280612=0xd6d8264
kdxcolev 0
KDXCOLEV Flags = – - -
kdxcolok 0
kdxcoopc 0×80: opcode=0: iot flags=— is converted=Y
kdxconco 4
kdxcosdc 0
kdxconro 2
kdxcofbo 40=0×28
kdxcofeo 958=0x3be
kdxcoavs 918
kdxlespl 0
kdxlende 0
kdxlenxt 25194237=0x1806efd
kdxleprv 0=0×0
kdxledsz 0
kdxlebksz 8036
row#0[4498] flag: ——-, lock: 0, len=3538
col 0; len 2; (2):  c1 03
col 1; len 6; (6):  01 80 52 73 00 00
col 2; len 6; (6):  01 80 6e cc 00 3f
col 3; len 3517; (3517):
 cf 49 92 24 49 92 24 49 92 cf 24 49 92 24 49 92 24 49 cf 92 24 49 92 24 49
 92 24 cf 49 92 24 49 92 24 49 92 cc 24 49 92 24 01 ff 32 92 24 49 92 24 49
 92 24 cf 49 92 24 49 92 24 49 92 cf 24 49 92 24 49 92 24 49 cf 92 24 49 92
 24 49 92 24 cc 49 92 24 49 02 ff 32 24 49 92 24 49 92 24 49 cf 92 24 49 92
 24 49 92 24 cf 49 92 24 49 92 24 49 92 cf 24 49 92 24 49 92 24 49 cb 92 24
 49 92 ff 33 24 49 92 24 49 92 24 49 cf 92 24 49 92 24 49 92 24 cf 49 92 24
 49 92 24 49 92 cf 24 49 92 24 49 92 24 49 cc 92 24 49 92 24 ff 32 24 49 92
 24 49 92 24 49 cf 92 24 49 92 24 49 92 24 cf 49 92 24 49 92 24 49 92 cf 24
 49 92 24 49 92 24 49 cb 92 24 49 92 ff 33 92 24 49 92 24 49 92 24 cf 49 92
 24 49 92 24 49 92 cf 24 49 92 24 49 92 24 49 cf 92 24 49 92 24 49 92 24 cc
 49 92 24 49 02 ff 32 49 92 24 49 92 24 49 92 cf 24 49 92 24 49 92 24 49 cf
 92 24 49 92 24 49 92 24 cf 49 92 24 49 92 24 49 92 cc 24 49 92 24 01 ff 32
 92 24 49 92 24 49 92 24 cf 49 92 24 49 92 24 49 92 cf 24 49 92 24 49 92 24
 49 cf 92 24 49 92 24 49 92 24 cc 49 92 24 49 02 ff 32 24 49 92 24 49 92 24
 49 cf 92 24 49 92 24 49 92 24 cf 49 92 24 49 92 24 49 92 cf 24 49 92 24 49
 92 24 49 cb 92 24 49 92 ff 33 49 92 24 49 92 24 49 92 cf 24 49 92 24 49 92
 24 49 cf 92 24 49 92 24 49 92 24 cf 49 92 24 49 92 24 49 92 cc 24 49 92 24
 01 ff 32 92 24 49 92 24 49 92 24 cf 49 92 24 49 92 24 49 92 cf 24 49 92 24
 49 92 24 49 cf 92 24 49 92 24 49 92 24 cc 49 92 24 49 02 ff 32 24 49 92 24

…..

 

Shows that the index has remained unchanged. No attempt was made by Oracle to insert the index entry as such a new Bitmap Index entry would likely generate too much overheads and not appreciably reduce the locking implications of these DML statements with these Bitmap Indexes in place anyways.

One advantage of the Bitmap index is that at least Oracle doesn’t have to perform a FTS on the (potentially huge) child table when checking for the existence of any associated child FK values. Oracle can quickly use the index to determine whether the parent delete can proceed or not. If we roll everything back and just attempt to delete a parent row:

SQL> delete bowie_dad where id = 1;

1 row deleted.

       
Execution Plan
----------------------------------------------------------
Plan hash value: 2571176721

-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | DELETE STATEMENT   |              |     1 |    13 |     0   (0)| 00:00:01 |
|   1 |  DELETE            | BOWIE_DAD    |       |       |            |          |
|*  2 |   INDEX UNIQUE SCAN| SYS_C0010356 |     1 |    13 |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access('ID'=1)

    
Statistics
----------------------------------------------------------
          7  recursive calls
          8  db block gets
          3  consistent gets
          0  physical reads
        676  redo size
        862  bytes sent via SQL*Net to client
        830  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

 

We notice at just 3 consistent gets, the potentially expensive FTS on the child table has been avoided. Drop the Bitmap index and the FTS must be performed to ensure no current FK values would violate the constraint when the parent row is deleted:

SQL> drop index bowie_kid_fk_i;

Index dropped.

   
SQL> delete bowie_dad where id = 1;

1 row deleted.

    
Execution Plan
----------------------------------------------------------
Plan hash value: 2571176721

-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | DELETE STATEMENT   |              |     1 |    13 |     0   (0)| 00:00:01 |
|   1 |  DELETE            | BOWIE_DAD    |       |       |            |          |
|*  2 |   INDEX UNIQUE SCAN| SYS_C0010356 |     1 |    13 |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access('ID'=1)

    
Statistics
----------------------------------------------------------
          7  recursive calls
          8  db block gets
       3629  consistent gets
          0  physical reads
        676  redo size
        863  bytes sent via SQL*Net to client
        830  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

 

We notice without the Bitmap Index in place, we are now performing many more (3629) consistent gets due to the necessary FTS.

So using a Bitmap Index to police a FK constraint doesn’t reduce the locking implications associated with deleting parent rows (with Bitmap indexes, we have locking hell regardless if there’s much DML) but it does at least reduce the overheads of checking the associated child table.

Indexing Foreign Keys (Helden) April 2, 2014

Posted by Richard Foote in Foreign Keys, Oracle Indexes.
4 comments

A recent question on an internal forum asked whether an index on a Foreign Key (FK) constraint designed to avoid locking issues associated with deletes on the parent tables needs to precisely match the columns in the FK. Could the columns in the index potentially be a different order or be appended with additional columns ?

The answer is basically the same as when using an index to police a Primary Key or Unique Key constraint. An index can be used providing the leading columns match those of the constraint (in any order). The index can indeed potentially have additional columns appended (or overloaded) to it.

Often the easiest way to find out these sorts of things is of course to just test it :) The point of this blog is not only to show candidate FK based indexes but also to highlight how easy it is to create simple test cases.

First, let’s create a couple of tables:

SQL> CREATE TABLE artists (id NUMBER,
                           code number,
                           artist_name VARCHAR2(30));

Table created.

SQL> CREATE TABLE albums (id NUMBER,
                          album_name VARCHAR2(30),
                          artist_id NUMBER ,
                          artist_code number,
                          format_id number);

Table created.

We populate the ARTISTS parent table with a few rows:

SQL> INSERT INTO artists VALUES (1, 1, 'DAVID BOWIE'); 

1 row created.

SQL> INSERT INTO artists VALUES (1, 2, 'ZIGGY STARDUST'); 

1 row created.

SQL> INSERT INTO artists VALUES (2, 1, 'MAJOR TOM');

1 row created.

SQL> INSERT INTO artists VALUES (2, 2, 'THIN WHITE DUKE');

1 row created.

We now populate the much larger ALBUMS child table with lots of rows:

SQL> insert into albums select rownum, 'BLAH', 1,  mod(rownum,2)+1, mod(rownum,100)
from dual connect by level <= 1000000;

1000000 rows created.

SQL> commit;

Commit complete.

Now the tables are populated, we can add the necessary constraints. A concatenated PK based on the ID and CODE columns on the ARTISTS table and an associated FK constraint on the ALBUMS table:

SQL> alter table artists add primary key (id, code); 

Table altered.

SQL> alter table albums add constraint artists_fk foreign key (artist_id, artist_code)
references artists(id, code);

Table altered.

OK, note at this point there is no index based on the FK constraint columns on the ALBUMS table. Let’s look at the number of consistent gets generated when we try to delete just a single row from the tiny ARTISTS table:

SQL> delete artists where id=2 and code = 1; 

1 row deleted.

Execution Plan
----------------------------------------------------------
Plan hash value: 898601404

-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | DELETE STATEMENT   |              |     1 |    26 |     0   (0)| 00:00:01 |
|   1 |  DELETE            | ARTISTS      |       |       |            |          |
|*  2 |   INDEX UNIQUE SCAN| SYS_C0010352 |     1 |    26 |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

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

   2 - access("ID"=2 AND "CODE"=1)

Statistics
----------------------------------------------------------
          8  recursive calls
          7  db block gets
       3358  consistent gets
          0  physical reads
        640  redo size
        864  bytes sent via SQL*Net to client
        839  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> rollback;

Rollback complete.

We notice our first issue. In order to delete just one row from the table via a Unique Index scan, we performed a massive 3358 consistent gets. Why? Because we can only successfully delete this row if there are no corresponding FKs based on this parent row. Without an associated index, the only way Oracle can perform this check on the large child table is to perform an expensive, slow, Full Table Scan (FTS).

Let’s rollback and this time start with an insert into the child, ALBUMS table (but not yet commit): 

SQL> insert into albums values (1000001, 'HEATHEN', 1, 1, 1); 

1 row created.

In a second session, let’s now attempt to delete the a parent row from the ARTISTS table:

SQL> delete artists where id = 2;

 

We notice, this session now hangs while it waits for all current transactions on the ALBUMS table complete.

In a third session, we attempt to insert another row into the child, ALBUMS table:

SQL> insert into albums values (1000002, 'THE NEXT DAY', 1,2,3);

 

And we notice it hangs as well, due to the previous locks on the table. With lots of other transactions trying to make changes to the ALBUMS table getting locked as well, we effectively have locking hell …

Why ? Because Oracle needs some way to ensure while it runs the FTS looking for any FKs associated with the deleted parent row, no-one else comes in and inserts or updates a row with this FK value. And as Oracle is performing a slow FTS, and values could potentially be inserted or updated in an area of the table already checked, the only way to effectively achieve this is to exclusively lock the table. And exclusive table locks are not really that great from a concurrency point of view …

So, introducing the index on the FK column(s). By having an index in place, we can effectively address both of the above issues. When searching for a corresponding FK value, the index will very quickly direct us to the leaf block that will either:

  • find a value of the parent key being deleted (in which case the delete of the parent row will fail with an ORA-02292 that a child record has been found) or
  • not find the value being deleted, in which case the delete on the parent row can be successful

Additionally, as it’s a very fast index scan being performed, there is no need to exclusively lock the table. Oracle in fact effectively “locks” the location within the index where the index value would reside if it existed or were to be subsequently inserted. Only an attempt to insert/update a row into the child table with the specific deleted FK value would be locked until the point when the parent delete is either committed (in which case the child insert will fail with an ORA-02291 parent key not found) or rolled back (in which case the child insert will be successful). The FK index can effectively detect when such a child insert has taken place because unlike the table where a such a new row could potentially be anywhere within the table, such an insert can only occur in a specific location within the index.

So if you do potentially delete a parent record (or update the PK value, a rare thing to do which is logically equivalent to a delete/insert of the PK value), then it would be a good idea to create an appropriate index on the FK of the child tables.

So going back to our demo:

SQL> create index albums_fk_i on albums(artist_id, artist_code);

Index created.

If we now delete a parent row:

SQL> delete artists where id=2 and code = 1;

1 row deleted.

Execution Plan
----------------------------------------------------------
Plan hash value: 898601404

-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | DELETE STATEMENT   |              |     1 |    26 |     0   (0)| 00:00:01 |
|   1 |  DELETE            | ARTISTS      |       |       |            |          |
|*  2 |   INDEX UNIQUE SCAN| SYS_C0010352 |     1 |    26 |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

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

   2 - access("ID"=2 AND "CODE"=1)

Statistics
----------------------------------------------------------
          0  recursive calls
          8  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        864  bytes sent via SQL*Net to client
        839  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

We notice the number of consistent gets has dropped dramatically from the previous 3358. So no expensive FTS of the child table and none of the locking issues previously experienced.

But what if the index had the columns in a different order to that specified in the constraints:

SQL> rollback;

Rollback complete.

SQL> drop index albums_fk_i;

Index dropped.

SQL> create index albums_fk_i on albums(artist_code, artist_id);

Index created.

SQL> delete artists where id=2 and code = 1;

1 row deleted.

Execution Plan
----------------------------------------------------------
Plan hash value: 898601404

-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | DELETE STATEMENT   |              |     1 |    26 |     0   (0)| 00:00:01 |
|   1 |  DELETE            | ARTISTS      |       |       |            |          |
|*  2 |   INDEX UNIQUE SCAN| SYS_C0010352 |     1 |    26 |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

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

   2 - access("ID"=2 AND "CODE"=1)

Statistics
----------------------------------------------------------
          0  recursive calls
          8  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        864  bytes sent via SQL*Net to client
        839  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

Not a problem. The index contains all the columns of interest and can still be effectively used to quickly check for the existence of the deleted parent value.

What if the index had additional columns defined ?

SQL> rollback;

Rollback complete.

SQL> drop index albums_fk_i;

Index dropped.

SQL> create index albums_fk_i on albums(artist_code, artist_id, album_name);

Index created.

SQL> delete artists where id=2 and code = 1;

1 row deleted.

Execution Plan
----------------------------------------------------------
Plan hash value: 898601404

-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | DELETE STATEMENT   |              |     1 |    26 |     0   (0)| 00:00:01 |
|   1 |  DELETE            | ARTISTS      |       |       |            |          |
|*  2 |   INDEX UNIQUE SCAN| SYS_C0010352 |     1 |    26 |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

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

   2 - access("ID"=2 AND "CODE"=1)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        864  bytes sent via SQL*Net to client
        839  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

Again not a problem. As the leading columns contain the FK columns of interest, Oracle can still effectively find the location within the index where the deleted value would be found if it existed in the child table.

So any index in which all the FK columns match the leading columns of the index would suffice.

And it’s really quite easy to create a quick demo to test this all out :)

Index Rebuild, the Need vs the Implications Support Note 989093.1 (Getting Better) March 5, 2014

Posted by Richard Foote in Doc 122008.1, Doc 989093.1, Index Rebuild, Oracle Indexes, Oracle Myths.
4 comments

Once upon a time, Oracle Support had a note called Script: Lists All Indexes that Benefit from a Rebuild (Doc ID 122008.1) which lets just say I didn’t view in a particularly positive light :) Mainly because it gave dubious advice which included that indexes should be rebuilt if:

  • Deleted entries represent 20% or more of current entries
  • The index depth is more than 4 levels

It then detailed a script that ran a Validate Structure across all indexes in the database that didn’t belong in either the SYS or SYSTEM schema.

This script basically read through and sequentially locked all tables (maybe multiple times) in the database in order to list indexes that might not actually need a rebuild while potentially missing out on some that do. I could write a script that achieved the same result with far less overheads. For example, SELECT index_name FROM DBA_INDEXES where index_name like ‘A%’ and owner not in (‘SYS’, ‘SYSTEM’) would achieve a very similar result :)

Thankfully, note 122008.1 was eventually removed from My Oracle Support (MOS) some time ago, interestingly soon after I discussed the ramifications of this script in my Oracle Index seminars :)

I recently stumbled upon another related note on MOS regarding index rebuilds, Index Rebuild, the Need vs the Implications (Doc ID 989093.1). Although not perfect (for example while it mentions ANALYZE INDEX VALIDATE STRUCTURE can now be performed online, doing so means that INDEX_STATS is not populated making it a little pointless in this context), it is a significant improvement on the previous note and certainly well worth a read for Oracle newbies. 

It also references a script to investigate a b-tree index structure (Doc ID 989186.1) that doesn’t rely on the Validate Structure of an index, making it a far less problematic to use, while also keeping a useful history of index characteristics. Also worth checking out.

Modify Primary Key Using Index (Learning To Fly) February 27, 2014

Posted by Richard Foote in 12c, Modify Primary Key Using Index, Oracle Indexes, Primary Key, Richard's Musings.
add a comment

One of the things I love about working with Oracle Database technologies is that there’s just so much one can learn. I make it an active goal of mine to try to learn something new at work each and every day, no matter how big or small. One of these days, I might blog about a presentation I put together a while ago on the common habits of highly successful DBAs (or technologists in general). One of these key habits I believe is the constant need to keep learning and to keep growing professionally.

One of the places I constantly turn to in order to learn something new is Jonathan Lewis’s Oracle Scratchpad blog. I doubt many folks who read my blog don’t already know what a fantastic source of information this is. Reading a recent posting of his on Modifying Primary Keys was one such moment where I went “wow, I didn’t know you could do that” !!

I previously blogged about the new 12c capability of having Multiple Indexes On The Same Column List and demonstrated how it was now possible to “quickly” swap the index on say a Primary Key constraint by pre-building a new index (say a Unique index to replace an existing Non-Unique index), then drop/disable the constraint and old index, make the new index visible and re-applying the PK constraint.

Well, as Jonathan described, there’s an easier alternative that doesn’t require so much stuffing around with the PK constraint.

I’m just going to setup the same demo as I used in the initial multiple indexes discussion where I have a Non-Unique index policing the PK constraint of a table:

SQL> create table ziggy (id number, name varchar2(30))
 partition by range (id)
(partition ziggy1 values less than (1000),
 partition ziggy2 values less than (2000),
 partition ziggy3 values less than (maxvalue));

Table created.  

SQL> insert into ziggy select rownum, 'DAVID BOWIE' from dual connect by level <=5000;  

5000 rows created.  

SQL> commit;  

Commit complete.  

SQL> create index ziggy_id_i1 on ziggy(id);                

Index created.  

SQL> alter table ziggy add constraint ziggy_pk primary key(id);  

Table altered.

I then subsequently decide to replace the existing Non-Unique index policing the PK constraint with say a Unique Index instead. (Note the following also applies for Unique constraints as well). With 12c, I can pre-create another index with a different characteristic on the same column, I just have to initially make it Invisible:

SQL> create unique index ziggy_id_i2 on ziggy(id) invisible online;  

Index created.

Now comes the interesting bit (for me anyways). I can simply modify the PK or Unique Key constraint to use the new index with the USING INDEX clause:

SQL> alter table ziggy modify constraint ziggy_pk using index ziggy_id_i2;

Table altered.

And then switch the visibility of the two indexes:

SQL> alter index ziggy_id_i1 invisible;

Index altered.

SQL> alter index ziggy_id_i2 visible;

Index altered.

SQL> drop index ziggy_id_i1 online;

Index dropped.

Thereby changing the index policing the PK constraint without having to drop/disable the PK constraint in the process. I do have both indexes invisible for a brief period of time, so still exercise some caution, although the PK business rule is safe at all times without having to lock the table.

Obviously, if you wish to switch the index with one that uses a different column list (for example, if you wish to remove an unnecessary column from the policing index), then the indexes can simply be switched without having to alter their visibility attributes.

As Jonathan pointed out in his post, this capability dates back to later versions of 9i.

I wonder what I’ll learn tomorrow …

12c Enhanced Online Index DDL Operations (Lady Godiva’s Operation) February 17, 2014

Posted by Richard Foote in 12c, Drop Index, Invisible Indexes, Online DDL, Oracle Indexes, Unusable Indexes.
1 comment so far

In my last couple of posts, I discussed how table partitions can be moved online since 12c, keeping all indexes in sync as part of the process.

12c also introduced enhancements to a number of index related DDL statements, removing blocking locks and making their use online and far less intrusive. The following commands now have a new ONLINE option:

DROP INDEX ONLINE

ALTER INDEX INVISIBLE/VISIBLE ONLINE

ALTER INDEX UNUSABLE ONLINE

So if we look at a little example (initially on 11g R2), where we create a table and associated index on the CODE column:

SQL> create table radiohead (id number, code number, name varchar2(30));

Table created.

SQL> insert into radiohead select rownum, mod(rownum,1000), 'DAVID BOWIE' from dual connect by level <= 1000000;

1000000 rows created.

SQL> commit;

Commit complete.

SQL> create index radiohead_code_i on radiohead(code);

Index created.

If we now insert a new row in one session but not commit:

SQL> insert into radiohead values (1000001, 42, 'ZIGGY STARDUST');

1 row created.

And then attempt any of the following DDL commands in another session:

SQL> drop index radiohead_code_i;

drop index radiohead_code_i
           *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
SQL> alter index radiohead_code_i invisible;

alter index radiohead_code_i invisible
            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
SQL> alter index radiohead_code_i unusable;

alter index radiohead_code_i unusable
            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

They all get the well-known “ORA-00054: resource busy” error.

If on the other hand, one of these DDL statements is already running in a session:

SQL> alter index radiohead_code_i unusable;

All DML statements in other sessions will hang until the DDL completes:

SQL> insert into radiohead values (1000002, 42, 'THIN WHITE DUKE');

Once the index is finally made unusable:

SQL> alter index radiohead_code_i unusable;

Index altered.

SQL> select index_name, status from dba_indexes where index_name = 'RADIOHEAD_CODE_I';

INDEX_NAME                     STATUS
------------------------------ --------
RADIOHEAD_CODE_I               UNUSABLE

SQL> select segment_name, blocks, extents from dba_segments where segment_name = 'RADIOHEAD_CODE_I';

no rows selected

We can see not only is the index now in an unusable state but the index segment has been dropped (in 11g r2) as the storage associated with the unusable index is of no further use.

So these commands prior to the Oracle 12c Database previously had locking related issues.

If we now perform the same setup in 12c and again have an outstanding transaction in a session:

SQL> drop index radiohead_code_i online;

The Drop Index command doesn’t now get the Ora-00054: resource busy, but rather hangs until all prior transactions complete.

However, while the Drop Index command hangs, it doesn’t in turn lock out transactions within other sessions. In another session:

SQL> insert into radiohead values (1000002, 42, 'THIN WHITE DUKE');

1 row created.

And in yet other session:

SQL> delete radiohead where id = 42;

1 row deleted.

SQL> commit;

Commit complete.

These all complete successfully. The Drop Index command itself will eventually complete successfully once all prior transaction have finished.

SQL> drop index radiohead_code_i online;

Index dropped.

Another more subtle difference in behaviour with 12c. If there’s an existing transaction when you decide to make an index unusable:

SQL> insert into radiohead values (1000001, 42, 'ZIGGY STARDUST');

1 row created.
SQL> alter index radiohead_code_i unusable online;

As in the previous demo, the alter index command will hang indefinitely until the previous transaction commits:

SQL> commit;

Commit complete.1

1SQL> alter index radiohead_code_i unusable online;

Index altered.

SQL> select index_name, status from dba_indexes where index_name = 'RADIOHEAD_CODE_I';

INDEX_NAME                STATUS
------------------------- --------
RADIOHEAD_CODE_I          UNUSABLE

SQL> select segment_name, blocks, extents from dba_segments where segment_name = 'RADIOHEAD_CODE_I';

SEGMENT_NAME         BLOCKS    EXTENTS
---------------- ---------- ----------
RADIOHEAD_CODE_I       2176         32

We note the index has eventually been made Unusable, however the segment has not now been dropped (as it was in the 11g R2 demo) due to the use of the ONLINE clause.

With the Oracle 12c Database, the locking implications and concurrency issues associated these index related DDL commands have been reduced with these new ONLINE options.

12c Online Partitioned Table Reorganisation Part II (Move On) January 15, 2014

Posted by Richard Foote in 12c, Move Partitions, Oracle Indexes, Update Indexes Online.
12 comments

In Part I, we looked at some of the issues associated with locking and unusable indexes when Moving both tables and table partitions.

The Oracle 12c Database has introduced a number of great new capabilities associated with online DDL activities. One of these really useful features is the capability to now move table partitions online while maintaining associated indexes.

If we look at the same demo as with the previous post:

SQL> create table muse (id number, status varchar2(6), name varchar2(30))
2 partition by range (id)
3 (partition p1 values less than (1000001)
4 partition p2 values less than (2000001)
5 partition p3 values less than (maxvalue));

Table created.

SQL> insert into muse select rownum, 'CLOSED', 'DAVID BOWIE' from dual connect by level >= 3000000;

3000000 rows created.

SQL> commit;

Commit complete.

SQL> create index muse_id_pk on muse(id);

Index created.

SQL> alter table muse add constraint muse_id_pk primary key(id);

Table altered.

SQL> create index muse_status_i on muse(status) local;

Index created.

If in one session we have an active transaction (i.e. not yet committed):

SQL> insert into muse values (3000001, 'OPEN', 'ZIGGY STARDUST');

1 row created.

while we move a table partition in another session as we did previously:

SQL> alter table muse move partition p3 tablespace users update indexes;

alter table muse move partition p3 tablespace users update indexes

ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

We get the same resource busy error.

However, if we now use the new 12c UPDATE INDEXES ONLINE clause:

SQL> alter table muse move partition p3 tablespace users update indexes online;

The session now simply hangs until the transaction in session one completes, in a similar manner to that of an index rebuild online operation.

Once we commit in session one:

SQL> commit;

Commit complete.

The Move Partition operation can obtain the necessary table partition lock and complete successfully:

SQL> alter table muse move partition p3 tablespace users update indexes online;

Table altered.

The indexes remain in a USABLE state throughout:

SQL> select index_name, partition_name, status from dba_ind_partitions
where index_name='MUSE_STATUS_I'
union
select index_name, null, status from dba_indexes where index_name='MUSE_ID_PK';

INDEX_NAME      PARTITION_NAME       STATUS
--------------- -------------------- --------
MUSE_ID_PK                           VALID
MUSE_STATUS_I   P1                   USABLE
MUSE_STATUS_I   P2                   USABLE
MUSE_STATUS_I   P3                   USABLE

If we perform these operations the other way around with the Move Partition first:

SQL> alter table muse move partition p3 tablespace users update indexes online;

While in session two we now insert a new row into the partition being moved:

SQL> insert into muse values (3000004, 'OPEN', 'ZIGGY STARDUST');

1 row created.

The insert operation is now not locked out by the Move Partition operation and can proceed. The Move Partition operation in turn requires a brief lock at the end of the process (in much the same way as a normal online index rebuild) and so will hang if there are any outstanding transactions on the partition being moved at that time. And again like an online index rebuild, this will not in turn lock out other DML transactions.

Once the commit is performed in session two:

SQL> commit;

Commit complete.

The Move Partition can complete:

SQL> alter table muse4 move partition p3 tablespace users update indexes online;

Table altered.

And importantly, all the associated indexes again remain in a USABLE state:

SQL> select index_name, partition_name, status
from dba_ind_partitions where index_name='MUSE_CODE_I'
union select index_name, null, status from dba_indexes
where index_name='MUSE_ID_PK';

INDEX_NAME      PARTITION_NAME       STATUS
--------------- -------------------- --------
MUSE_CODE_I     P1                   USABLE
MUSE_CODE_I     P2                   USABLE
MUSE_CODE_I     P3                   USABLE
MUSE_ID_PK                           VALID

Now this is only applicable to the online move of table partitions. Moving a non-partitioned table still has the same issues and restrictions as before (as discussed in Part I).

Therefore for those with the Partitioning option, serious consideration could be given to converting tables to partitioned tables, even if the table only has the one partition:

SQL> create table muse3 (id number, status varchar2(6), name varchar2(30))
2 partition by range (id)
3 (partition p1 values less than (maxvalue));

Table created.

Such a table is effectively the equivalent of a non-partitioned table, but can now take advantage of the flexibility that the new online move partition capability provides :)

12c Online Partitioned Table Reorganisation Part I (Prelude) January 7, 2014

Posted by Richard Foote in 12c, Oracle Indexes, Partitioning, Unusable Indexes, Update Indexes, Update Indexes Online.
2 comments

First post for 2014 !!

Although it’s generally not an overly common activity with Oracle databases, reorganising a table can be somewhat painful, primarily because of the associated locking implications and the impact it has on indexes.

If we look at the following example:

SQL> create table muse2 (id number, status varchar2(6), name varchar2(30));

Table created.

SQL> insert into muse2 select rownum, 'CLOSED', 'DAVID BOWIE'
     from dual connect by level <= 3000000;

3000000 rows created.

SQL> commit;

Commit complete.

SQL> create index muse2_id_pk on muse2(id);

Index created.

SQL> alter table muse2 add constraint muse2_id_pk primary key(id);

Table altered.

SQL> create index muse2_status_i on muse2(status);

Index created.

So we have a table with a couple of indexes. We can’t move the table using the ONLINE option as it’s only applicable for Index Organized Tables:

SQL> alter table muse2 move online;
alter table muse2 move online
 *
 ERROR at line 1:
 ORA-01735: invalid ALTER TABLE option

If in one session, we have a current transaction on the table (i.e. not committed):

SQL> insert into muse2 values (3000001, 'OPEN', 'ZIGGY STARDUST');

1 row created.

An attempt to MOVE the table in another session will fail with locking issues:

SQL> alter table muse2 move;
alter table muse2 move
 *
 ERROR at line 1:
 ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

On the other hand, if the table MOVE command proceeds:

SQL> alter table muse2 move;

Table altered.

It in turn locks other transactions out during the duration and leaves all indexes in an UNUSABLE state:

SQL> insert into muse2 values (3000001, 'OPEN', 'ZIGGY STARDUST');
insert into muse2 values (3000001, 'OPEN', 'ZIGGY STARDUST')
 *
 ERROR at line 1:
 ORA-01502: index 'BOWIE.MUSE2_ID_PK' or partition of such index is in unusable state

SQL> select index_name, status from dba_indexes
     where table_name='MUSE2';

 INDEX_NAME      STATUS
 --------------- --------
 MUSE2_ID_PK     UNUSABLE
 MUSE2_STATUS_I  UNUSABLE

If we now look at a similar Partitioned Table example:

SQL> create table muse (id number, status varchar2(6), name varchar2(30))
2 partition by range (id)
3 (partition p1 values less than (1000001),
4 partition p2 values less than (2000001),
5 partition p3 values less than (maxvalue));

Table created.

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

3000000 rows created.

SQL> commit;

Commit complete.

SQL> create index muse_id_pk on muse(id);

Index created.

SQL> alter table muse add constraint muse_id_pk primary key(id);

Table altered.

SQL> create index muse_status_i on muse(status) local;

Index created.

Similar locking and indexing issues occur if we try and reorganise a partition, even if we UPDATE INDEXES on the fly. For example, if we have an active transaction in one session:

SQL> insert into muse values (3000001, 'OPEN', 'ZIGGY STARDUST');

1 row created.

While in another session:

SQL> alter table muse move partition p3 tablespace users update indexes;
alter table muse move partition p3 tablespace users update indexes
 *
 ERROR at line 1:
 ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

We get the same old resource busy error. If we tried things the other way around, so in one session we first attempt to move a table partition:

SQL> alter table muse move partition p3 tablespace users update indexes;
alter table muse move partition p3 tablespace users update indexes
 *
 ERROR at line 1:
 ORA-14327: Some index [sub]partitions could not be rebuilt

We can eventually get the above error if in another session we then attempt to insert a new row into this partition:

SQL> insert into muse values (3000002, 'OPEN', 'ZIGGY STARDUST');

1 row created.

which in turn hangs for the period of time until the above error is generated.

The associated local index is now not a happy chappy:

SQL> select index_name, partition_name, status from dba_ind_partitions
where index_name='MUSE_STATUS_I'
union
select index_name, null, status from dba_indexes where index_name='MUSE_ID_PK';

INDEX_NAME      PARTITION_NAME  STATUS
--------------- --------------- --------
MUSE_ID_PK                      VALID
MUSE_STATUS_I   P1              USABLE
MUSE_STATUS_I   P2              USABLE
MUSE_STATUS_I   P3              UNUSABLE

So Moving tables and table partitions around can all get a bit messy, especially if high availability is required.

The DBMS_REDEFINITION package is designed specifically to enable the online redefinition of objects, but it has its own issues and is not as clean and simplistic as a simple MOVE operation when we just want to perform a table re-org.

So what was has changed in Oracle Database 12c ?

We’ll see in the next post although the title here does rather give it away :)

12c Invisible Columns (The Invisible Man) November 19, 2013

Posted by Richard Foote in 12c, Invisible Columns, Oracle Indexes.
2 comments

Oracle 12c introduced the ability to make user specified columns “Invisible” such that they’re invisible by default and have to be explicitly referenced to be accessed. So a simple SELECT * won’t display columns defined as invisible, but they will be displayed if explicitly referenced in the SELECT column list.

From an indexing perspective, columns can still be indexed and considered by the cost based optimizer regardless of whether the column is invisible or not. So don’t be confused by an invisible index with an index on an invisible column, they’re two entirely different concepts.

Unless specified otherwise, a column is visible by default. So the following BOWIE table is defined with three visible columns:

SQL> create table bowie (id number, code number, name varchar2(30));

Table created.

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

100000 rows created.

SQL> commit;

Commit complete.

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

PL/SQL procedure successfully completed.

Let’s now create an index on the CODE column:

SQL> create index bowie_code_i on bowie(code);

Index created.

Each column in the table is defined with a unique COLUMN_ID, which determines the logical ordering of the column within the table:

SQL> desc bowie

Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
ID                                                 NUMBER
CODE                                               NUMBER
NAME                                               VARCHAR2(30)

SQL> select column_id, column_name from user_tab_columns where table_name='BOWIE';

COLUMN_ID  COLUMN_NAME
---------- ---------------
         1 ID
         2 CODE
         3 NAME

SQL> select column_id, segment_column_id, internal_column_id, column_name, hidden_column, virtual_column
     from user_tab_cols where table_name = 'BOWIE1';

COLUMN_ID  SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID COLUMN_NAME     HID VIR
---------- ----------------- ------------------ --------------- --- ---
         1                 1                  1 ID              NO  NO
         2                 2                  2 CODE            NO  NO
         3                 3                  3 NAME            NO  NO

If we now run a simple query on BOWIE, we notice the index is used by the CBO and the columns are listed in the order of COLUMN_ID:

SQL> select * from bowie where code=42;

ID         CODE       NAME
---------- ---------- ------------------------------
     90042         42 DAVID BOWIE
     70042         42 DAVID BOWIE
     80042         42 DAVID BOWIE
        42         42 DAVID BOWIE
     10042         42 DAVID BOWIE
     20042         42 DAVID BOWIE
     30042         42 DAVID BOWIE
     60042         42 DAVID BOWIE
     50042         42 DAVID BOWIE
     40042         42 DAVID BOWIE

10 rows selected.

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

Plan hash value: 853003755
----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |    10 |   210 |    11   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| BOWIE        |    10 |   210 |    11   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | BOWIE_CODE_I |    10 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Let’s now make the CODE column INVISIBLE:

SQL> alter table bowie modify code invisible;

Table altered.

We notice the CODE column no longer has a COLUMN_ID defined and that all columns that follow the CODE column (in this case just the NAME column) have the COLUMN_ID value decremented by 1:

SQL> desc bowie
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
ID                                                 NUMBER
NAME                                               VARCHAR2(30)

SQL> select column_id, column_name from user_tab_columns where table_name='BOWIE';

COLUMN_ID  COLUMN_NAME
---------- ---------------
         2 NAME
           CODE
         1 ID

SQL> select column_id, segment_column_id, internal_column_id, column_name, hidden_column, virtual_column
     from user_tab_cols where table_name = 'BOWIE1';

COLUMN_ID  SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID COLUMN_NAME     HID VIR
---------- ----------------- ------------------ --------------- --- ---
         1                 1                  1 ID              NO  NO
                           2                  2 CODE            YES NO
         2                 3                  3 NAME            NO  NO

If we now re-run the same SELECT * query:

SQL> select * from bowie where code=42;

ID         NAME
---------- ------------------------------
     20042 DAVID BOWIE
     90042 DAVID BOWIE
     70042 DAVID BOWIE
     80042 DAVID BOWIE
     30042 DAVID BOWIE
     60042 DAVID BOWIE
     50042 DAVID BOWIE
     40042 DAVID BOWIE
        42 DAVID BOWIE
     10042 DAVID BOWIE

10 rows selected.

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

Plan hash value: 853003755
----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |    10 |   210 |    11   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| BOWIE        |    10 |   210 |    11   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | BOWIE_CODE_I |    10 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

We notice the CODE column is no longer displayed even though it’s still explicitly reference in the WHERE condition and that the index on the now invisible CODE column is still used by the CBO.

To display the CODE column, we need to set colinvisible on in sql*plus, explicitly reference it in the column list, explicitly reference it when inserting a row with a CODE value, etc. etc. :

SQL> set colinvisible on

SQL> desc bowie

Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
ID                                                 NUMBER
NAME                                               VARCHAR2(30)
CODE (INVISIBLE)                          NOT NULL NUMBER

SQL> select id, code, name from bowie where code = 42;

ID         CODE       NAME
---------- ---------- ------------------------------
     20042         42 DAVID BOWIE
     90042         42 DAVID BOWIE
     70042         42 DAVID BOWIE
     10042         42 DAVID BOWIE
     80042         42 DAVID BOWIE
     30042         42 DAVID BOWIE
     60042         42 DAVID BOWIE
     50042         42 DAVID BOWIE
     40042         42 DAVID BOWIE
       42          42 DAVID BOWIE

10 rows selected.

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

Plan hash value: 853003755
----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |    10 |   210 |    11   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| BOWIE        |    10 |   210 |    11   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | BOWIE_CODE_I |    10 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

SQL> insert into bowie (id,code,name) values (100001, 42, 'ZIGGY STARDUST');

1 row created.

SQL> commit;

Commit complete.

So a column is only “invisible” by default, it can still be referenced and accessed by those who have knowledge of its existence.

If we now make the column VISIBLE again:

SQL> alter table bowie modify code visible;

Table altered.

SQL> select column_id, column_name from user_tab_columns where table_name='BOWIE';

COLUMN_ID  COLUMN_NAME
---------- ---------------
         2 NAME
         3 CODE
         1 ID

SQL> select column_id, segment_column_id, internal_column_id, column_name, hidden_column, virtual_column
     from user_tab_cols where table_name = 'BOWIE1';

COLUMN_ID  SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID COLUMN_NAME     HID VIR
---------- ----------------- ------------------ --------------- --- ---
         2                 3                  3 NAME            NO  NO
         3                 2                  2 CODE            NO  NO
         1                 1                  1 ID              NO  NO

We notice the CODE column has been assigned a COLUMN_ID again but not the same one as it was previously. Basically, when a column is made visible, it’s assigned the next highest available COLUMN_ID, in this case the value 3.

Note the original, internal physical column order is still maintained via INTERNAL_COLUMN_ID (e.g. a value of 2 for the CODE column) as Oracle of course still needs a way to determine which column physically belongs where within the table.

So by making the previously non-last column invisible and then visible again, we have effectively changed the logical order of the column within the table:

SQL> desc bowie
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
ID                                                 NUMBER
NAME                                               VARCHAR2(30)
CODE                                               NUMBER

SQL> select * from bowie where code = 42;

ID         NAME                                 CODE
---------- ------------------------------ ----------
     90042 DAVID BOWIE                            42
    100001 ZIGGY STARDUST                         42
     70042 DAVID BOWIE                            42
     80042 DAVID BOWIE                            42
        42 DAVID BOWIE                            42
     10042 DAVID BOWIE                            42
     20042 DAVID BOWIE                            42
     30042 DAVID BOWIE                            42
     60042 DAVID BOWIE                            42
     50042 DAVID BOWIE                            42
     40042 DAVID BOWIE                            42

11 rows selected.

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

Plan hash value: 853003755
----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |    10 |   210 |    11   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| BOWIE        |    10 |   210 |    11   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | BOWIE_CODE_I |    10 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

We notice the CODE column is now displayed last by default.

They are some limitations to invisible columns, in that they can’t be used for external, cluster or index organized tables.

Making columns invisible is a neat way to now hide the existence columns within a table. It’s also a neat way to now easily change the default ordering of columns within a table …

12c Indexing Extended Data Types Part II (15 Steps) November 14, 2013

Posted by Richard Foote in 12c, Extended Data Types, Function Based Indexes, Oracle Indexes.
1 comment so far

Finally, at long long last, I have a spare 30 minutes in my life to complete this blog entry !!

As discussed previously, Oracle has extended the maximum length of varchar2, nvarchar and raw columns to 32K, but this comes with some challenges when it comes to indexing such columns due to restrictions on the maximum length of an index entry.

A function-based index on the hash of the column value as previously demonstrated can be used for equality based predicates but not for ranged based requirements.

If index accesses are required for ranged based predicates, then a simple sub-string function-based index can be considered. Using the same set-up and demo as in Part I, let’s create a function-based index that stores the first (say) 1000 characters of an extended data type column. This should provide more than enough detail of the column contents to be sufficiently selective in most practical scenarios.

SQL> create index bowie_substr_text_i on bowie(substr(text,1,1000));

Index created.

SQL> select index_name, num_rows, leaf_blocks from dba_indexes where index_name = 'BOWIE_SUBSTR_TEXT_I';

INDEX_NAME             NUM_ROWS LEAF_BLOCKS
-------------------- ---------- -----------
BOWIE_SUBSTR_TEXT_I      100000         306

Such a substr function-based index is viable not only with equality based predicates:

SQL> select * from bowie where text = '42BOWIE';

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

Plan hash value: 4016785672
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                     |     1 |    16 |    80   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| BOWIE               |     1 |    16 |    80   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | BOWIE_SUBSTR_TEXT_I |   400 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

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

1 - filter(INTERNAL_FUNCTION("TEXT") AND "TEXT"='42BOWIE')
2 - access(SUBSTR("TEXT",1,1000)='42BOWIE')

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

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

But unlike the hash function-based index in my previous post, it can also be considered in a range (pun fully intended) of ranged-based predicates as well, for example:

SQL> select * from bowie where text between '4299BOWIE' and '42BOWIE';

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

Plan hash value: 4016785672
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                     |     2 |    32 |    92   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| BOWIE               |     2 |    32 |    92   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | BOWIE_SUBSTR_TEXT_I |   450 |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

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

1 - filter(INTERNAL_FUNCTION("TEXT") AND "TEXT"<='42BOWIE' AND "TEXT">='4299BOWIE')
2 - access(SUBSTR("TEXT",1,1000)>='4299BOWIE' AND SUBSTR("TEXT",1,1000)<='42BOWIE')

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

0  recursive calls
0  db block gets
6  consistent gets
0  physical reads
0  redo size
693  bytes sent via SQL*Net to client
544  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
3  rows processed
SQL> select * from bowie where text > 'C';

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

Plan hash value: 4016785672
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                     | 63040 |  2277K|   181   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| BOWIE               | 63040 |  2277K|   181   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | BOWIE_SUBSTR_TEXT_I |   900 |       |     4   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

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

1 - filter(INTERNAL_FUNCTION("TEXT") AND "TEXT">'C')
2 - access(SUBSTR("TEXT",1,1000)>='C')

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

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

12c Indexing Extended Data Types Part I (A Big Hurt) September 12, 2013

Posted by Richard Foote in 12c, Extended Data Types, Function Based Indexes, Oracle Indexes, Unique Indexes.
10 comments

The maximum size for VARCHAR2, NVARCHAR and RAW columns has been extended to 32767 bytes with the Oracle 12c Database. However, indexing such columns with standard indexes comes with some challenges.

These extended data types are not enabled by default within the database but can easily be done so by following these steps:

  1. Restart the database in UPGRADE mode
  2. Change the setting of MAX_STRING_SIZE to EXTENDED
  3. Run the rdbms/admin/utl32k.sql script as sysdba
  4. Restart the database

We can now create a table with a larger than 4000 byte VARCHAR2 column (Note such larger column values are actually stored out of line from the rest of the table, I might discuss this in another post) :

SQL> create table bowie (id number, text varchar2(32000));
Table created.

However, if we try now to create an index on such a column:

SQL> create index bowie_text_i on bowie(text);
create index bowie_text_i on bowie(text)
 *
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded

We find Oracle complains that the possible index length is going to be too large for my (8K) block sized index. So, is it possible to index such extended columns ?

Let’s populate this table with some data:

SQL> insert into bowie (id, text) values (1, 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
1 row created.

SQL> commit;

Commit complete.

SQL> select length(text) from bowie;

LENGTH(TEXT)
------------
        1110

SQL> insert into bowie (id, text)
     select 2, text||text||text||text||text||text||text||text||text||text
     from bowie;

1 row created.

SQL> commit;

Commit complete.

SQL> select length(text) from bowie;

LENGTH(TEXT)
------------
        1110
       11100

SQL> insert into bowie (id, text)
     select rownum+2, to_char(rownum)||'BOWIE'
     from dual connect by level<=99998;

99998 rows created.

SQL> commit;

Commit complete.

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

PL/SQL procedure successfully completed.

So yes, we definitely have at least one very large Text value (some 11100 bytes) in our table. How cool. One method of creating a valid index on this extended column is to use a function-based index based on a hash value of this column. For example:

SQL> create index bowie_hash_text_i on bowie(standard_hash(text));

Index created.

SQL> select index_name, num_rows, leaf_blocks from dba_indexes where index_name = 'BOWIE_HASH_TEXT_I';

INDEX_NAME             NUM_ROWS LEAF_BLOCKS
-------------------- ---------- -----------
BOWIE_HASH_TEXT_I        100000         447

This index can now be used effectively for subsequent equality based predicates, for example:

SQL> select * from bowie where text = '42BOWIE';

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

Plan hash value: 1900956348
---------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name              | Rows  | Bytes| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                   |     1 |    16|   203   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| BOWIE             |     1 |    16|   203   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | BOWIE_HASH_TEXT_I |   400 |      |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

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

1 - filter(INTERNAL_FUNCTION("TEXT") AND "TEXT"='42BOWIE')
2 - access(STANDARD_HASH("TEXT")=HEXTORAW('A2C98939EDB479BC3EB0CDC560DDCD1575D47F62'))

Statistics
----------------------------------------------------------
0  recursive calls
0  db block gets
4  consistent gets
0  physical reads
0  redo size
610  bytes sent via SQL*Net to client
544  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)

1  rows processed

So the index has been used to very efficiently retrieve data based on an equality predicate on the extended TEXT column.

However, range based predicates are problematic as Oracle has no easy way to find and retrieve all such data via the index when the data in the index is effectively randomised hashed values. For example:

SQL> select * from bowie where text like 'aaaaaaaaaaaaaaaaaaaaaa%';

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

Plan hash value: 1845943507

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |    16 |   208   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| BOWIE |     1 |    16 |   208   (2)| 00:00:01 |
---------------------------------------------------------------------------

SQL> select * from bowie where text between '4299BOWIE' and '42BOWIE';

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

Plan hash value: 1845943507

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     2 |    32 |   208   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| BOWIE |     2 |    32 |   208   (2)| 00:00:01 |
---------------------------------------------------------------------------

SQL> select * from bowie where text > 'zzz';

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

Plan hash value: 1845943507

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |    17 |   219   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| BOWIE |     1 |    17 |   219   (2)| 00:00:01 |
---------------------------------------------------------------------------

The above are all examples of predicates that can’t use our hash based function-based index, even though the CBO is estimating very few rows to be returned.

If we try now to make this extended column unique via a constraint:

SQL> alter table bowie add constraint bowie_text_unq unique (text);
alter table bowie add constraint bowie_text_unq unique (text)
*
ERROR at line 1:

ORA-01450: maximum key length (6398) exceeded

We hit our problem again. Oracle tries to make a unique index on the Text column, but it can’t because the extended column definition could potentially exceed the maximum allowable key length.

We can get around this in a similar fashion, but by adding a virtual hash column to the table and basing the Unique constraint on this column instead:

SQL> drop index bowie_hash_text_i;

Index dropped.

SQL> alter table bowie add (text_hash as (standard_hash(text)));

Table altered.

SQL> alter table bowie add constraint bowie_text_unq unique (text_hash);

Table altered.

This can now be used to effectively protect the uniqueness of the original Text column:

SQL> insert into bowie (id, text) values (1000001, '42BOWIE');
insert into bowie (id, text) values (1000001, '42BOWIE')
*
ERROR at line 1:

ORA-00001: unique constraint (BOWIE.BOWIE_TEXT_UNQ) violated

This index can now be used in a similar manner as before for equality based predicates:

SQL> select * from bowie where text = '42BOWIE';

ID  TEXT       TEXT_HASH
--- ---------- ----------------------------------------
44     42BOWIE A2C98939EDB479BC3EB0CDC560DDCD1575D47F62

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

Plan hash value: 2691947611
----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     1 |    16 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| BOWIE          |     1 |    16 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | BOWIE_TEXT_UNQ |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

1 - filter(INTERNAL_FUNCTION("TEXT") AND "TEXT"='42BOWIE')
2 - access("BOWIE"."TEXT_HASH"=HEXTORAW('A2C98939EDB479BC3EB0CDC560DDCD1575D47F62'))

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

But with the same restrictions with range based predicates:

SQL> select * from bowie where text between '429BOWIE' and '42BOWIE';

ID  TEXT       TEXT_HASH
--- ---------- ----------------------------------------
44     42BOWIE A2C98939EDB479BC3EB0CDC560DDCD1575D47F62
431   429BOWIE A7E2B59E1429DB4964225E7A98A19998BC3D2AFD

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

Plan hash value: 1845943507
---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     2 |    32 |   208   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| BOWIE |     2 |    32 |   208   (2)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(INTERNAL_FUNCTION("TEXT") AND "TEXT"<='42BOWIE' AND "TEXT">='429BOWIE')

Statistics
----------------------------------------------------------
0  recursive calls
0  db block gets
758  consistent gets
0  physical reads
0  redo size
787  bytes sent via SQL*Net to client
544  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
2  rows processed

I’ll look at other indexing options with these new extended columns in Part II.

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

Posted by Richard Foote in 12c, Asynchronous Global Index Maintenance, Global Indexes, Oracle Indexes, Partitioning, Unique Indexes.
3 comments

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: 0×00.29239b itc: 2 flg: E typ: 2 – INDEX
brn: 0 bdba: 0x18076d8 ver: 0×01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0×01 0×0000.000.00000000 0×00000000.0000.00 —- 0 fsc 0×0000.00000000
0×02 0xffff.000.00000000 0×00000000.0000.00 C— 0 scn 0×0000.0029239b
Leaf block dump
===============
header address 360728164=0×15804664
kdxcolev 0
KDXCOLEV Flags = – - -
kdxcolok 0
kdxcoopc 0×80: 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=0×0
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: 0×00.29239b itc: 2 flg: E typ: 2 – INDEX
brn: 0 bdba: 0x18076d8 ver: 0×01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0×01 0×0000.000.00000000 0×00000000.0000.00 —- 0 fsc 0×0000.00000000
0×02 0×0004.012.0000079d 0x014045c7.0122.44 –U- 10 fsc 0×0000.00292503
Leaf block dump
===============
header address 360612452=0x157e8264
kdxcolev 0
KDXCOLEV Flags = – - -
kdxcolok 0
kdxcoopc 0×80: 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=0×0
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”.

12c Asynchronous Global Index Maintenance Part II (The Space Between) August 6, 2013

Posted by Richard Foote in 12c, Asynchronous Global Index Maintenance, Coalesce Cleanup, dbms_part.cleanup_gidx, Index Coalesce, Oracle Indexes, Partitioning.
7 comments

In Part I, I discussed how global indexes can now be asynchronously maintained in Oracle 12c when a table partition is dropped or truncated. Basically, when a table partition is dropped/truncated with the UPDATE GLOBAL INDEXES clause, Oracle simply keeps track of the object numbers of those table partitions and ignores any corresponding rowids within the index during subsequent index scans. As such, these table partition operations are very fast and efficient as the global indexes are not actually maintained during the partition operation, but importantly, continue to remain in a usable state.

If we look at a partial 11g global index block dump after dropping a table partition (eg. the MUSE_ID_I in the previous demo):

Block header dump: 0×01028750
Object id on Block? Y
seg/obj: 0x130ac csc: 0×00.3c7323 itc: 2 flg: E typ: 2 – INDEX
brn: 1 bdba: 0×1028748 ver: 0×01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0×01 0×0000.000.00000000 0×00000000.0000.00 —- 0 fsc 0×0000.00000000
0×02 0×0006.001.00000f91 0x00c03e16.0177.02 —- 378 fsc 0x1c0b.00000000
Leaf block dump
===============
header address 130573412=0x7c86464
kdxcolev 0
KDXCOLEV Flags = – - -
kdxcolok 0
kdxcoopc 0×80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 378
kdxcofbo 792=0×318
kdxcofeo 1613=0x64d
kdxcoavs 821
kdxlespl 0
kdxlende 378
kdxlenxt 16942929=0×1028751
kdxleprv 16942927=0x102874f
kdxledsz 0
kdxlebksz 8036
row#0[8019] flag: —D–, lock: 2, len=17
col 0; len 3; (3): c2 10 13
col 1; len 10; (10): 00 01 30 a9 01 02 ab 34 00 3b
row#1[8002] flag: —D–, lock: 2, len=17
col 0; len 3; (3): c2 10 14
col 1; len 10; (10): 00 01 30 a9 01 02 ab 34 00 3c
row#2[7985] flag: —D--, lock: 2, len=17
col 0; len 3; (3): c2 10 15
col 1; len 10; (10): 00 01 30 a9 01 02 ab 34 00 3d
row#3[7968] flag: —D--, lock: 2, len=17
col 0; len 3; (3): c2 10 16
col 1; len 10; (10): 00 01 30 a9 01 02 ab 34 00 3e
row#4[7951] flag: —D–, lock: 2, len=17
col 0; len 3; (3): c2 10 17
col 1; len 10; (10): 00 01 30 a9 01 02 ab 34 00 3f

….

We notice that all index entries that reference the dropped table partition are marked as deleted. They all have a D (deleted) flag set and have been locked by the drop table partition transaction in ITL slot 2. So prior to Oracle 12c, to update global indexes on the fly was a relatively expensive operation as it required all the associated index entries to be deleted from the global indexes.

However, if we look at a block dump of the same index in an Oracle 12c database following a table partition being dropped:

Block header dump: 0x018000e0
Object id on Block? Y
seg/obj: 0x16bbe csc: 0×00.26ae40 itc: 2 flg: E typ: 2 – INDEX
brn: 1 bdba: 0x18000d8 ver: 0×01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0×01 0×0000.000.00000000 0×00000000.0000.00 —- 0 fsc 0×0000.00000000
0×02 0xffff.000.00000000 0×00000000.0000.00 C— 0 scn 0×0000.0026ae40
Leaf block dump
===============
header address 364741220=0x15bd8264
kdxcolev 0
KDXCOLEV Flags = – - -
kdxcolok 0
kdxcoopc 0×80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 378
kdxcofbo 792=0×318
kdxcofeo 1613=0x64d
kdxcoavs 821
kdxlespl 0
kdxlende 0
kdxlenxt 25166049=0x18000e1
kdxleprv 25166047=0x18000df
kdxledsz 0
kdxlebksz 8036
row#0[8019] flag: ——-, lock: 0, len=17
col 0; len 3; (3): c2 10 13
col 1; len 10; (10): 00 01 6b bb 01 80 04 71 00 3b
row#1[8002] flag: ——-, lock: 0, len=17
col 0; len 3; (3): c2 10 14
col 1; len 10; (10): 00 01 6b bb 01 80 04 71 00 3c
row#2[7985] flag: ——-, lock: 0, len=17
col 0; len 3; (3): c2 10 15
col 1; len 10; (10): 00 01 6b bb 01 80 04 71 00 3d
row#3[7968] flag: ——-, lock: 0, len=17
col 0; len 3; (3): c2 10 16
col 1; len 10; (10): 00 01 6b bb 01 80 04 71 00 3e
row#4[7951] flag: ——-, lock: 0, len=17
col 0; len 3; (3): c2 10 17
col 1; len 10; (10): 00 01 6b bb 01 80 04 71 00 3f

We notice there are no deleted index entries, the index remains totally untouched by the drop table partition operation. So the good news is that dropping/truncating a table partition while updating global indexes is extremely fast and efficient while the indexes remain hunky dory as subsequent index range scans can ignore any rowids that don’t reference existing table partitions of interest.

However, the bad news is that during subsequent index DML operations, Oracle does not know which index entries are valid and which are not and so the space used by these “orphaned” index entries can not be automatically reclaimed and reused as it can with conventionally deleted index entries. Therefore, we need some other way to clean out the orphaned index entries.

There are a number of possible ways to do this. One way is to simply rebuild the global index (or index partition):

SQL> alter index muse_code_i rebuild partition code_p1;

Index altered.

SQL> select index_name, null partition_name, orphaned_entries, num_rows, s.blocks, leaf_blocks, status
from dba_indexes i, dba_segments s where i.index_name = s.segment_name and table_name='MUSE' and partitioned = 'NO'
union
select index_name, i.partition_name, orphaned_entries, num_rows, s.blocks, leaf_blocks, status
from dba_ind_partitions i, dba_segments s where i.partition_name = s.partition_name and index_name like 'MUSE%';

INDEX_NAME   PARTITION_NAME  ORP  NUM_ROWS     BLOCKS LEAF_BLOCKS STATUS
------------ --------------- ------------- ---------- ----------- --------
MUSE_CODE_I  CODE_P1         NO    1000000       2944        2758 USABLE
MUSE_CODE_I  CODE_P2         YES   1000000       4352        4177 USABLE
MUSE_ID_I                    YES   2000000       9216        5849 VALID

Effective, but relatively expensive as this requires the entire index structure to be rebuilt from scratch. Depending on the scale and distribution of the orphaned index entries, another possibly cheaper alternative is to use the new CLEANUP coalesce clause:

SQL> alter index muse_id_i coalesce cleanup;

Index altered.

SQL> exec dbms_stats.gather_index_stats(ownname=>user, indname=>'MUSE_ID_I', estimate_percent=>null);

PL/SQL procedure successfully completed.

SQL> select index_name, null partition_name, orphaned_entries, num_rows, s.blocks, leaf_blocks, status
from dba_indexes i, dba_segments s where i.index_name = s.segment_name and table_name='MUSE' and partitioned = 'NO'
union
select index_name, i.partition_name, orphaned_entries, num_rows, s.blocks, leaf_blocks, status
from dba_ind_partitions i, dba_segments s where i.partition_name = s.partition_name and index_name like 'MUSE%';

INDEX_NAME   PARTITION_NAME  ORP  NUM_ROWS     BLOCKS LEAF_BLOCKS STATUS
------------ --------------- --- --------- ---------- ----------- --------
MUSE_CODE_I  CODE_P1         NO    1000000       4224        4137 USABLE
MUSE_CODE_I  CODE_P2         YES   1000000       4352        4177 USABLE
MUSE_ID_I                    NO    2000000       9216        5849 VALID

SQL> analyze index muse_id_i validate structure;

Index analyzed.

SQL> select name, lf_rows, del_lf_rows from index_stats;

NAME            LF_ROWS DEL_LF_ROWS
------------ ---------- -----------
MUSE_ID_I       2000000           0

This visits each index leak block and removes all the orphaned index entries as part of the coalesce process. Note this is a more “powerful” version of coalesce as a standard coalesce is not aware of orphaned index entries and will only coalesce the index without actually removing the orphaned index.

Yet another possible option is to simply wait for the PMO_DEFERRED_GIDX_MAINT_JOB job to run (scheduled by default during the 2am maintenance window) to clean out orphaned index entries from all currently impacted global indexes. Yet another alternative is to manually run the dbms_part.cleanup_gidx procedure which is in turn called by this job:

SQL> exec dbms_part.cleanup_gidx;

PL/SQL procedure successfully completed.

SQL> select index_name, null partition_name, orphaned_entries, num_rows, s.blocks, leaf_blocks, status
from dba_indexes i, dba_segments s where i.index_name = s.segment_name and table_name='MUSE' and partitioned = 'NO'
union
select index_name, i.partition_name, orphaned_entries, num_rows, s.blocks, leaf_blocks, status
from dba_ind_partitions i, dba_segments s where i.partition_name = s.partition_name and index_name like 'MUSE%';

INDEX_NAME   PARTITION_NAME  ORP   NUM_ROWS     BLOCKS LEAF_BLOCKS STATUS
------------ --------------- --- ---------- ---------- ----------- --------
MUSE_CODE_I  CODE_P1         NO     1000000       2944        2758 USABLE
MUSE_CODE_I  CODE_P2         NO     1000000       4352        4177 USABLE
MUSE_ID_I                    NO     2000000       9216        5849 VALID

We notice the last index partition has now been cleaned out and no longer has orphaned index entries.

So with the new asynchronous global index maintenance capabilities of the Oracle 12c database, we can perform a much faster and more efficient drop/truncate table partition operation while keeping our global indexes in a usable state and leave the tidying up of the resultant orphaned index entries to another time and method of our convenience.

12c Asynchronous Global Index Maintenance Part I (Where Are We Now ?) August 2, 2013

Posted by Richard Foote in 12c, Asynchronous Global Index Maintenance, Oracle Indexes, Partitioning.
4 comments

I previously looked at how global index maintenance was performed when dropping a table partition prior to Oracle Database 12c. Let’s see how things have now changed since the introduction of 12c.

Let’s start by creating the same partitioned table and global indexes as previously:

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> create index muse_id_i on muse(id);

Index created.

SQL> create index muse_code_i on muse(code) global partition by range(code)(partition code_p1 values less than (50000), partition code_p2 values less than (maxvalue));

Index created.

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

PL/SQL procedure successfully completed.

If we look at the current state of affairs, all is currently hunky dory:

SQL> select index_name, null partition_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' and partitioned = 'NO'
union select index_name, i.partition_name, num_rows, s.blocks, leaf_blocks, status, orphaned_entries from dba_ind_partitions i, dba_segments s where i.partition_name = s.partition_name and index_name like 'MUSE%';

INDEX_NAME   PARTITION_NAME    NUM_ROWS     BLOCKS LEAF_BLOCKS STATUS   ORP
------------ --------------- ---------- ---------- ----------- -------- ---
MUSE_CODE_I  CODE_P1            1500000       4224        4137 USABLE   NO
MUSE_CODE_I  CODE_P2            1500000       4352        4177 USABLE   NO
MUSE_ID_I                       3000000       9216        8633 VALID    NO

However, a difference to note here is a new data dictionary column called ORPHANED_ENTRIES which denotes whether the index currently has any orphaned index entries. What are these ? We shall see …

Let’s see how expensive it is to now drop the same table partition while updating the global indexes:

SQL> select n.name, s.value from v$sesstat s, v$statname n where s.statistic# = n.statistic# and (n.name = 'redo size' or n.name = 'db block gets') and s.sid=7;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
db block gets                                                        129249
redo size                                                         105069544

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

Table altered.

Elapsed: 00:00:00.76

SQL> select n.name, s.value from v$sesstat s, v$statname n where s.statistic# = n.statistic# and (n.name = 'redo size' or n.name = 'db block gets') and s.sid=7;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
db block gets                                                        129314
redo size                                                         105083724

As we can see, this is significantly different than before when this was a relatively slow and expensive exercise. At just 65 block gets and only 14180 bytes of redo, this is now about the same cost as dropping the partition without updating the global indexes. How can this be ?

If we now look at the status of our global indexes:

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

PL/SQL procedure successfully completed.

SQL> select index_name, null partition_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' and partitioned = 'NO'
union
select index_name, i.partition_name, num_rows, s.blocks, leaf_blocks, status, orphaned_entries
from dba_ind_partitions i, dba_segments s where i.partition_name = s.partition_name and index_name like 'MUSE%';

INDEX_NAME   PARTITION_NAME    NUM_ROWS     BLOCKS LEAF_BLOCKS STATUS   ORP
------------ --------------- ---------- ---------- ----------- -------- ---
MUSE_CODE_I  CODE_P1            1000000       4224        4137 USABLE   YES
MUSE_CODE_I  CODE_P2            1000000       4352        4177 USABLE   YES
MUSE_ID_I                       2000000       9216        5849 VALID    YES

We notice that indeed, the index entries have been reduced (for example, only 2M index entries now in MUSE_ID_I instead of 3M) as if the indexes have been updated. However, we also notice that although the indexes are both marked as now having orphaned entries, they’re still in a USABLE state.

Basically, when dropping (or truncating) a table partition, Oracle in 12c now “postpones” the actual removal of the global index entries associated with the dropped/truncated partition. This can now be performed asynchronously at a time of our choosing. So it’s therefore now very quick and very cheap to update these global indexes on the fly.

However, most importantly, the indexes are still usable and can be guaranteed to return the correct results, ignoring any orphaned entires as required. These can be easily ignored as they all have an object number in the index entry rowids associated with the dropped table partition object and not the table partition(s) of interest as required by the queries.

So if we now select values via the ID column index that only spans data in the dropped table partition:

SQL> select * from muse where id between 42 and 420;

no 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 |     1 |       |     3   (0)| 00:00:01 |       |       |
------------------------------------------------------------------------------------------------------------------------

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

2 - access("ID">=42 AND "ID"<=420)

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

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

0  recursive calls
0  db block gets
4  consistent gets
0  physical reads
0  redo size
470  bytes sent via SQL*Net to client
532  bytes received via SQL*Net from client
1  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
0  rows processed

We notice that quite correctly, no rows are now returned.

The partitioned global index on the CODE column likewise only returns valid data when accessed:

SQL> select * from muse where code=42;

ID       CODE NAME
---------- ---------- ------------------------------
1000042         42 DAVID BOWIE
1100042         42 DAVID BOWIE
1200042         42 DAVID BOWIE
1300042         42 DAVID BOWIE
1400042         42 DAVID BOWIE
1500042         42 DAVID BOWIE
1700042         42 DAVID BOWIE
1600042         42 DAVID BOWIE
1900042         42 DAVID BOWIE
1800042         42 DAVID BOWIE
2000042         42 DAVID BOWIE
2100042         42 DAVID BOWIE
2200042         42 DAVID BOWIE
2300042         42 DAVID BOWIE
2400042         42 DAVID BOWIE
2500042         42 DAVID BOWIE
2600042         42 DAVID BOWIE
2700042         42 DAVID BOWIE
2900042         42 DAVID BOWIE
2800042         42 DAVID BOWIE

20 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 4070098220
---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |             |    20 |   460 |    24   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE                     |             |    20 |   460 |    24   (0)| 00:00:01 |     1 |     1 |
|   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| MUSE        |    20 |   460 |    24   (0)| 00:00:01 | ROWID | ROWID |
|*  3 |    INDEX RANGE SCAN                         | MUSE_CODE_I |    20 |       |     3   (0)| 00:00:01 |     1 |     1 |
---------------------------------------------------------------------------------------------------------------------------

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

3 - access("CODE"=42)

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

Statistics

----------------------------------------------------------
1  recursive calls
0  db block gets
25  consistent gets
2  physical reads
0  redo size
1147  bytes sent via SQL*Net to client
554  bytes received via SQL*Net from client
3  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
20  rows processed

As we can see, only valid data belonging to the non-dropped partitions are returned via the index, even though the index has orphaned index entries that reference the dropped table partition.

If we look at the INDEX_STATS of these indexes, we notice at one level that the orphaned index entries are counted as if they’re deleted entries:

SQL> analyze index muse_id_i validate structure;

Index analyzed.

SQL> select name, lf_rows, del_lf_rows from index_stats;

NAME            LF_ROWS DEL_LF_ROWS
------------ ---------- -----------
MUSE_ID_I       3000000     1000000

We see that the index statistics is indicating that there are 1M so-called deleted index entries. The validation process is ensuring that the orphaned index entries only reference partitions that indeed no longer exist and counts such entries as deleted ones.

So it currently looks we’ve got the best of both worlds here. We effectively get the same performance during the drop table partition operation as if we don’t maintain the global indexes but get the same index availability and subsequent query performance as if we do. So what’s the catch ?

Well, very importantly, unlike actual deleted index entries, they are not readily removed and their space reused by subsequent DML activities within the leaf blocks. In fact, these orphaned index entries can even “get in the way” as we see here when we attempt to reinsert the same data back into table:

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

1000000 rows created.

Elapsed: 00:03:56.52

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

Plan hash value: 1731520519
-------------------------------------------------------------------------------
| Id  | Operation                      | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | INSERT STATEMENT               |      |     1 |     2   (0)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL       | MUSE |       |            |          |
|   2 |   COUNT                        |      |       |            |          |
|*  3 |    CONNECT BY WITHOUT FILTERING|      |       |            |          |
|   4 |     FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

3 - filter(LEVEL<=1000000)

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

700  recursive calls
758362  db block gets
53062  consistent gets
5069  physical reads
355165692  redo size
869  bytes sent via SQL*Net to client
903  bytes received via SQL*Net from client
3  SQL*Net roundtrips to/from client
75  sorts (memory)
0  sorts (disk)
1000000  rows processed

SQL> commit;

Commit complete.

This is notably slower and more expensive than if the index entries had actually been deleted because Oracle is not able to simply identify and overwrite the orphaned index entries during DML operations as they’re not physically marked as deleted. If we look at the INDEX_STATS after inserting these new rows:

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            12288    4000000     1000000

SQL> analyze index muse_code_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_CODE_I           9216    2000000      500000

We notice that unlike actual deleted index entries in which all the deleted space would have simply have been reused, we see instead  that none of the space occupied by the orphaned rows has been reused. This in the end means accessing more index blocks, potentially performing more block splits, more newer blocks having to be generated and overall more work having to be performed than would have been necessary if they had just been plain deleted index entries.

So how we actually get rid of these orphaned index entries ? I look at a number of different techniques we can use in Part II. And yes, good old block dumps are on their way as well :)

Reuse Of Empty Index Leaf Blocks (Free Four) August 1, 2013

Posted by Richard Foote in DBMS_SPACE, Leaf Blocks, Oracle Indexes.
add a comment

A recent question by Stalin Subbiah has prompted me to write a quick post on the reuse of empty leaf blocks. In part, the question asked:

“Is there anyway I could monitor the effectiveness of empty blocks being reused from freelist of an index resulting from purge process that we are planning to start soon?”

I’ve previously discussed how Oracle can recycle index blocks that contain nothing but deleted index entries as such blocks are effectively added to the index freelist to be reused by subsequent index block splits. In my “Index Internals – Rebuilding The Truth” presentation, I mention a number of methods of how to see this reuse in operation, such as via block dumps, tree dumps and INDEX_STATS.

However, another simple method which I don’t think I’ve discussed here before is the use of the DBMS_SPACE package. So to help answer Stalin’s question, a simple demo.

Let’s start by creating and populating a table/index in a non-ASSM tablespace:

SQL> create table radiohead (id number, name varchar2(30)) tablespace bowie_stuff;

Table created.

SQL> insert into radiohead select rownum, 'DAVID BOWIE' from dual connect by level <= 1000000;

1000000 rows created.

SQL> commit;

Commit complete.

SQL> create index radiohead_id_i on radiohead(id) tablespace bowie_stuff;

Index created.

If we use DBMS_SPACE.FREE_BLOCKS to take a look at the number of free blocks currently in the index:

SQL> var free_blocks number
SQL> exec dbms_space.free_blocks(segment_owner=> user, segment_name=> 'RADIOHEAD_ID_I', segment_type=> 'INDEX', freelist_group_id=> 0, free_blks=> :free_blocks)

PL/SQL procedure successfully completed.

SQL> print free_blocks

FREE_BLOCKS
-----------
          0

We can see there are currently no free blocks.

OK, lets now delete a whole bunch of rows from the table/index:

SQL> delete from radiohead where id between 1 and 900000;

900000 rows deleted.

SQL> commit;

Commit complete.

If we now look at the number of free blocks:

SQL> exec dbms_space.free_blocks(segment_owner=> user, segment_name=> 'RADIOHEAD_ID_I', segment_type=> 'INDEX', freelist_group_id=> 0, free_blks=> :free_blocks)

PL/SQL procedure successfully completed.

SQL> print free_blocks

FREE_BLOCKS
-----------
       2003

We can see we now have some 2003 free blocks. Index blocks that are totally empty or contain nothing but deleted index entries are considered free blocks, which can potentially be reused/recycled by subsequent index block split operations.

We’ll now insert a whole bunch of new rows into the table, about 1/2 the number I deleted. Notice these new rows have ID values that are greater than all the current ID values within the table. As we’re effectively inserting monotonically increasing values, Oracle will perform 90-10 block splits, but these new index blocks as required will simply reuse the empty blocks that previously contained the deleted (lower range) ID values:

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

500000 rows created.

SQL> commit;

Commit complete.

We can confirm this by seeing how the number of free blocks has now reduced since the rows have been inserted:

SQL> var free_blocks number
SQL> exec dbms_space.free_blocks(segment_owner=> user, segment_name=> 'RADIOHEAD_ID_I',
segment_type=> 'INDEX', freelist_group_id=> 0, free_blks=> :free_blocks)

PL/SQL procedure successfully completed.

SQL> print free_blocks

FREE_BLOCKS
-----------
        938

We can see that the free blocks has now reduced to just 938 blocks, down from 2003.

So if you’ve previously deleted a batch of rows in a similar manner and you want to keep track of how many index blocks are still currently free (remembering they remain in the index structure in their original logical location until recycled or reused), you can simply use the DBMS_SPACE.FREE_SPACE package.

If your index resides in an Automatic Segment Space Management (ASSM) tablespace, DBMS_SPACE.UNUSED_SPACE provides similar data.

Next, back to Oracle Database 12c and Asynchronous Global Index Maintenance …

Global Index Maintenance – Pre 12c (Unwashed and Somewhat Slightly Dazed) July 26, 2013

Posted by Richard Foote in Global Indexes, Oracle Indexes, Partitioning.
1 comment so far

Before I discuss another Oracle Database 12c new feature, Asynchronous Global Index Maintenance, thought it might be worthwhile discussing how Global Indexes were handled prior to 12c.

I’ll begin by creating and populating a simple range partitioned table:

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.

I’ll now create two global indexes, one non-partitioned, the other partitioned:

SQL> create index muse_id_i on muse(id);
Index created.

SQL> create index muse_code_i on muse(code) global partition by range(code)
(partition code_p1 values less than (50000), partition code_p2 values less than (maxvalue));

Index created.

SQL> select index_name, null partition_name, num_rows, s.blocks, leaf_blocks, status
from dba_indexes i, dba_segments s where i.index_name = s.segment_name and table_name='MUSE' and partitioned = 'NO'
union select index_name, i.partition_name, num_rows, s.blocks, leaf_blocks, status
from dba_ind_partitions i, dba_segments s where i.partition_name = s.partition_name and index_name like 'MUSE%';

INDEX_NAME      PARTITION_NAME    NUM_ROWS     BLOCKS LEAF_BLOCKS STATUS
--------------- --------------- ---------- ---------- ----------- --------
MUSE_CODE_I     CODE_P1            1500000       4224        4135 USABLE
MUSE_CODE_I     CODE_P2            1500000       4352        4177 USABLE
MUSE_ID_I                          3000000       9216        8633 VALID

So we currently have two happy chappy global indexes. I’m now however going to drop one of the table partitions without updating the global indexes and monitor both the db block gets and amount of redo that gets generated:

SQL> select n.name, s.value from v$sesstat s, v$statname n where s.statistic# = n.statistic# and (n.name = 'redo size' or n.name = 'db block gets') and s.sid=129;

NAME               VALUE
------------- ----------
db block gets     457109
redo size      234309652

SQL> alter table muse drop partition muse1;

Table altered.

Elapsed: 00:00:00.66

SQL> select n.name, s.value from v$sesstat s, v$statname n where s.statistic# = n.statistic# and (n.name = 'redo size' or n.name = 'db block gets') and s.sid=129;

NAME           VALUE
-------------- ----------
db block gets      457165
redo size       234320512

We notice the operation completed very quickly and generated minimal db block gets (just 56) and redo (just 10860 bytes). However, this of course comes at a price:

SQL> select index_name, null partition_name, num_rows, leaf_blocks, status
2  from dba_indexes i where table_name='MUSE' and partitioned = 'NO'
3  union select index_name, i.partition_name, num_rows, leaf_blocks, status
4  from dba_ind_partitions i where index_name like 'MUSE%';

INDEX_NAME      PARTITION_NAME    NUM_ROWS LEAF_BLOCKS STATUS
--------------- --------------- ---------- ----------- --------
MUSE_CODE_I     CODE_P1            1500000        4135 UNUSABLE
MUSE_CODE_I     CODE_P2            1500000        4177 UNUSABLE
MUSE_ID_I                          3000000        8633 UNUSABLE

Both global indexes are now unusable as a result as they haven’t been maintained on the fly and so have orphaned index entries pointing to the now non-existent table partition. So it was fast but left the global indexes in an unusable state which have to now be rebuilt.

The other option would be to drop the table partition but to also update the global indexes at the same time as follows:

SQL> select n.name, s.value from v$sesstat s, v$statname n where s.statistic# = n.statistic# and (n.name = 'redo size' or n.name = 'db block gets') and s.sid=129;
NAME               VALUE
------------  ----------
db block gets     129615
redo size      103978912

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

Table altered.

Elapsed: 00:00:13.08

SQL> select n.name, s.value from v$sesstat s, v$statname n where s.statistic# =n.statistic# and (n.name = 'redo size' or n.name = 'db block gets') and s.sid=129;

NAME                VALUE
-------------- ----------
db block gets      185758
redo size       148012132 

We notice this time, the operation has taken considerably longer and has generated many more db block gets (56,143 up from 56) and much more redo  (44,033,220 bytes up from 10,860).  So updating the global indexes on the fly comes at a cost, but at least they remain usable at the end of the operation:

SQL> select index_name, null partition_name, num_rows, s.blocks, leaf_blocks, status from dba_indexes i, dba_segments s where i.index_name = s.segment_name andtable_name='MUSE' and partitioned = 'NO'
2  union select index_name, i.partition_name, num_rows, s.blocks, leaf_blocks, status from dba_ind_partitions i, dba_segments s where i.partition_name = s.partition_name and index_name like 'MUSE%';

INDEX_NAME      PARTITION_NAME    NUM_ROWS     BLOCKS LEAF_BLOCKS STATUS
--------------- --------------- ---------- ---------- ----------- --------
MUSE_CODE_I     CODE_P1            1000000       4224        4135 USABLE
MUSE_CODE_I     CODE_P2            1000000       4352        4177 USABLE
MUSE_ID_I                          2000000       9216        5849 VALID

Having updated the global indexes and having effectively deleted 1/3 of the table with the lowest ID values, if we were to now try and find the current minimum ID value:

SQL> select min(id) from muse;
MIN(ID)
----------
1000001

Execution Plan
----------------------------------------------------------
Plan hash value: 2104594370
----------------------------------------------------------------------------------------
| Id  | Operation                  | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |           |     1 |     6 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE            |           |     1 |     6 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)| MUSE_ID_I |     1 |     6 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

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

The CBO is trying to use the index via the Index Full Min/Max Scan to quickly find this minimum ID. However, it keeps hitting leaf blocks with nothing but empty/deleted entries due to dropping the table partition, until it gets through roughly 1/3 of all the index leaf blocks before finally finding the first (and so minimum) non-deleted index value. As such, at 2,787 consistent gets, it’s a relatively expensive operation.

If however, we were to insert a whole bunch of new rows into the table (note these are rows with an ID value greater than existing rows) and then re-run the same query:

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

1000000 rows created.

SQL> commit;

Commit complete.

SQL> select min(id) from muse;

MIN(ID)
----------
1000001

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

Plan hash value: 2104594370
----------------------------------------------------------------------------------------
| Id  | Operation                  | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |           |     1 |     6 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE            |           |     1 |     6 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)| MUSE_ID_I |     1 |     6 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

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

We notice that at just 161 consistent gets (reduced from 2,787), the Index Full Min/MAX Scan is much more efficient as most of the previously empty leaf blocks on the “left hand side of the index” have now been recycled due to inserting the new data into the “right hand side of the index”. As such, we now find the minimum ID value via the index much more efficiently.

So that was how things kinda worked in 11g and beforehand. However, with Oracle 12c, things have now changed as we’ll see in the next post …

Follow

Get every new post delivered to your Inbox.

Join 1,703 other followers