Re: Index characteristics

Carl Kayser wrote:

> I have seen "cardinality" and "selectivity", which are reciprocals.
> Basically, selectivity is the mathematical expectation of the number of rows
> that will be retrieved for a particular value (or set of values). So,
> selectivity is an integer and and 0< cardinality <= 1.
>
> Within Sybase I use the non-ANSI formula "select selectivity = avg (count
> (col1) from <table> group by col1". (With the QUEL languages the
> computation appears to be well-defind. Relational calculus vs. relational
> algebra.) I don't know if this works with Oracle.
>
>
> "kstahl" <ktsahl_at_yahoo.com> wrote in message
> news:aq2dnd_pfJxZtFLd4p2dnA_at_comcast.com...
>

>>When an index is build on a column, the degree to which it helps
>>with efficieny is based on just how much variance there is
>>between the values. So, this would mean that if the values were
>>large and were virtually random it would help queries much more
>>then if an index were build on a char field that might only have
>>two possible values.
>>
>>Ok, so I know the theory, but what is the proper terminology for
>>this. I can describe the phenomenon, but I don't remember the
>>exact word that should be used. Is it variability?
>>

Yep, that must be it. I took that and did a google on "oracle index cardinalty" and it basically confirmed what you said. I also learned something else. A regular B-tree index works best with high cardinality while a bit-mapped index works best with low cardinality.

Now, all I have to do is burn that buzz-word into my brain for the next time I need to explain indexes to report writers.

Thanks for the assist. Received on Tue Jun 15 2004 - 19:07:19 CDT