Shakespeare wrote:
> "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.
>>
>
> 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
