Re: estimated of CLUSTERING_FACTOR

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.

Jan Received on Wed Sep 05 2007 - 10:22:06 CDT