Notes in-line.
--
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
April 2004 Iceland http://www.index.is/oracleday.php
June 2004 UK - Optimising Oracle Seminar
"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?
>
Your 20% is close to text-book for space reclamation.
B-tree indexes on randomly arriving data tend to run at
an average of about 75% packing (possibly 69% if a paper
on fringe analysis that I've recently looked at is relevant)
and a rebuild to the default creation state (PCTFREE 10) packs
them at 90%. Working the arithmetic
You start at around 75 blocks worth of data spread
across about 100 actual blocks.
After packing, you have reallocated to ca. 75 * 10 / 9 blocks.
which is about 83 blocks. A saving of 17%. (Using 69 as the
typical figure, the answer drops out at 23%)
Predicting what this will do for queries in terms of improving
performance is case specific, but in general you wouldn't
expect it to make things worse.
> 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....
>
Deleted leaf entries and height don't really tell you anything,
but if you "validate index", which locks the index and can take
a long time, the index_stats view gives you PCT_USED column
which tells you roughly the percentage of the current used branch
blocks (BR_BLKS) and leaf blocks (LF_BLKS) you would need
if you rebuilt the index at 100% (PCTFREE 0).
> Yes, I know, that such information will not be applicable to every
> situation, etc.., but I am curious as to whether anybody has studied
> the issue in detail.
There is some discussion of this in my book (Practical Oracle 8i),
it hasn't changed significantly in 9 or 10.
>
>
> Thanks,
> SAP BASIS Consulant
Received on Fri Apr 02 2004 - 03:07:47 CST
