Path: text.usenetserver.com!out01b.usenetserver.com!news.usenetserver.com!in02.usenetserver.com!news.usenetserver.com!postnews.google.com!19g2000hsx.googlegroups.com!not-for-mail
From:  hjr.pythian@gmail.com
Newsgroups: comp.databases.oracle.server
Subject: Re: How many people here rebuid index regularly?
Date: Sat, 06 Oct 2007 03:05:01 -0700
Organization: http://groups.google.com
Lines: 31
Message-ID: <1191665101.715663.144170@19g2000hsx.googlegroups.com>
References: <Y1hNi.7131$DF2.4804@trndny09>
   <MPG.216ffe82b29bfb0e98968f@news.dommel.be>
   <1191581490.312563.10430@o80g2000hse.googlegroups.com>
   <MPG.2170841994c1cc35989691@news.dommel.be>
   <1191612916.550302.168250@57g2000hsv.googlegroups.com>
   <1191654820.081330.250130@w3g2000hsg.googlegroups.com>
NNTP-Posting-Host: 124.176.107.55
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
X-Trace: posting.google.com 1191665101 14071 127.0.0.1 (6 Oct 2007 10:05:01 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Sat, 6 Oct 2007 10:05:01 +0000 (UTC)
In-Reply-To: <1191654820.081330.250130@w3g2000hsg.googlegroups.com>
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US; rv:1.8.1.7) Gecko/20070914 Firefox/2.0.0.7,gzip(gfe),gzip(gfe)
Complaints-To: groups-abuse@google.com
Injection-Info: 19g2000hsx.googlegroups.com; posting-host=124.176.107.55;
   posting-account=ps2QrAMAAAA6_jCuRt2JEIpn5Otqf_w0
Xref: usenetserver.com comp.databases.oracle.server:435997
X-Received-Date: Sat, 06 Oct 2007 06:05:01 EDT (text.usenetserver.com)

On Oct 6, 5:13 pm, csn...@gmail.com wrote:
> > I realise this would take time, but have you considered a treedump of
> > the indexes before and after the rebuild:
>
> > alter session set evnts = 'immediate trace name treedump level <index
> > object id>';
>
> > You should see considerable differences in the dump files for a given
> > index if the rebuild of it improves performance.  And those file pairs
> > with differences would indicate the indexes upon which you should
> > concentrate your rebuild efforts.
>
> Comparing index sizes before and after rebuild would probably reveal
> all there is to know.

No, it wouldn't be all there is to know.

If an index settles down at about 75% space utilisation in a steady-
state scenario, it stands to reason that a rebuild will show it small
after the rebuild that before. But if you were to conclude from that
that the rebuild was obviously of benefit, and that the reduction in
size was "all you needed to know", you'd be missing a trick or three.

Does the now-compacted index now suffer from higher contention rates
than it used to? It could well do so. Do the next X-number of inserts
to the table now take place slower than they did before because they
now have to cause the index to re-acquire the empty space it
previously already had available to it? It could well be the case.

Size is NOT everything, in short.