Re: Rebuild indexes

On 5/22/05, Peter Dixon <peterdixon001_at_hotmail.com> wrote:
> I have an application where data is never deleted, I have just rebuilt an
> index which was previously 4gb in size and its now 3gb in size. Can anybo=
dy
> explain?

The first thing that comes to mind is that (assuming what was taught on the Oracle Fundementals and tuning course I went on with Oracle University last year is true[1]) when an index leaf block that is not the right most block is filled up the next entry to be put in results in a 50-50 split, two blocks are created one containing the lower 50% of the entries and the other containing the higher 50%, both half full.. Assumign that the indexed field isn't an increasing key type value (i.e. it is possible to insert a value that will be lower (futher left) than an earlier value) that will mean that your index leaf blocks will mostly be between 50% and 100% full. Assuming a reasonably random distribution of data your average block would be around 75% full (split the difference between 50% and 100%). When you rebuild the index the blocks are (with the same caveat as above) repacked to 100% (or upto PCTFREE, I forget which) full. this would explain why the size of the index has changed to 75% of it's previous value. The same amount of data, it's just packed more tightly.

I don't have a database to hand to check this but it does seem to fit the situation and my reccollection of how I'm told Oracle handles indexes.

Stephen

[1] I'm told, and have seen, that it's not always a given. Maybe there's a conference paper in somone's future entitled something like "Lies Oracle Told Me: The gap between training and reality"

--=20
It's better to ask a silly question than to make a silly assumption.

--
http://www.freelists.org/webpage/oracle-l