Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: estimated of CLUSTERING_FACTOR

Re: estimated of CLUSTERING_FACTOR

From: joel garry <>
Date: Wed, 05 Sep 2007 15:03:35 -0700
Message-ID: <>

On Sep 5, 8:05 am, DA Morgan <> wrote:
> Andrea wrote:
> > On 5 Set, 13:58, sybrandb <> wrote:
> >> On Sep 5, 1:09 pm, Andrea <> 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).
> To be proactive is a good thing. But there is a line that separates
> being proactive and having, as Gaja Krishna Vaidyanatha has called it,
> "Compulsive Tuning Disorder." More people have the later.
> I too am in agreement with Sybrand. It appears that faced with a need
> to do "something" you have chosen an arbitrary "something" without
> metrics supporting that that "something" has any value.
> --

I think you guys are a little too hard on Andrea here. I've read that ch. 5 in the past, and just read it again with this thread and a work issue in mind. Working through the clustering on the work issue, I see it is not relevant. But I wouldn't have known that without looking. And I wouldn't likely have gotten around to looking without Andrea's post. And it is a good thing that bad papers are debunked whenever they pop up. Andrea is right, this group is made for discussing such issues. The only thing I think he could have done better is say which paper he was reading, so we could attack, er, discuss it rationally.

(The work issue is a set of tables had a significant amount of data deleted, exp/imp into a test db showed gigabytes of space saved, even after months of new transactions. Moving tablespaces or exp/imp would significantly impact production, and cost money [ie, me]. Gigs of space is an issue, since they propagate to backups and standby. Performance doesn't _seem_ to be an issue, though waiting on a monthend  batch recently popped up again. Buying more space is a discontinuous cost with fully populated SANs. CTD? Whatever management wants...)


-- is bogus.
"...important questions regarding whether a patent holder's deceptive
conduct before a private standards-determining organization may be
condemned under the antitrust laws."
Received on Wed Sep 05 2007 - 17:03:35 CDT

Original text of this message