Re: Index rebuilds

Luc Gyselinck wrote:

> 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.

Hate to be picky, but that's not actually true. *On average* the split works out at 50%, but it's up to Oracle to decide what the actual split should be at any one point, and that could well be nowhere near 50% for a particular split at a particular time.

>Thus, the PCTFREE (a
> PCTFREE < 10 will be rounded up to 10)

Where on earth did you get this idea, that sub-10% PCTFREEs are rounded up to 10%? It's not true.

>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.

Er, that's not strictly true either. Oracle will never coalesce two sub-50% blocks like that *automatically*, true. But they didn't invent 'alter index blah coalesce' in Oracle 8i for no reason you know.

>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.

If I'm reading you correctly, that statement is untrue, too. A block which is totally empty can most certainly be re-used. If you have a block with entries for Adam, Bob, Charles and David. And a second block with Edward, Frank, Graham and Howard. And you delete Adam, Charles and David, then that first block is still very much a 'B-ish' block, because Bob's still in it. You can't insert a record for 'Wilma' and expect it to slot into all that empty space in Bob's block. Instead, Wilma will have to go into a new, third, index node.

But if you were to delete Bob as well, then there are no entries at all in the first leaf node. And that node therefore has no 'value-based' significance. It's not a 'B-ish' block any more, but a completely free bit of space. If you were now to insert the entry for Wilma, it will most definitely use that first node, and not need to acquire a third one.

Therefore, when 100% empty, index leaf nodes can, and are, re-used. And that of course is exactly what a PCTUSED of 0 would mean and imply, and precisely why PCTUSED can't be specified for an index... precisely because it *is* (and logically can only be) zero.

>So, if you have a
> very active index/table, you may after a while end up with an index with
> many empty blocks.

If they are totally empty, that's not the case. The next set of inserts to the table will re-use the completely vacated leaf nodes.

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

Oh dear. Not true.

>
> To find out how many blocks are lost forever is not possible.

That's because there's no such thing as a block that is lost for ever.

>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.

Screammmmmm. This is complete hokey. It's also been discussed to death on this very newsgroup barely a few weeks ago. That ratio is not measuring 'blocks that are lost for ever', but merely the proportion of leaf entries which have been deleted but haven't been cleaned out of their leaf nodes (Adam, Charles and David entries, using the earlier example) *yet*, versus the number of legitimate leaf entries.

But the point is that Oracle *will clean out* those deleted leaf entries, of its own accord, and without any intervention on your part the minute you insert a new record which needs to be housed within a leaf node that contains some deleted entries. Hence, pursuing the earlier example, if you delete Adam, Charles and David, you would have 3 deleted leaf entries. If you then insert a record for Cynthia, you would have 0 deleted leaf entries. And all without a single rebuild.

And yes, Oracle clears out all THREE deleted entries from the one node, even though you are asking only to re-use the space occupied by one of them.

You started out so well, and then trailed away into a blizzard of myths, half-truths, complete errors and misstatements. You ought to work for TUSC.

The actual truth of the matter is that indexes almost never need to be rebuilt, because Oracle's own self-balancing and self-cleaning mechanisms are very efficient. And they've been that way since Oracle version 7, so this isn't a version-dependent thing, either. (OK: coalesce is an 8i thing).

Regards
HJR

-- 
--------------------------------------------
See my brand new website, soon to be full of 
new articles: www.dizwell.com.
Nothing much there yet, but give it time!!
--------------------------------------------