This discussion is archived
1 2 Previous Next 23 Replies Latest reply: Aug 23, 2007 1:36 PM by John Spencer Go to original post RSS
  • 15. Re: When I should rebuild the index
    Exadba Pro
    Currently Being Moderated
    When you rebuild an index, the physical effect is to reduce the size of the index (especially the leaf block count). This may result in three possible benefits:
    The optimiser calculations may produce a smaller value for the cost of using the index, so the optimiser may use the index in more execution plans.
    Typical queries that use the index may have to visit fewer index blocks, and therefore run more efficiently.
    Because the index is smaller, it may become a better survivor in the buffer cache LRU chain, so that blocks from the index are found in the buffer more frequently, and fewer blocks from other objects are flushed to allow index blocks to be reloaded. If this occurs, system-level I/O is reduced, resulting in a possible performance gain for everyone.
  • 16. Re: When I should rebuild the index
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    MAS,

    > When you rebuild an index, the physical effect is to
    reduce the size of the index (especially the leaf
    block count). This may result in three possible
    benefits:
    The optimiser calculations may produce a smaller
    value for the cost of using the index, so the
    optimiser may use the index in more execution plans.
    Typical queries that use the index may have to visit
    fewer index blocks, and therefore run more
    efficiently.
    Because the index is smaller, it may become a better
    survivor in the buffer cache LRU chain, so that
    blocks from the index are found in the buffer more
    frequently, and fewer blocks from other objects are
    flushed to allow index blocks to be reloaded. If this
    occurs, system-level I/O is reduced, resulting in a
    possible performance gain for everyone.

    You're right, especially on the words usage of "may", "if" and "possible". The improvement is maybe less than the rebuilding cost of indexes.
    That's why I wouldn't advise to rebuild index every day by automatic procedure. I would prefer to be sure about the target to touch.

    Nicolas.
  • 17. Re: When I should rebuild the index
    hans forbrich Oracle ACE Director
    Currently Being Moderated
    One of the headaches with the rebuild is: if the leaf block issue is created by the pattern of DML then (and especially during DML) the database is working harder in an effort to restore the 'inefficient' leaf pattern. SO you need to rebuild because the normal state is not efficient for some queries.

    This is well documented -see the referenced presentation from Mr. Foote.

    It would be much better to determine the actual cause of the problem and make a rational decision. For example, perhaps a materialized view would be better than maintaining that specific index. Or perhaps a FBI or reverse key index would be better.

    As it is, rebuilding indexes periodically make external consultants very happy - while the DBA is off doing rebuilds, the consultant can be billing for interesting DBA work. (And THAT is the justification for the Diagnostics Pack in 9i and higher!)
  • 18. Re: When I should rebuild the index
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    >>As it is, rebuilding indexes periodically make external consultants very happy - while the DBA is off doing rebuilds, the consultant can be billing for interesting DBA work
    I had never thought about that... thanks, now I understand some weird situations.

    Nicolas.
  • 19. Re: When I should rebuild the index
    454375 Newbie
    Currently Being Moderated
    After reading some documents and links provided in this thread i can understand that rebuilding index in a regular interval is a bad practice.

    The question is when should we rebuild a index??
    I could not come to a conclusion. I read the below points from some documents..

    1.
     select   substr(segment_name,1,50), segment_type, extents, bytes
                  from     dba_segments
                where    extents > 5
                   and segment_type = 'INDEX'
                   and owner = user   
            order by extents desc;
    If the above query returns any row ????

    2. select blevel from dba_indexes where index_name=<index name>
    If blevel is more than 3 ???

    3. select lf_rows, del_lf_rows from index_stats where name= <Index name>
    Looking at ratio of del_lf_rows and lf_rows???

    Or index rebuilding is not required at all???

    Please share your ideas on this.

    Regards,
    Satya
  • 20. Re: When I should rebuild the index
    293720 Newbie
    Currently Being Moderated
    As previously mentioned, go to Google, do a search on "index internals richard foote", and read the paper. It explains in detail, why index rebullds are almost never needed, and the few cases where they may be beneficial.

    None of the three metrics listed above, (lf_rows/del_lf_rows, blevel, number of extents) are valid metrics to determine if an index should be rebuilt.

    -Mark
  • 21. Re: When I should rebuild the index
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    I already linked this paper in Re: When I should rebuild the index of this thread.

    Nicolas.
  • 22. Re: When I should rebuild the index
    311441 Employee ACE
    Currently Being Moderated
    It's good to see that my presentation is still getting read and helping folks with questions such as these.

    It still amazes me though how many DBAs still consider rebuilding indexes every x number of days as an essential part of their job role.

    I would love to sit next to a DBA one day who after very careful analysis spots one of those evil indexes that has a level greater than 3, then rebuilds the thing while watching the hour glass as redo logs churn out log after log, only to find that the index still has a level greater than 3 afterwards. I of course would simply smile and ask if they're ready to try it again ...

    Then of course if the index were to reduce in height, you do have to wonder just how lightening fast the unique index scan will suddenly become because the index scan would have to read many leaf blocks, usually many tens of thousands of rows for any benefit to be noticeable to the application user. However most of these rebuilding rules of thumbs don't consider the actual benefit arising from such a rebuild.

    I'll hopefully be presenting a two day Oracle University seminar on all things Oracle Index Internals in some countries in EMEA early next year. Looks like it might be beneficial for a number of folk in this thread to attend !!

    Cheers

    Richard Foote
  • 23. Re: When I should rebuild the index
    John Spencer Oracle ACE
    Currently Being Moderated
    As far as I am concerned, there are two cases where an index re-build is required.

    1. After you have done an ALTER TABLE t MOVE for some valid reason
    2. After you have done ALTER INDEX i UNUSABLE and a massive dataload.

    In both these cases an index re-build is mandatory.

    There used to be a third case. If you have an index on, for example, a sequence generated number as a primary key. Another column in the table is a flag indicating record processed. Every x period of time you go out an delete all of the records where the processed flag is Y. Most, but not all, of the older (i.e lower sequence number) records are deleted, leaving many index leaf blocks with only one or two index entries in them. Since the new records being added (i.e. higher sequence numbers) cannot use these blocks because the values are wrong, a re-build used to be a good idea. Since (I believe) 9i, you can now do ALTER INDEX i COALESCE to compact those underfilled blocks into a much smaller number of blocks, so a re-build is no longer required.

    John
1 2 Previous Next