Re: When to use bitmap index based on cardinality

There is a document on bitmap indexes on and some 'proper' tests on my website that goes into some detail, but your assumption that 5,000 value on a 20M row table is perfectly sound.

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

cutler_temp_at_usa.net wrote in message <79fecl$85p$1_at_nnrp1.dejanews.com>...
>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
Received on Fri Feb 05 1999 - 13:45:49 CST