Re: Index characteristics

From: Carl Kayser <kayser_c_at_bls.gov>
Date: Tue, 15 Jun 2004 15:16:39 -0400
Message-ID: <canhun$aa4$1@blsnews.bls.gov>

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?
>
Received on Tue Jun 15 2004 - 14:16:39 CDT