jump to navigation

Bitmap Index Degradation Since 10g (Fix You) June 1, 2010

Posted by Richard Foote in Bitmap Indexes, Index Internals, Oracle Indexes.
trackback

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.

About these ads

Comments»

1. Martin Preiss - June 2, 2010

Richard,
if I recall correctly a row lock is taken for the index leaf entry, when a bitmap index is updated – and no other transaction can update an indexed column in any row covered by that bitmap piece until the original transaction commits or rolls back. So shouldn’t the optimized 10g way (with the reuse of index entries) make the locking issues even worse?

Regards
MP

2. Richard Foote - June 4, 2010

Hi Martin

Indeed (as I’ll discuss sometime) !!

3. oracledisect - September 7, 2010

…How is that you have a DB that is 2 patchsets ahead of latest release by Oracle for 10g2? (10.2.0.7)

Richard Foote - September 9, 2010

Hi Oracledisect

That’s a very very good question !! I have no idea what I might have been drinking at the time !!

Amended the post with the version of Oracle I have here in front of me from which I ran the demo.

Thanks for the heads-up.

4. BITMAP index and Locking issue « Oracle - December 10, 2010

[...] are these from Richard Foote – Bitmap Index Degradation Since 10g (Fix You), Bitmap Index Degradation After DML Prior To 10g (Beauty and the Beast) Leave a Comment LikeBe [...]

5. OLTP sistemlerde Bitmap index kullanımının sakıncaları | eneskoroglu - September 16, 2012

[...] Kaynak: http://richardfoote.wordpress.com/2010/06/01/bitmap-index-degradation-since-10g-fix-you/ [...]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,716 other followers

%d bloggers like this: