"high" clustering factors and what to do about them
We have a table T with a simple one-column, non-unique index I on a NULLABLE VARCHAR2(250) column C. The clustering factor of this index is 281,931, cluster ratio (factor/num rows) is .778, the number of rows is 362,547 and the block count is 27,275.
What constitutes a "high" clustering factor? I've read mixed things but basically a lot of people saying that any cluster ratio over .5 is considered "high".
How heavily does this contribute to the to the optimizer's choice to do a index seek vs. a full-table scan? The query in question looks like this: select * from T where C = 'blah' and S is null and C2=1 and C3=0 and C4=0 and TYPE in ('type1', 'type2','type3'). S is VARCHAR2(32), C1-3 are NUMER(4) and TYPE is a VARCHAR2(3).
On a hunch, I also checked the index for the table's primary key column ID (VARCHAR2(32)), and it's clustering factor is 202,400, ratio is .558 with obviously the same # of rows and blocks. This still seems high. Does this mean that even my PK is not an effective index?
Also, when it comes to addressing high clustering factors, I've read competing answers about having to analyze/rebuild the index vs. having to rebuild the table vs. actually having to restructure the data itself. Any advice here?
Thanks in advance! This is my first time delving this deep into the specifics of the Oracle CBO.