Re: BITMAP INDEXES....WHAT IS THE SCOOP

let me clarify a bit further. Bitmaps are best used for columns which are very non-selective. The opposite of what you expect for btree performance. Oracle creates a bitmap structure for each distinct value in the column with a single bit for each row. The bit is 1 if the distinct value for that bitmap is true for that row. 0 if it is false. The bitmaps are stored in a compress form and expanded at query time.

You select a column like normal: SELECT * FROM FRED WHERE COLOR = 'BLUE';

If fred.color contains only values 'RED', 'YELLOW' and 'BLUE', then there will be 3 bitmaps. Each bitmap will have the exact same number of bits, one per table row. If you were to merger them all together, you would have a bitmap of all '1's, wouldn't you?

Think about the structures and operations here and you will see that the feature is very fast for highly non-selective columns with many rows. Oracle grabs the bit positions for the desired values/bitmaps and converts each to a ROWID. Then it can fetch rows as usual.

Bear in mind the obvious performance cost of insertion. 7.3 databases require that you rebuild the index each time a row is inserted. Oracle8 supports dynamic insertion, but I have not evaluated the cost yet.

Slick stuff.

Roger Snowden

Troy Fruetel <Troy_Fruetel_at_connects.com> wrote in article <647i90$sk5$1_at_news3.mr.net>...
> >>Where are these things be best used..?<<
Received on Wed Nov 12 1997 - 00:00:00 CST