Hi

I have been testing coalescing and rebuilding indexes today and I was wondering under which situation should coalesce be used and under which situation should rebuild be used?

I have carried several steps to test 2 situations

1.- I filled an table with 1000 rows
2.- Ran analyze index validate structure
3.- checked index_stats
4.- deleted 100 rows
5.- Ran analyze index validate structure
6.- checked index_stats
7.- deleted 500 rows
8.- Ran analyze index validate structure
9.- checked index_stats
10.- alter index rebuild for one test
----- alter index coalesce for another test
11.- Ran analyze index validate structure
12.- checked index_stats

The difference is as follws

before rebuilding and coalescing I had this:
BTREE_SPACE USED_SPACE PCT_USED LF_ROWS DEL_LF_ROWS
----------- ---------- ---------- ---------- -----------
24020 12502 53 900 500


REBUILDING:
BTREE_SPACE USED_SPACE PCT_USED LF_ROWS DEL_LF_ROWS
----------- ---------- ---------- ---------- -----------
7996 5497 69 400 0

COALESCING:
BTREE_SPACE USED_SPACE PCT_USED LF_ROWS DEL_LF_ROWS
----------- ---------- ---------- ---------- -----------
16024 5497 35 400 0


From this result I guess coalesce is pretty useless? Except that it does not require extra space