Re: estimated of CLUSTERING_FACTOR

On Sep 5, 8:10 am, 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 -

You have no indication at this point there is a problem; why take time and effort 'resolving' something which doesn't need resolution? The clustering factor hasn't created any problems of which you're aware, so why try to 'solve' that which doesn't exist? Learning is one thing, but to expend effort to 'fix' something which isn't proven to be broken is a waste of time and resources.

I would suggest you read Jonathan Lewis' work on the clustering factor to gain a better understanding of the value and how it's calculated. You can find it here, in his most recent book:

"Cost-Based Oracle Fundamentals", Chapter 5

The ISBN for this text is 1-59059-636-6. The entire book is well worth the time one would devote to reading it. I would strongly suggest purchasing a copy for your personal library.

David Fitzjarrell Received on Wed Sep 05 2007 - 08:49:16 CDT