Analyze schema v/s Rebuild Indexes
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Analyze schema v/s Rebuild Indexes

  1. #1
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Hi,

    If one analyzes the schema then is rebuilding the indexes necessary.

    I mean analyze will take care of it anyways.

    Please let me know if I am right or wrong.

    Thanks
    Ronnie

  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    After you analyze, there is no need to rebuild indexes.

    On the other hand, after you rebuild indexes, you better analyze.


  3. #3
    Join Date
    Aug 2001
    Posts
    184
    analyze has nothin to do with rebuild indxs. if ur index has holes before analyze, it'll still have holes after.
    OCP DBA 8i
    ocpwannabe@yahoo.com
    -----------------------------
    When in doubt, pick 'C'.

  4. #4
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    I analyze my tables once a week every sunday night.

    basically there is no activity on the database after that till monday morning.

    I also have events scheduled in my OEM to alert me if any of the indexes need to be rebuild and I still get alerts immediatly after the whole schema has been anaysed.

    why is that.

    Ronnie

  5. #5
    Join Date
    Aug 2001
    Posts
    184
    Originally posted by ocpwannabe
    analyze has nothin to do with rebuild indxs. if ur index has holes before analyze, it'll still have holes after.
    OCP DBA 8i
    ocpwannabe@yahoo.com
    -----------------------------
    When in doubt, pick 'C'.

  6. #6
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    I think you are right ocpwannabe.

    what do the others have to say about it anyways.

    Ronnie

  7. #7
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    Periodic reindexing will recover space (especially if you update the base table frequently). Updates force the index to create new leaf blocks, and the old ones just don't get recycled or deleted.

    I've realized significant performance increases by rebuilding indexes. However, it's not something you want to do weekly. We do it once a year or as needed .. that seems to be enough.

    Analyzing your table will provide more accurate statistics for the cost based optimizer.

    -Ken

  8. #8
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Originally posted by KenEwald
    Periodic reindexing will recover space (especially if you update the base table frequently). Updates force the index to create new leaf blocks, and the old ones just don't get recycled or deleted.

    I've realized significant performance increases by rebuilding indexes. However, it's not something you want to do weekly. We do it once a year or as needed .. that seems to be enough.


    Analyzing your table will provide more accurate statistics for the cost based optimizer.

    -Ken
    What exactly is the difference between analyzing and rebuilding indexes.

    Dont both of them do the same job.

    Ronnie

  9. #9
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    As KenEwald pointed out, rebuilding your indexes reshuffles your leaf nodes and removes any leaf nodes that are empty. I usually rebuild indexes after a large number of rows has been deleted or the height gets really large.

    Analyze will compute statistics on your data to help the optimizer choose the best execution method. You should analyze your tables whenever the data distribution changes drastically.

    And no, they don't do the same job.
    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
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Do the Primary Keys also have to be rebuild like indexes as it is nothing but a unique index.

    Please correct me if I am wrong...
    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

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