How to determine index rebuild?
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: How to determine index rebuild?

  1. #1
    Join Date
    Apr 2000
    Location
    Malmoe, Sweden
    Posts
    23
    How does one determine when itīs time to rebuild a index?
    Is it the clustering factor, leaf blocks or distinct keys that indicates this? Perhaps all of them?

    /Tuve

  2. #2
    Join Date
    Mar 2001
    Posts
    18
    I believe its fragmentation on the leafs that determine this, which you can check in index_stats, after VALIDATE INXDEX command. There should be no more than 10 per cent of deleted rows to existing rows



    -

  3. #3
    Join Date
    Apr 2000
    Location
    Malmoe, Sweden
    Posts
    23
    Well, perhaps that works but there must be a simpler way to determine this. If you have thousands of indexes you cant analyze xxx validate + INDEX_STATS on every single on of them. (well, you CAN technically but itīll take forever).
    Anybody got a neat script?

    /Tuve

  4. #4
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    If there have been a bulk deletes from a table, then it's necessary to rebuild the indexes.

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    tuvham why it would take forever?
    Unfortunately as far as I know this is the only way!

  6. #6
    Join Date
    May 2000
    Location
    Portsmouth, NH, USA
    Posts
    378

    Smile


    Pando,
    I think he is assuming you MANUALLY have to validate each index and then check the stats on them.

    I haven't tried it, but you could just write a script to validate and check each index and spool to a file or something.

    - Magnus

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    yea just use a dynamic sql script

  8. #8
    Join Date
    Apr 2000
    Location
    Malmoe, Sweden
    Posts
    23
    Iīm just thinking of the situation in which you are out of office, on a customer database, and donīt have your premade script to help you. Then you have to make a script to see the indexes that need rebuilding and then you have to run it, which probably takes some time (havenīt tried it but i can imagine). All this only for checking indexes. (think sga,dd,db buffer,tables,sql,parameters and so on)
    I remember someone saying that its time to rebuild indexes if the index is bigger than the table, but that is probably a pretty inprecise method.
    Maybe the best solution to get rid of the administration effort is to have them all rebuilt automatically on weekends.
    Or what do you think?
    /Tuve

  9. #9
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Send mail to marist89@excite.com with the subject heading of "SCRIPT: rebuild_candidates.sql" and I'll send you a package that I wrote to gather these statistics...
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  10. #10
    Join Date
    Jan 2001
    Posts
    126
    Hi Jeff,

    If you publish this under "Oracle Scripts", it will benefit everyone.

    Baliga

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