Bitmap Index Degradation Since 10g (Fix You)

As discussed in my earlier post on Bitmap Index Degradation After DML Prior To 10g, Oracle wasn’t particularly efficient in the manner it maintained Bitmap Indexes after DML operations. During insert operations, if an existing Bitmap index entry didn’t cover the rowid range of a new row to be inserted, Oracle would create a new Bitmap index entry with a default range of 8 rowids and all the overheads this entails (and all the overheads this entails. As such, Bitmap indexes prior to 9i would often explode in size.

Thankfully since 10g, these issues have been largely addressed.

To illustrate, I’m going to run the exact same demo as I did in 9i, this time specifically on a 10.2.0.4 windows database, although you should get similar results in all versions of 10g/11g as well.

As before, I’m going to create the same table and populate it with the same data, creating a Bitmap index on the CODE column which has 1000 distinct values:

SQL> create table bowie as select mod(rownum,1000)+1 id, mod(rownum,1000)+1 code,'BOWIE' name from dual connect by level <= 1000000;
 
Table created.
 
SQL> create bitmap index bowie_code_i on bowie(code) compute statistics;
 
Index created.
 
SQL> select index_name, blevel, leaf_blocks, num_rows from dba_indexes where index_name='BOWIE_CODE_I';
 
INDEX_NAME                         BLEVEL LEAF_BLOCKS   NUM_ROWS
------------------------------ ---------- ----------- ----------
BOWIE_CODE_I                            1         500       1000

 

A freshly created Bitmap index has the same leaf blocks and index entries as in 9i.

Looking at a partial block dump of the first leaf block in the index:

row#0[5013] flag: ------, lock: 0, len=3019
col 0; len 2; (2):  c1 02
col 1; len 6; (6):  01 42 1e 8c 00 e0
col 2; len 6; (6):  01 42 28 ad 01 7f
col 3; len 2998; (2998):
 03 c1 eb 01 c4 bd 01 c2 eb 01 c5 bd 01 c3 eb 01 c1 eb 01 c4 bd 01 c2 eb 01
 c5 bd 01 c3 eb 01 c1 eb 01 c4 bd 01 c2 eb 01 c5 bd 01 c3 eb 01 c1 eb 01 c4
 bd 01 c2 eb 01 c5 bd 01 c3 eb 01 c2 eb 01 c5 bd 01 c3 eb 01 c6 bd 01 c4 eb
 01 c2 eb 01 c5 bd 01 c3 eb 01 c6 bd 01 c4 eb 01 c2 eb 01 c5 bd 01 c3 eb 01
 c7 bd 01 c5 eb 01 c3 eb 01 c6 bd 01 c4 eb 01 c7 bd 01 c5 eb 01 c3 eb 01 c6
...

row#1[1994] flag: ------, lock: 0, len=3019
col 0; len 2; (2):  c1 03
col 1; len 6; (6):  01 42 1e 8a 00 00
col 2; len 6; (6):  01 42 28 ab 00 9f
col 3; len 2998; (2998):
 00 c4 bd 01 c2 eb 01 c5 bd 01 c3 eb 01 c6 bd 01 c4 eb 01 c2 eb 01 c5 bd 01
 c3 eb 01 c6 bd 01 c4 eb 01 c2 eb 01 c5 bd 01 c3 eb 01 c6 bd 01 c4 eb 01 c2
 eb 01 c5 bd 01 c3 eb 01 c6 bd 01 c4 eb 01 c3 eb 01 c6 bd 01 c4 eb 01 c7 bd
 01 c5 eb 01 c3 eb 01 c6 bd 01 c4 eb 01 c7 bd 01 c5 eb 01 c3 eb 01 c6 bd 01
 c4 eb 01 c0 be 01 c6 ea 01 c4 eb 01 c7 bd 01 c5 eb 01 c0 be 01 c6 ea 01 c4
...

 

We’ll look back and compare differences within this leaf block in a moment but for now just note there are 2 index entries within the leaf block, with 1 index entry for each distinct value of the CODE column (as with the 9i example).

OK, next we insert a new row and see what happens. Remember in 9i, Oracle created a new index entry as no existing bitmap index entries had a rowid range that span the rowid of this newly inserted row:

SQL> insert into bowie values (1, 1, 'ZIGGY');
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> analyze index bowie_code_i compute statistics;
 
Index analyzed.
 
SQL> select index_name, blevel, leaf_blocks, num_rows from dba_indexes where index_name='BOWIE_CODE_I';
 
INDEX_NAME                         BLEVEL LEAF_BLOCKS   NUM_ROWS
------------------------------ ---------- ----------- ----------
BOWIE_CODE_I                            1         500       1000

OK, we notice a significant difference here from the 9i example. Although we’ve just inserted a new row, Oracle has not created a new Bitmap index entry (still 1000 rows in the index). Clearly, Oracle has managed to reuse the existing index entry for the newly inserted CODE value “1”, rather than add a new index entry.

A partial leaf block dump reveals what has happened:

row#0[1992] flag: ------, lock: 2, len=3021
col 0; len 2; (2):  c1 02
col 1; len 6; (6):  01 42 1e 8c 00 e0
col 2; len 6; (6):  01 42 28 ae 00 07
col 3; len 3000; (3000):
 03 c1 eb 01 c4 bd 01 c2 eb 01 c5 bd 01 c3 eb 01 c1 eb 01 c4 bd 01 c2 eb 01
 c5 bd 01 c3 eb 01 c1 eb 01 c4 bd 01 c2 eb 01 c5 bd 01 c3 eb 01 c1 eb 01 c4
 bd 01 c2 eb 01 c5 bd 01 c3 eb 01 c2 eb 01 c5 bd 01 c3 eb 01 c6 bd 01 c4 eb
 01 c2 eb 01 c5 bd 01 c3 eb 01 c6 bd 01 c4 eb 01 c2 eb 01 c5 bd 01 c3 eb 01
 c7 bd 01 c5 eb 01 c3 eb 01 c6 bd 01 c4 eb 01 c7 bd 01 c5 eb 01 c3 eb 01 c6
...

 

Rather than create a new index entry for the CODE value “1”, Oracle has made a couple of key changes to the existing index entry. Firstly, we notice it has changed the end rowid range (col 2) from a value of 01 42 28 ad 01 7f  to  01 42 28 ae 00 07 so that the rowid range now includes the rowid associated with the newly inserted row.

Additionally, it has modified the bitmap index string column (col 3) to incorporate the location of the newly inserted row within the increased rowid range. This has resulted in the bitmap string column increasing from 2998 to 3000 bytes, thus increasing the overall size of the index entry by the 2 additional bytes (3021 up from 3019).

So since 10g, Oracle is significantly more efficient and where possible will simply adjust the current rowid range of the Bitmap index entry and modify the bitmap string accordingly to accommodate a new row value (resulting in an overall increase of just 2 bytes overall in this example) rather than create a totally new index entry (which required an additional 21 bytes in the 9i example).

If we were to populate this Bitmap index from scratch as we did in the 9i example:

SQL> create table radiohead (id number, code number, name char(5));
 
Table created.
 
SQL> create bitmap index radiohead_code_i on radiohead(code);
 
Index created.
 
SQL> begin
  2  for i in 1..1000 loop
  3    for j in 1..1000 loop
  4     insert into radiohead values (j, j, 'BOWIE');
  5     commit;
  6    end loop;
  7  end loop;
  8  end;
  9  /
 
PL/SQL procedure successfully completed.
 
SQL> analyze index radiohead_code_i compute statistics;
 
Index analyzed.
 
SQL> select index_name, blevel, leaf_blocks, num_rows from dba_indexes where index_name='RADIOHEAD_CODE_I';
 
INDEX_NAME                         BLEVEL LEAF_BLOCKS   NUM_ROWS
------------------------------ ---------- ----------- ----------
RADIOHEAD_CODE_I                        2         781       1000

  

We notice the index has not deteriorated anywhere near to the same degree as the 9i bitmap index example. Previously, the 9i bitmap index grew to a massive 5,347 leaf blocks but this 10g version has only become a moderate 781 leaf blocks, just 281 leaf blocks greater than a freshly rebuilt bitmap index. It has only grown by some 56%, (of which much has to do with the free space associated with subsequent index block splits), whereas the 9i version of the index grew by a massive 969%.

In summary, bitmap indexes in currently “supported” versions of Oracle are maintained in a much more efficient manner than they were previously, to the point where the need for frequently rebuilds has been much reduced, even in tables in which such indexes are not dropped during heavy loads.

That said, Bitmap indexes are still unsuitable in OLTP type environments (even in 11g)  due to the locking implications associated with them. Perhaps a discussion for another day.