Re: estimated of CLUSTERING_FACTOR

"Jan Krueger" <jk_at_stud.uni-hannover.de> schreef in bericht news:46dec915$0$29373$4c56b896_at_news-read1.lambdanet.net...
> 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
>
>
>

This still leaves the problem of multiple indexes, in different sort order. Make one better, make the others worse....

Shakespeare Received on Wed Sep 05 2007 - 11:29:12 CDT