Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Average Disk Space Saved By Index Reorganization in 8i or 9i

Re: Average Disk Space Saved By Index Reorganization in 8i or 9i

From: Mark <>
Date: 5 Apr 2004 11:23:10 -0700
Message-ID: <>

I'd like to read your paper too.

Mark Simmons
Sr. Oracle DBA
Sabre-Holdings, Southlake, TX

"Richard Foote" <> wrote in message news:<o5bbc.136313$>...
> "SAP BASIS Consultant" <> wrote in message
> > Hello,
> >
> >
> > Last year, on an 8i/AIX database, I remember reducing the size of some
> > indexes (Default B-Tree) by some 20% or more, and improving query
> > times fairly significantly, although the indexes, generally, had few
> > deleted leaf nodes (Some 3% or fewer), and an index height of 3. Thus,
> > theoretically, they were good indexes (My stats. were very recent),
> > but they still benefited from a rebuild. Is this very unusual, or have
> > others had similar results?
> >
> > Is there any information (For 8i or 9i) as to the extent to which
> > indexes can *generally* be reduced in size, given the deleted leaf
> > node/leaf node ratio, height, PCTFREE and/or other settings....
> >
> I've just about completed a white paper on Oracle B-Tree indexes called
> "Oracle B-Tree Index Internals: Rebuilding The Truth" and earlier in the
> week presented a PowerPoint version to our local user group. It basically
> looks at the issue of index myths and actually *proves* them to be
> incorrect. It also shows how one can investigate indexes oneself via the
> imaginative use of Oracle treedumps, block dumps and index statistics. It
> also gives clear guidelines on when index rebuilds may be beneficial, the
> specific characteristics that the index needs to meet and the various
> contributing factors that need to be considered. It covers issues that are
> simply not documented anywhere and uses example case scenarios that are
> reproducible and can be the basis of further investigations in your own
> specific environments.
> Basically the conclusion is that in the vast majority of indexes for the
> vast majority of queries in the vast majority of database configurations, a
> saving of 20% index space would achieve between none to negligible
> performance benefits. I say majority because in some very specific
> scenarios, some benefits could be expected (e.g. a Fast Full Index Scan
> where a 20% improvement would be expected if the index was sufficiently
> sized for 20% to be noticeable). That said, other factors such as subsequent
> insert performance and space wastage after subsequent block splits needs to
> be factored in as well. I discuss and show demos of it all.
> The reaction I've received after my presentation to the user group has
> really shocked me. Both in terms of how well it's been received and in how
> so many people have such a myth driven perspective in how indexes work and
> need to be maintained. I had people with over 10 years Oracle experience say
> they "never know that".
> The white paper just needs a little tidying up and a good proof read before
> it's ready to be "aired" in public.
> However, in answer to your specific question, I would class your example as
> "very unusual".
> Cheers
> Richard
Received on Mon Apr 05 2004 - 13:23:10 CDT

Original text of this message