Re: Bitmap Sizing

In article <01bc095c$fc2b2b20$f065d280_at_freh-nt-007>

           bwskiles_at_adpc.purdue.edu "Bard" writes:
> The Tuning book says bitmap indexes are typically 25 percent the size of a
> regular B-tree index. In another place it says bitmap indexes are
> typically only a fraction of the size of regular indexes. I'm dealing with
> tables with millions of rows, and I'd like to create initial extents with a
> little more accuracy than "probably around 25 percent" of their current
> size or "likely even smaller." Does anyone have any ideas on a possible
> formula for estimating the size of bitmap indexes?

If I understand correctly, the size of a bitmap index is related to the number of distinct values that the key column has.

If the key column has 474 unique values, there will be 474 'mini bitmaps', one per value.

I guess there must be some admin overhead, pointers etc. to the header info.

> PS. One initial test with a bitmapped index reduced the explain plan cost
> of a query from 6,297 down to 208. The time of the query went from over 2
> minutes to about 20 seconds. This was on a table with 1.1 million records.
> The indexed column has only 474 unique values. The Rows Per Key is 2,417.

One good thing about bitmap indexes is that if you have a query which has this sort of "where" clause...

	select col1, col2, col3 
	from mytable
	where 	col1 = "value1" 
	OR	col1 = "value2";

... Oracle can simply pick up the bitmaps for "value1" and "value2" and then do a logical OR on them - easy ! - hence the speed.

-- 
Dennis
----------------------------------------------------------------
Dennis Adams			dennis_at_comsense.com
Common Sense Computing (UK) Ltd	http://www.comsense.com/comsense
Canada House, 272 Field End Rd, Eastcote, Middx HA4 9NA
Phone: +44 181 866 4400		Fax: +44 181 429 4778