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: Jonathan Lewis <>
Date: Fri, 2 Apr 2004 09:07:47 +0000 (UTC)
Message-ID: <c4jah3$94$>

Notes in-line.


Jonathan Lewis

The Co-operative Oracle Users' FAQ

April 2004 Iceland
June  2004      UK - Optimising Oracle Seminar

"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?
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

Original text of this message