Re: STATE columns - bitmap index or not?

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Mon, 12 Feb 2001 16:12:19 -0000
Message-ID: <969214$9r9$1@soap.pipex.net>

consider bitmap indexes only for low cardinality columns that have a low frequency of insert/update/delete activity. In other words its the two conditions cardinality and rate of chanmge of data that need to be considered.

As for beyond when do bitmaps become ineffective, I haven't done or seen any research on this, but would expect (in other words your mileage may vary) the limit to be some percentage of the number of rows in the table. in other words I'd expect state to be a good candidate for a 10 billion row table and a very poor one for a 250 row table.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
<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 Mon Feb 12 2001 - 10:12:19 CST