RE: Index clustering factor

I'd first try hinting the index and see if that results in less db work.

 

From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Orysia Husak
Sent: Tuesday, January 08, 2008 6:45 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: 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

 


CONFIDENTIALITY NOTICE: 
        This message is intended for the use of the individual or entity to 
which it is addressed and may contain information that is privileged,
confidential and exempt from disclosure under applicable law.  If the reader of 
this message is not the intended recipient or the employee or agent responsible 
for delivering this message to the intended recipient, you are hereby notified 
that any dissemination, distribution or copying of this communication is 
strictly prohibited.  If you have received this
communication in error, please notify us immediately by email reply.

Other related posts: