Index clustering factor
Date: Tue, 8 Jan 2008 17:44:38 -0700
On our 10.2.0.3 database, we have a very simple query that is not using the index that we expected and is instead doing a full tablescan. The clustering factor of this index is very high. Have any of you encountered a similar problem? How have you solved it?
Have any of you used the technique described in the book Cost-Based Oracle Fundamentals by Jonathan Lewis to adjust the clustering factor of an index using the sys_op_countchg() function?
Our query is :
Select * from tableA where fielda=:A and fieldb=:B
We have an index on tableA (fielda, fieldb), but Oracle isn't using the index.
The clustering factor of the index is : 1089825
The table is located in an ASSM type tablespace and the table stats:
I'd appreciate your suggestions ...... or experiences using the sys_op_countchg() function.
Sr. Oracle DBA - Classroom Applications Hosting
University of Phoenix/ Apollo Group, Inc.
http://www.freelists.org/webpage/oracle-l Received on Tue Jan 08 2008 - 18:44:38 CST