Coalesce Index has lower costs & does not require more disk space - Oracle Indexes and Partitioned Tables

Q.  While considering Costs and Benefits of Coalescing or Rebuilding Indexes which has lower costs and does not require more disk space?
- Published on 19 Oct 15

a. Coalesce Index
b. Rebuild Index
c. Both A & B
d. None of the above

ANSWER: Coalesce Index
 

    Discussion

  • Nirja Shah   -Posted on 29 Sep 15
    - Improper sizing or increased growth can produce index fragmentation.

    - To eliminate or reduce fragmentation, you can rebuild or coalesce the index.

    - But before you perform either task weigh the costs and benefits of each option and choose the one that works best for your situation.

    - Rebuild Index
    1. Quickly moves index to another tablespace
    2. Higher costs: requires more disk space
    3. Creates new tree, shrinks height if applicable
    4. Enables you to quickly change storage and tablespace parameters without having to drop the original index.

    - Coalesce Index
    1. Cannot move index to another tablespace
    2. Lower costs: does not require more disk space
    3. Coalesces leaf blocks within same branch of tree
    4. Quickly frees up index leaf blocks for use.

Post your comment / Share knowledge


Enter the code shown above:

(Note: If you cannot read the numbers in the above image, reload the page to generate a new one.)