Re: estimated of CLUSTERING_FACTOR

On Sep 5, 9:22 am, sybrandb <sybra..._at_gmail.com> wrote:
> On Sep 5, 3:10 pm, Andrea <netsecur..._at_tiscali.it> wrote:
>
>
>
>
>
> > On 5 Set, 13:58, sybrandb <sybra..._at_gmail.com> wrote:
>
> > > On Sep 5, 1:09 pm, Andrea <netsecur..._at_tiscali.it> wrote:
>
> > > > Hi,
>
> > > > i was reading some paper related on clustering factor, this column
> > > > aimed to identified if the rows of a table are synchronized (ordered)
> > > > with the index.
> > > > If i don't mistake, the value of clustering_factor have to approached
> > > > to number of blocks of the table and more far away from number of
> > > > rows.
>
> > > > So, if i have understood well, main indications on this column are:
>
> > > > 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?
>
> > > > thanks for your advice and opinions.
> > > > bye
> > > > --
> > > > Andrea
> > > > ( a guy that tries to become to DBA)
>
> > > The best method is to bother only when it will *resolve* anything.
> > > Otherwise you are embarking on an exercise in futility and getting
> > > paid for wasting your and your employers time.
>
> > > --
>
> > sorry but i discord with you, some crumbs of my FREE time i dedicated
> > for learn and increase my competence.
> > I think that one (but important) of DBA role is "also" do a proactive
> > tuning of database.
> > what is badly in this?
> > if i search and engage to find possible bottleneck (leaf blocks,
> > chained rows, objects fragmentation, waits, clustering factor, etc..)
> > i could to avoid that this become to a problem (reactive).- Hide quoted text -
>
> > - Show quoted text -
>
> Please look up the word *resolve* in a dictionary. Your so-called 'pro-
> active' maintenance won't *resolve* anything, it will just waste
> resources, time and money.
> It is the same issue as rebuilding indexes, rebuilding tables,
> defragmenting tablespaces etc: it just doesn't RESOLVE anything.
>
> --
> Sybrand Bakker
> Senior Oracle DBA- Hide quoted text -
>
> - Show quoted text -

Andrea, Sybrand is correct in that you should not rebuild tables or indexes without a specific goal in mind and that goal should be backed by statistics.

There are many web articles about Oracle available that contain misleading or just plan wrong information. Some of the most prolific authors are the worst offenders for publishing misinformation.

Think about what you have read. Look up other source of information on the same subject. Try to test the information before acting on it.

In the case of clustering remember that if a table has multiple indexes that the table rows can be sorted into an order that aids the clustering of probably only one of the indexes and doing so may well hurt the clustering factor of one of more of the other indexes. This means you need to measure the effect not only on the query that you reorganized the data for but also on the queries that used the other indexes. You may order the table to improve the clustering for idx1 but find no noticalbe performance improvement when you are done but find a noticable adverse affect for the query using idx2.

So think before acting and test your results. Document them so you know if you want to do the activity again.

HTH -- Mark D Powell -- Received on Wed Sep 05 2007 - 09:03:37 CDT