Oracle Scratchpad

August 24, 2011

Bitmap Index

Filed under: Indexing,Oracle — Jonathan Lewis @ 6:18 pm BST Aug 24,2011

A few days ago I got an email about a problem with a system that would use a bitmap index to satisfy a query but wouldn’t use the equivalent B-tree index – and the DBA wanted to make the switch because he wanted to downgrade to Standard Edition from Enterprise Edition.

In outline there was a table with a mapping column defined as varchar2(N) holding a string of 1′s and 0′s representing a bit mask. Typically each map value had about 800 rows associated with it and the users’ queries were all about matching bits using the utl_raw.bitand() and utl_raw.bit_or() functions.

My response was that the only surprise was that Oracle was using the bitmap index, not that it wasn’t using the B-tree index as it seemed that the only way the index could be used was with an index fast full scan. I was curious, so I said I’d take a look at the query, the object definitions the plan, and the 10053 trace file if the DBA cared to send them to me.

It turned out that I was correct – the index fast full scan was the plan used by the bitmap index because the queries were of the form:

select  count(*)
from    tableX t0
where
        utl_raw.bit_and(t0.mapping, '0000.....001') = '0000.....001'
;

But, as the DBA pointed out, Oracle didn’t even consider this plan when he changed the bitmap to a B-tree. Why not ? For the same old reason that Oracle often surprises people by ignoring indexes – the column was not declared as NOT NULL, which means there could be rows in the table that are not in the B-tree index, so the index was not a valid target for comparison. (In this case the human eye can see that this is irrelevant, but the optimizer is blindly following a heurisitc – or rule – at this point.)

Key point: Oracle’s standard B-tree indexes do not hold index entries that are completely null. Bitmap indexes (and cluster indexes) do have entries for the nulls.

3 Comments »

  1. On a bit different but associated topic – utl_raw’s bit functions are very slow. And as far as I remember this is the case with statistics_level set to TYPICAL. From a colleague of mine who did some research on why, it looks like time is spent somewhere in accounting PL/SQL running time (v$session_time_model). No guarantee on that, but it could be tested.

    Comment by Timur Akhmadeev — August 25, 2011 @ 7:01 am BST Aug 25,2011 | Reply

    • I wonder how much the dbms_profiler would pick up in terms of internal call counts. Mind you, almost any pl/sql function at the wrong place in a where clause – particularly when called for every row in a large table – tends to be a CPU threat.

      Comment by Jonathan Lewis — August 27, 2011 @ 5:36 am BST Aug 27,2011 | Reply

  2. [...] Jonathan Lewis shares a very interesting post about bitmap index. [...]

    Pingback by Log Buffer #235, A Carnival of the Vanities for DBAs | The Pythian Blog — August 26, 2011 @ 11:01 am BST Aug 26,2011 | Reply


RSS feed for comments on this post. TrackBack URI

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

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,909 other followers