clustering factor and uniqueness

I have a table with 17M rows, and a B-tree index with 13M unique values, 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? What if that had been a PK with same very high clustering factor? Would performance be as bad? 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 ?

What optimization options are available? Will using bitmap help for the big clustering factor index?
If I have to rebuild the table to match the index order, what will happen to the other 2 indexes? Will using bitmap indexes help? Received on Wed Mar 29 2006 - 13:08:57 CST