Re: bitmap index and distinct values

From: Cee Pee <carlospena999_at_gmail.com>
Date: Sat, 25 Apr 2015 13:05:24 -0500
Message-ID: <CAPTPB13axJD0_vvhjVcm-Y-99VarAm_u66cxnqEG=fSx33cBmw_at_mail.gmail.com>

Thank you Martin and Ram. I read the link, it is bit too technical for me to follow. A picture would help a lot. I have seen pictures related to btree index structure,but none on bitmap index structure. Do you happen to have a diagram related to bitmap index. I can compare the picture and the article. The picture of bitmap indexes in my mind are more of an array shaped structures, not a tree shaped as the article seems to suggest. thanks.

On Thu, Apr 23, 2015 at 12:30 AM, Martin Preiss <mtnpreiss_at_gmx.de> wrote:

> Carlos,
>
> 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> 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

Received on Sat Apr 25 2015 - 20:05:24 CEST