Re: estimated of CLUSTERING_FACTOR

On Sep 5, 11:29 am, "Shakespeare" <what..._at_xs4all.nl> wrote:
> "Jan Krueger" <j..._at_stud.uni-hannover.de> schreef in berichtnews: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- Hide quoted text -
>
> - Show quoted text -

Precisely why I suggested Chapter 5 from Jonathan's latest book. Reading that should eliminate a lot of confusion.

David Fitzjarrell Received on Wed Sep 05 2007 - 11:49:29 CDT