RE: Index clustering factor
Date: Wed, 9 Jan 2008 14:37:08 -0600
I'd first try hinting the index and see if that results in less db work.
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Orysia Husak Sent: Tuesday, January 08, 2008 6:45 PM
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:
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.
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.