As usual, always a good idea to book early to avoid disappointment. Last time I taught this in Germany, it was totally booked out.

I’ll also be attending and presenting at Oracle OpenWorld this year. I’ve been asked to present a session for the OpenWorld Oracle User Group Forum on the Sunday (19 September 2010) and with a bit of luck and a few more votes, perhaps the Indexing 11g R1 & R2 New Features presentation I submitted via the Oracle Mix Suggest-A-Session. I’ll also no doubt present something again during the Unconference as I have in past years.

This is a great opportunity to let the “public” not only submit proposals for presentations and panel sessions but also to vote for those sessions you would really like to see at these conferences. If you’ve got a presentation you think would be of interest to others, I would strongly recommend submitting a proposal and see how it goes. It’s a great opportunity to perhaps share your knowledge and experiences at these big Oracle events and to also have a say in what you would like to see presented.

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:

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:

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.

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:

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.

search

Disclaimer

The views expressed on this blog are my own and do not necessarily reflect the views of Oracle Corporation.

Contact Details

If you wish to contact me directly, please do so at richard.foote@bigpond.com