Forum Stats

  • 3,816,532 Users
  • 2,259,202 Discussions
  • 7,893,506 Comments

Discussions

Categories

Optimization of all indexes?

755744
755744 Member Posts: 42
edited Apr 1, 2010 6:42AM in General Database Discussions
Hello,

I know that an "alter index X coalesce" optimizes the index X. But I want to optimize all indexes of a database but I don't want to write a script with some hundred alter index statements? Is there a smart way to do this like:

for i in indexes loop
alter index i coalesce;
commit;
end loop;

(Pseudocode)

Edited by: user9206958 on 01.04.2010 01:30
Tagged:
· Share on TwitterShare on Facebook

Answers

  • Hemant K Chitale
    Hemant K Chitale Member Posts: 15,759 Blue Diamond
    Use PLSQL.

    Define a cursor that selects the index names

    Open the cursor
    Loop through the cursor
    for each index name fetched use
    execute immediate 'alter index '||index_name || 'coalesce ;'


    Hemant K Chitale
    http://hemantoracledba.blogspot.com
    Hemant K Chitale
    · Share on TwitterShare on Facebook
  • Fahd.Mirza
    Fahd.Mirza Member Posts: 2,984 Bronze Trophy
    Does coalesce "really" optmizes an index? Coalescing an index frees up space of adjacent leaf blocks within a branch block and creates free space with in index and that facilitates the future insertion and updation in index without adding up more space. But does it improves the optmizes the performance of select, delete or update or it just optmizes the space of index?

    regards
    · Share on TwitterShare on Facebook
  • Hemant K Chitale
    Hemant K Chitale Member Posts: 15,759 Blue Diamond
    Think of INDEX [FAST] FULL SCAN operations.

    And there has been a long discussion of "large" / "leaking" indexes some months ago. A COALESCE makes sense (and does improve performance for those cases).


    Hemant K Chitale
    · Share on TwitterShare on Facebook
  • 755744
    755744 Member Posts: 42
    In my case the most tables in the database have the character of a queue and every row has the life cycle of Insert -> Updated* -> Delete. And the ids are forgiven with increasing numbers... I think the indexes are degenerately, if there are 1000 new rows daily and 1000 deletes per day. And the system has run since a year.
    · Share on TwitterShare on Facebook
  • mbobak
    mbobak Member Posts: 1,342 Gold Badge
    Actually, no. If the following conditions are all true:
    1.) Sequence generated key.
    2.) Newest key values are inserted.
    3.) Keys are updated over time.
    4.) Oldest keys are deleted.
    5.) Deletion doesn't skip any key values, leaving behind older key values forever.

    If all the above is true, you should never need to coalesce or rebuild your indexes.

    All the new key values go into the "Right hand" leaf block. On the left side, when the last key in a particular block is deleted, the empty block is left in the index structure and added back to the pool of free blocks. As a new block is needed on the right side, Oracle will grab the emptied block from the left side, unlink it from the structure, and move it to the right side.

    So, in this way, blocks move from the left over to the right to be recycled. The only time this cannot happen is if some old values are left behind in the blocks on the left. (i.e. if item #5 above is not true.) If that's the case, there may be some benefit in periodic coalesces.

    For more than you ever needed to know about indexes, how they work, and when index rebuilds are necessary, see Richard Foote's blog, and check out his white papers, especially "Rebuilding the Truth".
    http://richardfoote.wordpress.com/

    Hope that helps,

    -Mark
    · Share on TwitterShare on Facebook
  • Hemant K Chitale
    Hemant K Chitale Member Posts: 15,759 Blue Diamond
    If you are not doing "selective deletes" of older data, then your index on the "queue" column does not need COALESCE.

    If your deletes are not for all "1000 rows" and some rows are excluded from the deletes, you can have near-empty leaf blocks "hanging around" and not being reused.

    Hemant K Chitale
    http://hemantoracledba.blogspot.com
    · Share on TwitterShare on Facebook
This discussion has been closed.