Re: STATE columns - bitmap index or not?

Should Gender be bitmapped-indexed on a table subject to extensive DML? Absolutely not. The system would be crippled. Nothing unambiguous about it, I'm afraid.

In other words, there's more at stake here than whether the column has low cardinality or not.

But that aside, I believe there was a post ages ago (either from Jonathan Lewis or Steve Adams, but my brain may be failing me utterly, and it could have been someone completely different) that suggested a cardinality of 50 is not unreasonable (it seems just a trifle high to me -I always teach a cardinality of 20 is where it starts to get dubious).

I think the other issue is that bitmaps are really only of use when you are examining vast amounts of data. A cardinality of 50 if there are only 50 rows is a ridiculous candidate for bitmapping. The same cardinality on 100000 rows starts to sound more reasonable.

I'm afraid it all comes down to 'suck it and see' -what's your application doing, how often does it do it, how significant is the gain or loss in performance for operation A as compared with the loss or gain for operation B on the same table and so on.

Regards
HJR <dmnwork_at_my-deja.com> wrote in message news:968n4c$tqh$1_at_nnrp1.deja.com...
>
>
> Should a STATE column be indexed using a bitmap index or b-tree index?
> Is 50 too high a cardinality to consider using a bitmap index? Any rule
> of thumb here, or should I create both types of index and "set timing
> on" to test the speed of queries using that index?
>
> Clearly, a GENDER column with only two unique values would be an
> unambiguous candidate for a bitmap index; past what number does the
> utility of a bitmap index provide diminishing returns?
>
> Thanks very much.
>
> - Dana
>
>
>
>
> Sent via Deja.com
> http://www.deja.com/
Received on Tue Feb 13 2001 - 01:28:49 CST