Re: performance impact of index

"Daniel Morgan" <damorgan_at_x.washington.edu> schrieb im Newsbeitrag news:1085638322.274883_at_yasure...
> Bricklen wrote:
> > mrFarenheit wrote:
> >
> >>>
> >> I your schema is purely read-only, you can create as much indexes as
> >> you want. But bitmapped indexes are only useful when the cardinality
> >> is relatively low (about 1.000 distinct values max).
> >> In other cases (more than one thousand distinct values for the
> >> column), it can be better to use b-tree indexes, but don't forget that
> >> the storage needed is much bigger.
> >> You can alos deal with function-based indexes if you want.
> >> .
> >
> >
> > Would you mind proving your statement "bitmapped indexes are only useful
> > when the cardinality is relatively low (about 1.000 distinct values
max)"?
> >
> > I'd be interested to see how you came up with that number, and the
> > ratios you've used for it.
>
> I would too. From everything I've read and done I'd use a number more
> closer to 10.
>
> --
> Daniel Morgan
> http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
> http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
> damorgan_at_x.washington.edu
> (replace 'x' with a 'u' to reply)
>

IMHO it is not the absolute number of distinct values, but the relation of count(*) = numrows and count(distinct values) = cardinality.
If count(distinct)/count(*) is below 0.1, then a bitmap index maybe taken into consideration.

Of course this may be criticized, it always depends ... In the end it is not number magics, it is just a decision that is to be prooved by seeing the results.

Just my 2c
Jan Received on Fri Jun 04 2004 - 13:52:03 CEST