Oracle Scratchpad

December 13, 2013

Bitmap Question

Filed under: Indexing,Infrastructure,Oracle,Partitioning — Jonathan Lewis @ 6:09 pm GMT Dec 13,2013

This question came up on the OTN database forum a couple of months ago: “Why doesn’t Oracle allow you to create globally partitioned bitmap indexes?” The obvius answer is “It just doesn’t, okay.” But it can be quite interesting to think of reasons why a particular mechanism might not have been implemented – sometimes the answer can give you an insight into how a feature has been implemented, or it might suggest cases where a feature might not work very well, it might give you some ideas on how to work around a particular limitation, and sometimes it might just help to pass the time on a short flight.

This note is just a little speculation about the problem – I was planning to write some detailed stuff about it, but Richard Foote supplied a suitable answer to the original question and I couldn’t find the time to do anything more detailed.

Basic point, I think, is that someone did the trade off between extra coding and extra benefit and decided that adding the feature would require too much effort for too little reward.  As Richard points out in this reply – the rowid stored in the index entry for a global index is a four-part structure in the order (data_object_id, file_id, block_id, row directory subscript) and a bitmap index entry consists of (user-defined key, start rowid, end rowid, string of bits).

If  Oracle decided to implement a global bitmap index they would either have the extra complication of working out how to make “string of bits” cross a data segment boundary; or they could keep it “easy” by simply ensuring that “string of bits” was limited to a single segment.

  • Strategy 1: start worrying about all the complicated side effects that might appear after production implementation if you try to make strings cross segments and decide not to risk it.
  • Strategy 2: recognise that if you keep a string inside a single segment then all your processing is going to be focused on “table segment at a time” – so you might as well not bother with creating global indexes, because any index combination code is going to behave pretty much like local indexes – particularly when you realise that each key value within a segment is likely to reference a lot of rows when the primary purpose of global indexes is to avoid doing a large number of (redundant) index probes to find a small number of rows.

One anomaly than pops into my mind – a globally hash partitioned index on a non-partitioned table doesn’t store an object_id. So why not allow for that type of global bitmap index.  Two-fold answer (a) why do more complicated stuff for one special case, and (b) the primary reason for creating globally hash-partitioned indexes on non-partitioned tables is to reduce contention on highly concurrent inserts – and you don’t create bitmap indexes if that’s happening to the table.

Since you can’t create global (or globally partitioned) bitmap indexes, you also can’t use the index_combine() operation to do btree/bitmap conversions, though. So once you’ve got this far with your thinking you might want to look at the possibility of using my “extended index hash join” strategy for getting as close as possible to emulating an index_combine().  (Investigation and demonstrataion left as exercise – but don’t be surprised if you can’t make it work, unexpected things happen with partitioned tables and indexes.)

 

1 Comment »

  1. Why would you want to, anyway?

    In the cases where I’ve found bitmap indexes appropriate, they’ve always been >99% smaller than their B-tree counterparts… why would you need to partition them? At least, partitioning it differently from however the underlying table might be partitioned?

    Here are the reasons I can think of for globally partitioning an index:

    1. Hash partitioning to avoid insert contention, as you mentioned; not likely with bitmap indexes, as you also mentioned

    2. Making it easier to build a large index piecemeal–reducing peak temp tablespace usage at the cost of more tablescans

    3. Creating partial indexes by juggling certain partitions to be marked unusable, to avoid maintenance overhead; but for any situation (that I can think of) where this is likely to be a big win, the B-tree would do just as well as the bitmap

    4. Partition-wise joins; but on a low-cardinality column that’s very unlikely to be helpful

    The only one that I can’t dismiss out of hand is #2. I’ve worked with bitmap indexes some, but nowhere near enough to have a good feel for how many resources it takes to build one vs. an equivalent B-tree.

    Other than that, maybe I’m not creative enough, but I just can’t come up with a use case that would make the feature actually desirable.

    Comment by Jason Bucata — December 13, 2013 @ 7:55 pm GMT Dec 13,2013 | 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

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,422 other followers