Re: Index rebuilds

When you create an index for the first time (CREATE INDEX), Oracle will scan the table, collect the column value(s) into memory (unless your sort_area_size is too small, then everything will be flushed into temporary extents in the temporary tablespace), sorts them and start building the index blocks in index extents in the target tablespace.

Each block will be filled to 100% minus the PCTFREE.

From then on, your index starts its life.

Inserts into the index may fill up a block, at which moment the block is split into 2 blocks, each then with 50% free space. Thus, the PCTFREE (a PCTFREE < 10 will be rounded up to 10) does not play any further role during the life of the index, with one exception: the 'last' block in the index is never filled for more than 100-PCTFREE percent; a new block is allocated if the last block goes beyond 100-PCTFREE percent used.

Deletes may empty the blocks. But Oracle will never 'merge' two blocks with each less than 50% used back into one block. Deleted entries may be reused by inserts. But when the last entry in a block is deleted, the block is empty and will from then on NEVER be reused. So, if you have a very active index/table, you may after a while end up with an index with many empty blocks.

If more than 20% of the blocks are empty, I would consider (and only then) rebuilding the index.

To find out how many blocks are lost forever is not possible. But you may get a good idea when you issue the following statement:

ANALYZE INDEX <index> VALIDATE STRUCTURE;

This statement will put a row in the INDEX_STATS table (owned by SYS, but queryable by everyone).

If the ratio of deleted leaf rows (DEL_LF_ROWS) and active leaf rows(LF_ROWS) is higher than 20%, I would do a rebuild.

"Neil Emery" <neil.emery_at_ds-s.com> wrote in message news:3f83a970_at_cpns1.saic.com...
> In the "olden" (or should that be golden?) days we were all recommended to
> regularly export and import
> tables where a large number of inserts/deletes occurred to 'tidy' them up.
> Am i correct in saying that this is no longer required with oracle 7/8/9 ?
>
> TIA
>
> Neil
>
>
Received on Wed Oct 08 2003 - 18:24:18 CDT