Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2004
    Posts
    84

    Unanswered: Rebuild Indexes?

    Hi,
    I have a cronjob to analyze schemas on a regular basis with CASCADE set to 'True' meaning statistics are gathered for INDEXES as well.
    Do I still need to have indexes rebuilt regularly.
    I am not very clear on this.

  2. #2
    Join Date
    Jan 2004
    Posts
    84
    Any inputs?

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    No, you don't need to rebuild indexes on a regular basis.

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    That doesn't mean he shouldn't validate the index structure once in a while to determine if any need rebuilding however.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    I agree with Duck. You SHOULD

  6. #6
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    oooppppssss.... You should validate the indexes looking the levels,
    deleted leafs, etc... Rebuild the indexes when they have "Browned" (deleted rows is a significate percentage of the total rows) or if the index has fragmented too much. I also tend to rebuild indexes if they go more than 3 levels.

    HTH
    Gregg

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by gbrabham
    oooppppssss.... You should validate the indexes looking the levels,
    deleted leafs, etc... Rebuild the indexes when they have "Browned" (deleted rows is a significate percentage of the total rows) or if the index has fragmented too much. I also tend to rebuild indexes if they go more than 3 levels.

    HTH
    Gregg
    Why? This isn't really my area of expertise, but Tom Kyte says:

    http://asktom.oracle.com/pls/ask/f?p...D:457820220286

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    Fair enough.
    But I only meant that you should validate your indexes and go from there.

    Not every day however like you mention.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  9. #9
    Join Date
    Dec 2003
    Posts
    47
    Hi, all !
    May I ask a related question ?
    When do I need to rebuild indexes ?

  10. #10
    Join Date
    Jan 2004
    Posts
    37
    when they fragmented a bit too much..
    this will adversely effect the performance of the query...

  11. #11
    Join Date
    Dec 2003
    Posts
    47
    Originally posted by chiranjib_s
    when they fragmented a bit too much..
    this will adversely effect the performance of the query...
    Thanks,
    And how can I detect it ?

  12. #12
    Join Date
    Jan 2004
    Posts
    37
    basically the performance of the queries on the pertaining tables will go down..Any table undergoing too many inserts or deletes will definiely have a fragmented index after a certain period of time..because the unused indexes keep on accumulating and the result is a fragmented
    index....(record deletes will not delete the corresponding index)
    so it is good practice to rebuild the indexes afeter a certain period of time...
    regards....

  13. #13
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    See Tom Kyte's differing view on this here:

    http://asktom.oracle.com/pls/ask/f?p...:3035290021641

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •