This content has been marked as final. Show 73 replies
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?
Senior Oracle DBA
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.
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.
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)
Senior Oracle DBA
Don't mind Sybrand Bakker (it may be a made-up name anyway), he is an anonymous phantom, not to mention a nasty racist:
As for facts, see proof here by Quest Software (Brian Peasland), citing Rich Niemiec:
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.
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.
Rebuilding an indexes is the subhject of greatWho claims indexes never benefit from a rebuild. My presentation clearly indicates those rare cases when it might.
debate, but it's a myth that Oracle indexes never
benefit from a rebuild.
Note "never" and "rarely" are not the same thing ...
- Index fast full scans may run faster after indexAs 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 ?
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
- Multi-block Index range scans will run faster whenFirstly, 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.
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
- Large-table full-table scans will run faster afterAnd what does this have to do with index rebuilds ?
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).
In an OracleWorld 2003 presentation titled “OracleUmmm, the *2003* presentation turns out to have been incorrect ... Are you claiming otherwise ?
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
In a paper titled “Metric Baselines: detecting andUmmm, not really. What does your article actually prove ?
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:
Hope this helps. . .
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 ?
"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.
"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.
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.
As for making up 'facts' I admit that I can learn something from 'Don Burleson'.
Senior Oracle DBA
Uh Huh, right. Speaking of ethics, where do you get off spewing hate speech?
when he stands corrected, thus showing he has no ethics.
"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."
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. . . .
Don Burleson is always making exxagarated claims, stating he has DBA experience even longer than Oracle existed
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 . . . .
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.
If you are unlucky enough to have a peculiar index whose access patterns are very unusual
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).
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.
Does a rebuild frequently help performance? No.
Does a rebuild sometimes help? Absolutely.
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.
Senior Oracle DBA