Re: Index clustering factor
- From: Hemant K Chitale <hkchital@xxxxxxxxxxxxxx>
- To: Orysia.Husak@xxxxxxxxxxxxx
- Date: Wed, 09 Jan 2008 23:58:26 +0800
How many rows is the statement
"Select * from tableA where fielda=:A and fieldb=:B"
expected to return ?
What does the Optimizer (explainplan) show as the Cardinality ?
A high Clustering Factor means that the rows for values :A and :B are
spread across very many blocks, non-contigously and Oracle would
certainly favour a full-table-scan if there are many rows to be fetch.
If the statement is expected to fetch only 1 or a few rows because
these two columns have very high skew, you could generate histograms
on the two columns and let Oracle decide to use the index.
If you really really have no other indexes on this table
(really have no other indexes ) then you could rebuild the
table ordered by fielda, fieldb and then rebuild the index
and see the ClusteringFactor come down low.
If you are using ASSM, once you resume inserting rows into the
table, the ClusteringFactor will go bad anyway !
Hemant
At 08:44 AM Wednesday, you wrote:
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
Hemant K Chitale
http://web.singnet.com.sg/~hkchital
and
http://hemantscribbles.blogspot.com
and
http://hemantoracledba.blogspot.com
"There is more to life than increasing its speed."
Mohandas Gandhi Quotes
: http://www.brainyquote.com/quotes/authors/m/mohandas_gandhi.html
--
//www.freelists.org/webpage/oracle-l
Other related posts:
