Re: clustering factor and uniqueness

"yoram.ayalon_at_structuredweb.com" <yoram.ayalon_at_structuredweb.com> wrote:
> I have a table with 17M rows, and a B-tree index with 13M unique
> values,

So is that 13M values with one row each and 1 value with 4M rows? Or is it 9M values with 1 row each and 4M values with 2 rows each?

> and clustering factor almost as large as # of unique values.
> Oracle bypasses the index when querying on the field, and if I force it
> to use the index will run forever.
>
> i also have 2 other indexes i needed to maintain.
> 2) bitmap index on another field, only 12K unique values, clustering
> factor of 18K
> 3) index on a subset of field above, only 90 unique values, clustering
> factor of 4M.
>
> queries on the 2 indexes above run in reasonable time
>
> my questions:
>
> I understand I have almost the worst possible clustering factor, but,
> shouldn't the high selectiveness, almost like a PK, get me reasonable
> performance?

That would depend on the query, eh? If the index has good selectivity but the query does not (i.e. range query, cartesian join, etc), then what do you get for your trouble?

> What if that had been a PK with same very high clustering
> factor? Would performance be as bad?

That would depend on the query.

> I have read this definition of
> clustering factor: "A count of how many visits to the table you would
> have to make if each entry in the index was read in turn, with
> consecutive visits to the same table block not being counted". But if
> you have a PK or a highly selective index, why would oracle need to
> read each entry in the index ?

That would depend on what the query actually is.

> What optimization options are available?

That would depend on what the query is.
...

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB