RE: Index clustering factor

The statement is expected to fetch only 1 or 2 rows. The default Oracle gather_stats generates a histogram size auto.

We've provided an index hint in the SQL and Oracle sill ignores the index.

We do have other indexes so we can't rebuild the table to reduce the clustering factor for this index.

When I generate an explain plan manually, the appropriate index is used. When I view the sql running through OEM, I see the execution plan is a full tablescan. Since the clustering factor for this index is very high, my assumption is that the index isn't being selected due to the high clustering factor.

In the chapter on clustering factor in the Cost-Based Oracle Fundamentals (chapter 5), Jonathan Lewis talks about a method for changing the clustering factor. I wondered if anyone had ever used that approach and if so, would the statistics have to be locked so that the clustering factor wasn't overwritten next time the stats were gathered.

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
-----Original Message-----
From: Hemant K Chitale [mailto:hkchital_at_singnet.com.sg] Sent: Wednesday, January 09, 2008 8:58 AM To: Orysia Husak
Cc: oracle-l_at_freelists.org
Subject: Re: Index clustering factor

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_at_apollogrp.edu
>

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

--
http://www.freelists.org/webpage/oracle-l