July 27, 2013

Index Coalesce : sys_op_lbid

A recent question on the oracle French forum about rebuilding indexes remembered me to write a small note to show the existence of a more interesting option to use when maintaining an index but unfortunately not widely used. It is the COALESCE index command. Let me put a simple model and explain (a) how to identify indexes that might benefit from the COALESCE and (b) how to show the effect of the COALESCE command on this kind of indexes.

This is a smashed index as you might have pointed out that in order to access to 2 leaf block keys we need to visit 102 blocks worth of data and to get 3 other leaf block keys we need to access 451 blocks and so on. This index needs really to be COALESCED. Before coalescing it let me show you how space is spread within this index:

The coalesce command did not changed the size of the index nor the number of its total blocks. However, it did make a nice data distribution since it freed up 1736 new blocks ( 4407-26669) and made 1768 (3197-1429) new blocks offering 25-50% of free space.

Before closing this blog article I would like to emphasize that if you arrive to the conclusion that your index needs to be coalesced then think about a periodic (each week for example) coalesce of this index. Because the conditions which smashed your index (delete of old data from left hand side of the index and insert new data into the right hand side of the index) is still present and will sooner or later smash your index so that a new coalesce will be necessary.

Advertisements

Share this:

Like this:

Related

It would be safe if they never rebuild your indexes. Rebuilding indexes should not be put under the hands of inexperienced DBAs as far as there is more chance that the rebuild will end up (sooner or later) to be of no effect in better cases and to dramatically alter the performance in worst cases.

That’s said, there are cases where an index might benefit from a rebuild(http://jonathanlewis.wordpress.com/category/oracle/indexing/index-rebuilds/). However, I doubt that today DBAs will investigate the benefit of rebuilding their indexes before jumping to the rebuild itself. This is without mentioning that many DBAs ignore the difference between rebuilding an index offline and online and the strategy used internally by Oracle in this two particular cases of rebuild.

Dear Mohamed,
Thank you very much for this very interesting post.
I thought the coalesce functionality was automatic in the tablespaces from the10G onward.
Obviously, we need to program it from time to time, once indexes have been selected as potentially disorganized.
I wonder where is the p_check_free_space function available on your website because I would like to use it.
Thanks in advance.

So your comment for example “in order to access to 2 leaf block keys we need to visit 102 blocks” is incorrect. It means that 102 leaf blocks have just 2 index entries. These index entries could well be unique and you only have to visit the one leaf block for each of these index entries.

Note there’s nothing here to suggest the index was really that “smashed” and the coalesce has simply increased the number of blocks with the maximum number of index entries (those with 377 entries) slightly from 2611 to 2649 and reduced some leaf blocks with fewer entries.

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.