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

"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 Fri Apr 02 2004 - 04:17:56 CST