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

I'd like to read your paper too.

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

"Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:<o5bbc.136313$Wa.122659_at_news-server.bigpond.net.au>...
> "SAP BASIS Consultant" <basis_consultant_at_hotmail.com> wrote in message
> news:dd2036f3.0404011105.b68ec47_at_posting.google.com...
> > 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