Re: Experience in bitmap index in Oracle 7.3

From: Brad W. Skiles <bwskiles_at_adpc.purdue.edu>
Date: 1997/02/06
Message-ID: <01bc1473$5568fe30$f065d280@freh-nt-007>#1/1

We've begun implementing bitmaps on our 7.3.2.3 HP-UX system. The experience has been good and bad.

Good

Storage improvements have been dramatic. An index on a fiscal_year field drop from 370 megs down to 2.7 megs. Not all reductions have been that extreme, but that shows you what the potential is. Performance has also shown dramatic improvements in some areas. I've seen the cost factor for some queries, using explain plan, drop to a fraction of its original size. Ditto for the execution time.

Bad

I've had a rough time predicting the size of these indexes in advance. I thought I had it worked out once, and the next index was several times larger than I had predicted. I'm still hoping someone can shed some concrete light on a formula for sizing bitmap indexes. It's more complicated than the number of unique keys*number of rows*1 bit.

We've now received 2 patches for 2 seperate bugs that appeared when we tried to create bitmap indexes. (I'm guessing we're on to a 3rd bug now.) One bug occured when a bitmap index appeared in the "and join" section of an explain plan. The other bug was revealed when bitmap indexes generated an ora-600 error when we attempted to build the indexes on one particular table.

The 3rd bug I now suspect has to do with views having to be rebuilt if we add a bitmap index to one of the tables on which the view is based.

I'm also suspicious that the bitmap indexes are causing Oracle to consume more memory. At any rate, my memory useage is up, and I haven't identified any change other than the bitmaps.

Directions

Instructions for bitmap indexes are in the Tuning book. Be sure to read about setting the COMPATIBLE parameter in your init.ora file. Good luck!

--brad
Received on Thu Feb 06 1997 - 00:00:00 CST