-
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
-
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 :
|A|A|A|A|B|B|B|B|B|C|C|C|C|D|D|D|E|E|E|
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.
Regards,
Chiappa
-
Complement to answer
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.
[]s
Chiappa
-
so what is the point of coalesce if rebuild does everything coalesce can do?
-
hi
I think rebuild is only applicable to index.
And the fragmentation in the free space is not only
happened due to index. It may also happen
if you delete row from non-indexed tables.
am i right?
I love dba job
-
Rebuilding means recreating an index that has been fragmented (say, after deletes). Coalescing means to defragment a tablespace (due to say, dropping segments).
[Edited by Halo on 04-06-2001 at 10:22 AM]
-
LOL
People I am talking about coalescing indexes and rebuilding indexes, nothing about tablespaces
From Oracle 8i you can coalesce indexes
-
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.
I hope it helps.
-
well not very clear but anyway if that is the case who would coalesce when rebuild does everything coalesce does? Except that rebuild needs extra space to carry out its task
-
Hi Pando.
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|