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.

create table t1 as
 select
 trunc(sysdate) + rownum d1,
 rownum n1
 from dual
 connect by level <= 1e6
 ;
create index ind_d1 on t1(d1);

exec dbms_stats.gather_table_stats (user, 't1');

In order to simulate a real life example of an index evolution I will try to update this index several times for the same range of data until it reaches a final stage

begin
 2 for j in 1..100
 3 loop
 4 update t1
 5 set d1 = trunc(d1) + j
 6 where t1.n1 between 15000 and 20000;
 7 end loop;
 8 commit;
 9 end;
 10 /

PL/SQL procedure successfully completed.

begin
 2 for j in 1..100
 3 loop
 4 update t1
 5 set d1 = trunc(d1) - j
 6 where t1.n1 between 15000 and 20000;
 7 end loop;
 8 commit;
 9 end;
 10 /

PL/SQL procedure successfully completed.

begin
 2 for j in 1..100
 3 loop
 4 update t1
 5 set d1 = trunc(d1) - j+1
 6 where t1.n1 between 15000 and 20000;
 7 end loop;
 8 commit;
 9 end;
 10 /

In order to know if my index can benefit from the coalesce command, I will use the sys_op_lbid internal oracle function which gives the number of Keys(leaf block id) per blocks.

select object_name,object_id
from user_objects
where object_name = 'IND_D1';

OBJECT_NAME OBJECT_ID
-------------------- ----------
IND_D1 509423

select
 2 keys_per_leaf, count(*) blocks
 3 from (
 4 select sys_op_lbid (509423, 'L', t1.rowid) block_id,
 5 count (*) keys_per_leaf
 6 from t1
 7 where d1 is not null
 8 group by sys_op_lbid (509423, 'L', t1.rowid)
 9 )
 10 group by keys_per_leaf
 11 order by keys_per_leaf;

KEYS_PER_LEAF BLOCKS
------------- ----------
 2 102
 3 451
 4 352
 5 133
 6 153
 7 72
 8 98
 9 22
 10 63
…..
 134 1
 140 1
 156 1
 196 1
 206 1
 215 1
 292 1
 377 2611
62 rows selected.

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:

 begin
 p_check_free_space (user, 'IND_D1', 'INDEX');
 end;
 /
Number of Blocks with 0-25% free space = 0 -------> Total Bytes = 0
Number of Blocks with 25-50% free space = 1459 -------> Total Bytes = 11.3984375
Number of Blocks with 50-75% free space = 0 -------> Total Bytes = 0
Number of Blocks with 75-100% free space = 0 -------> Total Bytes = 0
Number of Full Blocks with no free space = 4407 -------> Total Bytes = 34.4296875

Total Blocks : 5866
Total Size MB: 46.928

Now it’s time to coalesce this index and get its new leaf block key distribution

alter index ind_d1 coalesce;

Index altered.

select
 2 keys_per_leaf, count(*) blocks
 3 from (
 4 select sys_op_lbid (509423, 'L', t1.rowid) block_id,
 5 count (*) keys_per_leaf
 6 from t1
 7 where d1 is not null
 8 group by sys_op_lbid (509423, 'L', t1.rowid)
 9 )
 10 group by keys_per_leaf
 11 order by keys_per_leaf;

KEYS_PER_LEAF BLOCKS
------------- ----------
 14 1
 29 1
 35 1
 167 1
 196 1
 210 1
 309 1
 367 1
 377 2649

9 rows selected.

Spot how the index become now (after it has been coalesced) more attractive as far as to get 14 (29, 35 or 367) keys of leaf blocks id we need to visit only one block.

Finally let me show you the new index space configuration the coalesce command has produced

begin
 p_check_free_space (user, 'IND_D1', 'INDEX');
end;
/
Number of Blocks with 0-25% free space = 0 -------> Total Bytes = 0
Number of Blocks with 25-50% free space = 3197 -------> Total Bytes = 24.9765625
Number of Blocks with 50-75% free space = 0 -------> Total Bytes = 0
Number of Blocks with 75-100% free space = 0 -------> Total Bytes = 0
Number of Full Blocks with no free space = 2669 -------> Total Bytes = 20.85

Total Blocks :5866
Total Size MB:46.928

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.