In article <MPG.2170841994c1cc35989691_at_news.dommel.be>, hasta_l3
@hotmail.com says...
> In article <1191581490.312563.10430_at_o80g2000hse.googlegroups.com>,
> hjr.pythian_at_gmail.com says...
> > > We do rebuild indexes, because it was measured to improve
> > > performance on our system (9i).
> > >
> > > See the thread "Proving or Debunking the need for rebuilding"
> > > in this group for a report...
> > >
> > > http://groups.google.be/group/comp.databases.oracle.server/browse_thr...
> > > hl=fr&lnk=gst&q=+Proving+or+Debunking+the+need+for+rebuilding&rnum=1#
> > > 9e32aa3cf9282f81
> > >
> > > --- Raoul
> >
> > Well, from what I read of that thread, you didn't measure very much
> > before, you weren't entirely certain on what to measure afterwards,
> > and it was all a bit vague and airy!
>
> Well, I cannot really agree, Howard
>
> As I recall, that was a very long, very controlled measurement effort,
> whose goal was to check whether our system did benefit from index
> rebuilds.
>
> I really expected to see that index rebuilds were not needed.
>
> To my surprise, the test showed that the rebuilds were beneficial.
> Trouble is that while the tests show with certainty a very
> measurable change, I dont have data to know with certainty why.
>
> The best hypothesis right now is :
> * Many of our queries are optimized with index range scans.
> * Some important indexes do index monotonically increasing fields -
> mainly sequence and date primary keys.
> * The delete pattern is such that 9 out of ten rows with consecutive
> dates or sequences are deleted, leaving the corresponding indexes
> with non empty but sparsely populated leaf blocks.
> - Therefore range scans take longer
BTW a favorite optimization technique here is that critical
queries get their data from the index alone. The underlying
table is not accessed at all.
Received on Fri Oct 05 2007 - 11:47:17 CDT
