DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Oracle indexes 101

  1. #1
    Join Date
    Mar 2001
    Posts
    149

    Oracle indexes 101

    Hi gurus, can you please provide me a good explanation of the following situations:

    - Impact of b-tree indexes when performing DML statements like massive inserts/updates/deletes
    - Periodically rebuilding of indexes (some said this is a waste of time and could be dangerous a activity)

    Thanks alot!!

  2. #2
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    Well, impact on DML is obvious, the index should be supported as well. Some gurus here with more practical esperience than me says that an index doubles the dml time. Of course that depends on the size of the table and respectively on the size of the index and on the kind of DML.
    For expample insert should not be directly dependent on the size of the table, sice data are not scanned at all, all you do is finding a space to insert the row, which, yes could be a bit slower in case of more extents but still not so critical. When we put an index into the scheme, in addition you should insert a new entry into the index at the appropriate place which means you read the root block+several branch blocks + several leafs, possibly then you split the leaf, possibly even some branches - here you are. Now multiply that by the number of indexes ( inserting entire row suppose you should update all of them)
    Updates are different story. You maintain only the indexex on the updated columns. So, possibly if you update a col which is not indexed - no problems at all. If the column is indexed, then it's even worse than insert, since you first find and delete the entry in the index for the original value, then insert new entry for the new value. On the other hand, you probably use where clause in the uodate. That means that index could help here, finding the rows to update and hence speeds up the update.
    Delete leads to deleting entries into all the corresponding indexes. Again delete uses where clause (if no where clause, you should use truncate) so, index can speed up the delete.
    About the rebuild, it depends on the number of so called deleted leaf rows. When you analyze index, a statistics are collected and you can see the number of deleted leaf rows there. If the number of deleted leaf rows is more than 30% of the total space occupied by the leafs, then rebuild should be useful. Particulary massive updates lead to increase of the deleted leaf rows

    Regards
    Boris

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Boris has written many good points.

    You can also read my article about index leaf block fragmentaion:
    http://www.oracleact.com/papers/inde...lock_frag.html

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