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
IMHO, coalesce and rebuild are VERY different concepts (I´m speaking in general here, not specifically about indexes).
Imagine the following situation : you have data for the object A, another object B and a C , D and E objects, this way :
IF I remove B and C data, I will have in disk :
|A|A|A|A| | | | | | | | | |D|D|D|E|E|E|
The empty spaces from B and C are CONTIGUOS, in THIS CASE coalesce will "join" these empty spaces in one unique large free space chunk.
OTOH, if in the original situation I remove B and D objects, I will get :
|A|A|A|A| | | | | |C|C|C|C| | | |E|E|E|
The free chunks are NOT contiguos, SO coalesce will NOT be able to reclaim it and join them in one, LARGER free chunk. In this case, only REBUILDING the object the space will be reorganized to gain more CONTIGUOS free space. I enfatize the ** CONTIGUOS ** : if Oracle needs to create a 10Mb extent but it have in disk only 5 free chunks of 2 Mb each, it bombs. In this case, you will need coalesce or rebuild, to get the 10 mb contiguos space needed.
Just completing : coalesce will NEVER change the
sum(bytes) of free space, but it might change the MAX(bytes). Coalesce ** will ** take adjancent free blocks and make them become one big block, and IT is the reason to use it.
Coalesce , to save if possible b*tree index space used .
Rebuild to regain or reorganise the space used by b*tree index (may be even after u have coalesced it).
You rebuild index after lots of deletes/inserts or updates.When there are lots of gaps.
You coalesce index so that the space already used (not deleted not released) be squeezed more so that u have more blocks to be used available in a leaf block.Also coalesce happens only at the left hand side of the index (from the 2nd column for primary index) and not anything in between blocks.
This is what I read once in Metalink:
If you want to move the index to another tablespaces => REBUILD
If you can't afford the space to have 2 copies in a moment => COALESCE
Rebuild is best coz decreases the B-Level but has table locking and space requirements.
Coalesce is second best, it is good to reclaim space only.
If in your query PCT_USED column of index_stats is, say, below 40 then coalesce first, because it is faster for large indexes, reanalyze, if you think it is still necessary to rebuild then do it
Hope it helps