coalesce vs rebuild
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: coalesce vs rebuild

  1. #1
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

  2. #2
    Join Date
    Sep 2000
    Location
    Sao Paulo,SP,Brazil, Earth, Milky Way
    Posts
    350
    IMHO, coalesce and rebuild are VERY different concepts (Im 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

  3. #3
    Join Date
    Sep 2000
    Location
    Sao Paulo,SP,Brazil, Earth, Milky Way
    Posts
    350

    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

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    so what is the point of coalesce if rebuild does everything coalesce can do?

  5. #5
    Join Date
    Feb 2001
    Posts
    49

    Talking

    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

  6. #6
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    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]

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    LOL
    People I am talking about coalescing indexes and rebuilding indexes, nothing about tablespaces
    From Oracle 8i you can coalesce indexes

  8. #8
    Join Date
    Feb 2001
    Posts
    389
    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.

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

  10. #10
    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
    Ramon Caballero, DBA, rcaballe@yahoo.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width