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@xxxxxxxxxxxxx

Other related posts: