Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: estimated of CLUSTERING_FACTOR

Re: estimated of CLUSTERING_FACTOR

From: Jan Krueger <>
Date: Thu, 06 Sep 2007 00:44:48 +0200
Message-ID: <46df30d7$0$29371$>

Shakespeare wrote:

> "Jan Krueger" <> schreef in bericht 
> news:46dec915$0$29373$

>> Andrea wrote:
>>> 1) if c factor is lower than blocks, maybe the table could have
>>> problems of fragmentation because of many DML statements (insert and
>>> delete)
>>> 2) if c factor is higher than blocks and it approach to num_rows, then
>>> the rows in the index are not ordered (not sync with the index).
>>> In first case: is SHRINK the table a method for resolve the problem?
>>> In second case: rebuild index resolve order of the rows ?
>>> Or, for both case the only best method is truncate and reinsert all
>>> rows?
>> While I totally agree to the fellows, there is nothing like a proactive
>> tuning, it should be nevertheless ok to answer your questions.
>> I don't know what you mean with fragmentation, but that's nothing, the
>> clustering factor is about. The clustering factor is an indication for the
>> CBO to use an index for a multirow result set or get it by a full
>> table scan. Another indication which is much more valuable for this
>> decision is a histogram.
>> To resolve an actual problem with index access where the underlying table
>> is not in a good order to fullfill by an index range scan at low cost you
>> need to rebuild the table in an ordered way. E.g. create new_table as
>> select from old_table ordered by...; rename new to old
>> This is something to also have in mind for definitions of materialized
>> views and cache tables.
> This still leaves the problem of multiple indexes, in different sort order. 
> Make one better, make the others worse....

Agreed. But we are only looking for the indexes where a range scan will be performed returning many rows. Two weeks ago I had a materialized view where the query based on it dramatically lost performance over night because the build order has changed (because someone added an index on an underlying table). The query utilized an index range scan on a date column and the solution was to add an order by date to the view definition.

I think it's worth to know. And also I'm sure most of the stuff said here is written in some book.

Jan Received on Wed Sep 05 2007 - 17:44:48 CDT

Original text of this message