Index clustering factor

From: Orysia Husak <>
Date: Tue, 8 Jan 2008 17:44:38 -0700
Message-ID: <>

On our 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:


  • ----------

   1976721 31517    

I'd appreciate your suggestions ...... or experiences using the sys_op_countchg() function.  

Thank you,


Orysia Husak

Sr. Oracle DBA - Classroom Applications Hosting

University of Phoenix/ Apollo Group, Inc.

Office: 602-557-6934

Mobile: 602-377-8586  

-- Received on Tue Jan 08 2008 - 18:44:38 CST

Original text of this message