Re: Rebuild indexes
- From: stephen booth <stephenbooth.uk@xxxxxxxxx>
- To: peterdixon001@xxxxxxxxxxx
- Date: Sun, 22 May 2005 19:31:54 +0100
On 5/22/05, Peter Dixon <peterdixon001@xxxxxxxxxxx> 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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
