11 Replies Latest reply: Oct 4, 2007 6:01 AM by 51034 RSS

    Rebuild multiple indexes...

    594763
      Hello all...

      Our 10g structure has around 180 indexes in it.
      While I know the...
      alter index <schema.index_name> rebuild online;
      ...command for rebuilding them one at a time, is there a way to en mass rebuild them all?

      Thanks!
        • 1. Re: Rebuild multiple indexes...
          546595
          may be you want to go through this

          reindex and analyze
          • 2. Re: Rebuild multiple indexes...
            APC
            is there a way to en mass rebuild them all?
            Rebuilding an index is something that normally needs to be done only occasionally, if ever. So why do you think you have a need to rebuild huge numbers of them?

            Cheers, APC

            blog: http://radiofreetooting.blogspot.com
            • 3. Re: Rebuild multiple indexes...
              547086
              Hi, you must rebuild the index only when it needed and always on maintenace windows, this never must be on operation time, what exactly is your problem??? What requirements do you have??

              Regards.
              • 4. Re: Rebuild multiple indexes...
                Madrid
                There is no need to rebuild all indexes massively as most probably performance gains would not be as amazing as you would expect, unless all indexes would be severly fragmented, with low key density and with big heights.

                There are two conditions that will let you know when an index has to be rebuilt, ( I ), when the height is bigger than 4 and ( II ) when the deleted keys vs total keys ratio is bigger than 20 %.

                On the link referred at the second post I further explain this.


                ~ Madrid
                • 5. Re: Rebuild multiple indexes...
                  594763
                  "So why do you think you have a need to rebuild huge numbers of them?"

                  Chalk it up to database ignorance.
                  We only have one app that makes use of one instance. Application response has been noticeably slowing as of late and documentation mentions rebuilding indexes as a housekeeping step.

                  With the link from devmiral and the conditions Madrid supplied, I can check some of the indexes on our largest tables and see which ones would benefit from the rebuild.



                  "Everybody is ignorant, only on different subjects" - Will Rogers
                  • 6. Re: Rebuild multiple indexes...
                    APC
                    Chalk it up to database ignorance.
                    Fair enough.

                    People who are new to Oracle will often believe anything they find on a site which sits high up the league of Google hits. The recommendation to routinely rebuild every index is mentioned on certain high-ranking websites as a panacea for poor query performance. Unfortunately, it rarely solves the problem.
                    "Everybody is ignorant, only on different subjects" - Will Rogers
                    The gaining of wisdom is about delineating the areas we know nothing about.

                    Cheers, APC

                    blog: http://radiofreetooting.blogspot.com
                    • 7. Re: Rebuild multiple indexes...
                      594763
                      ...my thanks to all for the help!
                      • 8. Re: Rebuild multiple indexes...
                        Richard Foote
                        Hi Madrid

                        What nonsense !!

                        You're basically recommending the locking of all your tables one at a time, locking some of them for potentially hours (the larger ones) for what ?

                        So you can determine rebuild criteria No 1, a rebuild criteria that is totally redundant as the height of an index is totally meaningless. It will rebuild indexes that don't need rebuilding and miss out on those that do.

                        Rebuilding indexes that start with the letters M,Y,T,H will achieve the same outcome, rebuilding indexes that don't require rebuilding and missing out on some that may benefit (and it would be a lot cheaper too).

                        What do you do when you rebuild an index with a height > 4 and it remains with a height greater than 4 ? Rebuild it again ? And again ? And Again, each time locking the table while you validate it's structure ?

                        Really ?

                        And rebuild criteria No 2 is pretty well useless as well as it firstly doesn't accurately calculate deleted space as often deleted index entries are cleaned out, it doesn't consider the fact the the space could potentially be reused in a timely manner and it doesn't consider the most important fact of all, that rebuilding the index might not improve performance at all (eg. index used for unique only scans, small to moderate range scans, etc ..)

                        What you're recommending is myth generating rubbish that would bring many databases down on it's knees during the running of the script while not achieving it's purpose.

                        Read and learn:

                        http://www.miracleas.dk/images/upload/Docs/Richard%20Foote.pdf

                        Read and learn ...

                        Cheers

                        Richard Foote
                        • 9. Re: Rebuild multiple indexes...
                          Jaffy
                          You cab use below query to find the index which are at invalid state and needed to rebuild.

                          select 'alter index' || ' ' || owner ||'.'||index_name|| ' ' || 'rebuild online' from dba_indexes where status not like '%VALID%' and owner='OWNER_NAME';

                          with the result, copy all the results and run at the sql prompt at a time and it will rebuild all at a time.

                          Jafar
                          • 10. Re: Rebuild multiple indexes...
                            51034
                            Oh sheesh. More silly oversimplifications without the necessary subtleites, qualifiers and riders.

                            when the deleted keys vs total keys ratio is bigger than 20%

                            Why 20%? Why not 15%? Why not 35%?

                            Oracle's performance tuning course material used to mention two different percentages in the same course book, after all!

                            And what about the fact that if you measure a deleted key ratio of 22% one minute and then perform a hundred randomised inserts, your ratio could well now be 4% without you having to lift a finger?

                            Oracle indexes re-use their deleted space all by themselves, in short. They don't need a rebuild to make them do it. If, because of the pattern of deletes and inserts on a table, a particular index has a persistently high level of deleted leaf rows, then fair enough: you might want that space back and maybe a rebuild is the only way to achieve that. But spouting a specific number as if its some cue for a Pavlovian reaction is just silly.

                            Jonathan Lewis has, in his time, demolished the index height issue several times, too.

                            And Brian Peasland's article to which your earlier link referred is well out of date.
                            • 11. Re: Rebuild multiple indexes...
                              51034
                              I do find it a little disturbing that Latin America's first Oracle Certified Master is busy peddling this sort of stuff. I had hoped the OCM programme would mean those holding the qualification would know the difference between myth and fact but apparently not.

                              I put it down to the need to get to his 5000th post, myself. Presumably as quickly as possible and with not too close a look at the quality of the material written to get there.