Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: When to use bitmap index based on cardinality

Re: When to use bitmap index based on cardinality

From: TurkBear <johng_at_nospam.mm.com>
Date: Fri, 05 Feb 1999 19:34:43 GMT
Message-ID: <36bc46dc.2859321@news2.mm.com>


It has always been my understanding that low cardinality is a non-relative term; that is, it is based on how many values a particular column can have, regardless of the number of rows containing this column... So, to my mind, bitmap indexes are for things like Yes/No, Male/Female, etc... Of course, my understanding may be, and many times has been, mistaken...

cutler_temp_at_usa.net wrote:

>I'm on Oracle 7.3.4.2
>
>The Oracle Tuning documentation says this about bitmap indexes:
><
>Bitmap indexes can substantially improve performance of queries with the
>following characteristics:
>
>The WHERE clause contains multiple predicates on low- or medium-cardinality
>columns.
>The individual predicates on these low- or medium-cardinality columns select a
>large number of rows.
>Bitmap indexes have been created on some or all of these low- or medium-
>cardinality columns.
>The tables being queried contain many rows.
>>
>
>So, if I have a table of 20 million rows, and 10 columns on that table have
>only 50 distinct values or fewer each, and those columns are in the WHERE
>clause of an SQL statement, those columns are candidates for bitmap indexes.
>
>But, what does low- or medium-cardinality really mean? If I have a column
>with 5000 distinct values on a 20 million row table, then the cardinality
>could be expressed as 5000/20,000,000 = 0.00025. This is low cardinality
>compared to the number of rows, right? Is this column a good case for bitmap
>indexing? I think it is, but I'm not sure. Does anyone know? I've had a
>difficult time actually testing it due to the large number of rows involved.
>I have received different answers from Oracle support, depending on who I'm
>talking to.
>
>Thanks
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

To reply please remove the 'nospam' part of the address Received on Fri Feb 05 1999 - 13:34:43 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US