1 2 3 4 5 Previous Next 73 Replies Latest reply: Nov 8, 2007 11:47 PM by damorgan Go to original post RSS
      • 15. Re: When to rebuild indexes
        153119
        Actually it is a myth they benefit from rebuilding.
        It has been demonstrated here many times.
        Did you ever read the Richard Foote paper?
        Why do you continue to spread this advice (also refer to your advice on BCHR, multiple blocksizes and solid state disks), when you have been corrected so many times?

        --
        Sybrand Bakker
        Senior Oracle DBA
        • 16. Re: When to rebuild indexes
          Aman....
          hi vivek,
          you can reach me at amanalchemist at yahoo dot com.
          Cheers,
          Aman....
          • 17. Re: When to rebuild indexes
            matt_glover
            In the interest of keeping this debate apples to apples, the advice here seems very anecdotal compared to the cold, hard facts of Mr. Foote's presentation. Surely somebody that has written "Definitive Reference" guides for Oracle products has done extensive testing to show that indexes do benefit from rebuilds.

            I'd be interested in seeing the data behind your claims as this seems to be one of the more active Oracle debates going on.
            • 18. Re: When to rebuild indexes
              51034
              I mean, I know Don and I aren't going to agree on whether to rebuild indexes routinely or not, but some of this stuff is just bizarre.

              Multi-block Index range scans will run faster when the data blocks are arranged in index-key order and when the data blocks have a high number of row entries (as evidenced by clustering_factor in dba_indexes).

              Translation: if you reorganize your table index access to it will be quicker. True -so long as your table only has one index, otherwise ordering the table in one index order will slow down access to it from the other index(es). And what has table re-organisation got to do with rebuilding indexes anyway?

              Large-table full-table scans will run faster after reorganization when the table has excessive chained or relocated rows, or low block density after massive DML (updates and deletes).

              This one is also sort-of true: reorganise your tables to eliminate migrated rows and index access to the data will perform faster (full table scans don't benefit, actually, because the point of migrated rows is that the index points to the wrong place: it's only index access that is therefore affected by them). But what table reorganisations have to do with a question about rebuilding indexes, I don't know. As an aside, since you cannot eliminate row chaining by re-organising a table unless you move it into large blocksize tablespace, we see this advice is just a coded way of banging on Don's favourite drum of the day: use multiple blocksizes in the one database. The fallaciousness of that advice has been discussed at length elsewhere on these forums.

              Table updates will run faster after reorganizations when the table has unbalanced freelists (with multi-freelist, freelist groups table in the obsolete dictionary-managed tablespaces only).

              Oh dear. Having been shown years ago that freelists cannot become unbalanced, it would seem Don never learns. Freelist GROUPS are not the same thing as freelists: there are two separate bits of syntax to deal with them, after all. Not mentioned here is the fact that freelist groups are only of relevance to RAC environments anyway. Of course, no-one in their right mind would run a RAC these days using anything other than ASSM, at which point the observation, even if it was clear on the distinction between freelists and freelist groups, becomes irrelevant.

              And, of course, it's completely irrelevant to the question of performing index rebuilds anyway!

              Indeed, why any of these points would help answer a person's question about when to rebuild indexes, I'm not sure. Perhaps Don happened to have a spare five minutes today and thought that pasting in a random piece of work he did three years ago would pass the time. His post is, after all, almost a verbatim transcript of this: http://tinyurl.com/yt29ma ...and that was written 2.6 years ago! It was also effectively demolished 2.6 years ago by Tom Kyte, amongst others.

              Answer to the original poster: Almost never. Don't buy into myths about lf_rows/del_lf_rows ratios. Don't buy into the nonsense about '10g will automatically rebuild indexes for you' (it doesn't). You have to rebuild an index if you move the table; if you do particular types of partition DDL; if you decide the index design (column order etc) is wrong. Otherwise, leave well alone. If you are unlucky enough to have a peculiar index whose access patterns are very unusual, you may indeed have to do an occasional, periodic rebuild -but the driver for doing that would be a measurable slow-down in query performance or a change in execution plan or an increase in some waits.
              • 19. Re: When to rebuild indexes
                153119
                Those figures will never show up, because they don't exist.
                Apart from that: rebuilding indexes is just symptom-fighting, it is not a long-term solution. If you are performing massive deletes you would either best truncate the table, or just drop the indexes so you cut down on redo during reorganization.
                The only valid reason for rebuilding is you are running out of disk space and customer refuses to buy extra disks (whether they are SSD or not is a completely different matter)

                --
                Sybrand Bakker
                Senior Oracle DBA
                • 20. Re: When to rebuild indexes
                  108476
                  Hi Matt,

                  Don't mind Sybrand Bakker (it may be a made-up name anyway), he is an anonymous phantom, not to mention a nasty racist:

                  http://forums.oracle.com/forums/thread.jspa?threadID=573960&start=15&tstart=0

                  As for facts, see proof here by Quest Software (Brian Peasland), citing Rich Niemiec:

                  http://www.quest-pipelines.com/newsletter-v2/rebuild.htm

                  There are two rules of thumb to help determine if the index needs to be rebuilt. If it is determined that the index needs to be rebuilt, this can easily be accomplished by the ALTER INDEX REBUILD command. Although not necessarily recommended, this command could be executed during normal operating hours. Rebuilding the index uses the existing index as a basis. The alternative is to drop and re-create the index. Creating an index uses the base table as its data source that needs to put a lock on the table. The index is also unavailable during creation.

                  First rule of thumb is if the index has height greater than four, rebuild the index. For most indexes, the height of the index will be quite low, i.e. one or two. I have seen an index on a 3 million-row table that had height three. An index with height greater than four may need to be rebuilt as this might indicate a skewed tree structure. This can lead to unnecessary database block reads of the index. It is helpful to know the data structure for the table and index. Most times, the index height should be two or less, but there are exceptions.

                  The second rule of thumb is that the deleted leaf rows should be less than 20% of the total number of leaf rows. An excessive number of deleted leaf rows indicates that a high number of deletes or updates have occurred to the index column(s). The index should be rebuilt to better balance the tree. The INDEX_STATS table can be queried to determine if there are excessive deleted leaf rows in relation to the total number of leaf rows.
                  • 21. Re: When to rebuild indexes
                    51034
                    As for facts, see proof here by Quest Software (Brian Peasland), citing Rich Niemiec:

                    That article is dated 2000 and Brian Peasland told me in about 2004 that he was grateful for certain clarifications I was able to offer him and he'd changed his opinion on index rebuilds accordingly. If that's the best you can do as a citation of 'fact', you're on thin ice. Well, non-existant ice, really.

                    Your first rule of thumb has been addressed by Richard in his presentation. After a rebuild, it is extremely unlikely the height will change. And even if it does, it won't make more than a few percentage difference to most access to data made via the index.

                    Your second rule of thumb is inadequate. If the ratio is 20% and stays there for an extended period, then a rebuild might be of benefit. Read as it is, it implies that deleted leaf space is there for ever -when randomised inserts, left to themselves, will actually reduce that percentage anyway.

                    Meanwhile, I hope you're not suggesting in your first main paragraph that "creating an index...needs to lock the table" but that rebuilds don't, because that would be just wrong, as you know. Even a 'rebuild online' actually takes a table lock twice... and if it can't get it, everyone trying to do DML on the table queues up behind it whilst it waits.

                    Never mind, of course, that rebuilds generate huge amounts of redo, swamp your buffer cache ...and probably achieve nothing concrete at the end of it all.

                    I find it hard to understand why you persist in peddling these fairy stories in the face of all available evidence and technical fact such as is neatly -and exhaustively- documented in Richard's presentation linked to at the start of this thread. How you can read that and still post this beats me: a clearer case of 'head in sand' I have yet to see.
                    • 22. Re: When to rebuild indexes
                      311441
                      Hi Don
                      Rebuilding an indexes is the subhject of great
                      debate, but it's a myth that Oracle indexes never
                      benefit from a rebuild.
                      Who claims indexes never benefit from a rebuild. My presentation clearly indicates those rare cases when it might.

                      Note "never" and "rarely" are not the same thing ...
                      - Index fast full scans may run faster after index
                      reorganization whenever the “density” of the index
                      entries becomes greater. In other words, it takes
                      less time to read 100,000 entries from a 100 block
                      index than reading the entries from a 500 block
                      index.
                      As clearly demostrated in my presentation. However, why lock each and every table in a database to validate every index to determine a rebuild criteria that doesn't actually identify these rare specific cases. An index that's poorly fragmented and impacts performance ?

                      >
                      - Multi-block Index range scans will run faster when
                      the data blocks are arranged in index-key order and
                      when the data blocks have a high number of row
                      entries (as evidenced by clustering_factor in
                      dba_indexes).
                      Firstly, your terminology is confusing here, index range scans don't perform muliblock reads. Secondly, as highlighted in the presentation, what about the impact on other indexes if you re-order the table ? A table can only have the one order, all other indexes will have their CFs impacted, possibly for the worse.

                      >
                      - Large-table full-table scans will run faster after
                      reorganization when the table has excessive chained
                      or relocated rows, or low block density after massive
                      DML (updates and deletes).

                      - Table updates will run faster after reorganizations
                      when the table has unbalanced freelists (with
                      multi-freelist, freelist groups table in the obsolete
                      dictionary-managed tablespaces only).
                      And what does this have to do with index rebuilds ?
                      In an OracleWorld 2003 presentation titled “Oracle
                      Database 10g: The Self-Managing Database” by Sushil
                      Kumar of Oracle Corporation, Kumar states that the
                      new Automatic Maintenance Tasks (AMT) Oracle10g
                      feature will "automatically detect and re-build
                      sub-optimal indexes.“
                      Ummm, the *2003* presentation turns out to have been incorrect ... Are you claiming otherwise ?
                      In a paper titled “Metric Baselines: detecting and
                      explaining performance events in em 10gr2” (Presented
                      at the RMOUG 2005 Training Days), John Beresniewicz
                      of Oracle Corporation notes that the use of
                      "baselines to capture and adapt thresholds to
                      expected time-dependent workload variations" is a
                      core feature of the next release of Oracle.

                      I have more notes here:

                      http://www.dba-oracle.com/oracle_tips_proof_table_inde
                      x_rebuilding.htm


                      Hope this helps. . .
                      Ummm, not really. What does your article actually prove ?

                      Cheers

                      Richard
                      • 23. Re: When to rebuild indexes
                        311441
                        Hi Don

                        You do know that Brian now refutes some of the points in that article don't you ?

                        And the presentation clearly demonstrates why those two rules of thumb are invalid.

                        Question Don, if you rebuild an index based on a rebuild criteria that states every index with a height of (say) 3 gets rebuild and afterwards it stays at the same height, do you rebuild it again and again and again and again and again and again and again and again and again and again and again and again and again and again and again and again and again and again and again and again and again and again and again and again and again and again and again and again and again and again and again and again and again and again and again and again and again and again and again ...

                        At what point do you decide that the rebuild criteria is incorrect ?

                        Cheers

                        Richard
                        • 24. Re: When to rebuild indexes
                          damorgan
                          You wrote:
                          "Rebuilding an indexes is the subhject of great debate, but it's a myth that Oracle indexes never benefit from a rebuild."

                          There is no such myth. The myth is that they often require rebuilding and this myth, unfortunately, has been perpetuated by people who claim to be DBA experts and have never actually done the lab work to verify their assumptions.

                          You wrote:
                          "it takes less time to read 100,000 entries from a 100 block index than reading the entries from a 500 block index."

                          It also takes less time to walk one mile than two. But that analogy has nothing to do with the rationalizations used by those that advocate index rebuilds absent metrics. If the index will return to the same state, 100K entries in 500 blocks in a few hours or days do you think the overhead of the continual rebuilds has no system impact?

                          Reconsider your advice. It sounds good. But it leads to bad practices. I have rarely seen a system that required any index rebuild. And those that did ... the fix was to attack the root cause ... not treat the symptom.
                          • 25. Re: When to rebuild indexes
                            153119
                            Don't mind Don Burleson, he's about the only 'savvy professional' here flaming his colleagues, when he stands corrected, thus showing he has no ethics.

                            Apart from that, while 'Sybrand Bakker' is not a made up name, Don Burleson is always making exxagarated claims, stating he has DBA experience even longer than Oracle existed, and claiming he wrote the 'definitive tuning guide', continually spamming this fact in this forum, where the 'Terms of Use' state this is not allowed.
                            As for making up 'facts' I admit that I can learn something from 'Don Burleson'.

                            --
                            Sybrand Bakker
                            Senior Oracle DBA
                            • 26. Re: When to rebuild indexes
                              153119
                              Everyone should best ignore Don Burleson.
                              He will never ever admit he stands corrected.
                              Too bad so many people follow up his oftend incorrect 'Silver Bullets' and 'Rules of Thumb' and never learn to solve a problem, instead of throwing hardware.

                              --
                              Sybrand Bakker
                              Senior Oracle DBA
                              • 27. Re: When to rebuild indexes
                                108476
                                when he stands corrected, thus showing he has no ethics.
                                Uh Huh, right. Speaking of ethics, where do you get off spewing hate speech?

                                "And, yes, they are usually Asian, and, yes, they are usually 100 percent incompetent. If there are any competent Asians, I failed to meet them."
                                Don Burleson is always making exxagarated claims, stating he has DBA experience even longer than Oracle existed
                                No puffery there. I was a full-time working DBA when Oracle was still a gleam in Larry's eye. I guess that in your mypoic world, databases did not exist before Oracle. . . .
                                • 28. Re: When to rebuild indexes
                                  108476
                                  Hi Howard,

                                  Weird, I find myself agreeing with you on many points, and I removed all references to "unbalanced freelists". I know that you think I made it up, but "sparse tables" were a huge issue in the early days of Oracle HA, especially with ERP's like SAP which perform huge batch loads. Regardless, I agree, it's not a big issue today . . . .
                                  If you are unlucky enough to have a peculiar index whose access patterns are very unusual
                                  OK, but it's high DML activity that seems to be the main factor. In large OLTP databases that do batch updates, it's not uncommon to see thousands of half-empty index blocks. The "sparse" index refers to "almost empty" blocks, not those on the freelist.
                                  but the driver for doing that would be a measurable slow-down in query performance or a change in execution plan or an increase in some waits.
                                  Good point. I only recommend rebuilding for databases that sit idle (i.e. Sunday's). Remember, servers depreciate fast, regardless of use, and batch jobs can be created that rebuild indexes with absolute safety (moving them between tablespaces).

                                  Does a rebuild frequently help performance? No.

                                  Does a rebuild sometimes help? Absolutely.
                                  • 29. Re: When to rebuild indexes
                                    153119
                                    Don Burleson,

                                    Please stop your unprofessional drivel.
                                    Your flames against Tom Kyte and Jonathan Lewis are floating around on the Internet.
                                    It is a known fact you will always shoot from the hip and try to kill the messenger, when you are exposed as a fraud, as you have been so often by Tom Kyte and Jonathan Lewis.
                                    Do you want me to repost them here?
                                    I will if you continue to flame in this fashion.

                                    --
                                    Sybrand Bakker
                                    Senior Oracle DBA