Re: bitmap index and distinct values

as far as I know the most important point is not the number of distinct values but the clustering of values - since bitmap indexes are very good in compressing repeated identical values: a bitmap index on a table with entries of the pattern (0, 0, 0, ..., 0, 1, 1, 1, ...., 1) can be compressed very efficiently - while the same index becomes very large if the values are not clustered (0, 1, 0, 1, 0, 1, ...): so the same number of distinct values can result in a very small and efficient or a very big and useless index. The reason for this behaviour is that bitmap indexes do not store the rowid for every index entry explicitly but calculate them via offsets. Richard Foote explains the effects in his blog:
https://richardfoote.wordpress.com/2010/04/13/so-what-is-a-good-cardinality-estimate-for-a-bitmap-index-column-song-2/.

Regards

Martin Preiss

On Tue, Apr 21, 2015 at 10:37 PM, Cee Pee <carlospena999_at_gmail.com <mailto:carlospena999_at_gmail.com>> wrote:

    Hi all,
    I was doing some reading on the internet about bitmap index     suitability as it relates to the number of distinct values. I came     across several links 1
<http://www.dba-oracle.com/t_bitmap_index_maximum_distinct_values_cardinality.htm>,
    2
<https://richardfoote.wordpress.com/2010/02/18/myth-bitmap-indexes-with-high-distinct-columns-blow-out/>,
    3 related to the topic. It seems what needs to be considered is     the ratio of distinct values of a column to the total number of     rows in the table rather than considering an absolute number (like     over 1,000 or 10,000). Can someone explain or point to a more     descriptive link.
    CP.

--
http://www.freelists.org/webpage/oracle-l