Index clustering factor

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:

  NUM_ROWS BLOCKS

• ----------

   1976721 31517    

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

Thank you,

Orysia      

Orysia Husak

Sr. Oracle DBA - Classroom Applications Hosting

University of Phoenix/ Apollo Group, Inc.

Office: 602-557-6934

Mobile: 602-377-8586

orysia.husak_at_apollogrp.edu  

--

http://www.freelists.org/webpage/oracle-l Received on Tue Jan 08 2008 - 18:44:38 CST