It seems numerous rumors and comments are being posted in the comments sections about things I may have once said, but upon research found were incorrect and retracted. Let me reiterate:
1. I have not found a case were an index rebuild, without re-arrangement of columns or change to the index structure, changes the clustering factor. Any prior advice I gave (nearly 2 years ago or so) to the contrary is incorrect.
2. I completely understand that the multiple block size feature in Oracle has limitations. I recommend them where the limitations are not a factor. That I haven't written a multi-page report on this fact with several more pages of complex proof, simply to prove what others have already done and is well documented in Oracle documentation I apologize for, I simply don't have the time to waste reinventing wheels.
3. The simple rules I mentioned in previous posts referred to the rules for database physical, PL/SQL and SQL design that are put forth in many of the Oak Table books, Oracle Press books and Rampant Books. Unfortunately, people don't follow these rules, the rules were made after the applications were designed, the people bought third party solutions that never follow any rules (good or bad), the people can't/won't change the code or a myriad set of other reasons that the good practices are not put in place. This is the real-world I dwell in and have to deal with.
4. I have never claimed that my advice on rebuilding indexes only applied to bitmaps, never have, never will. There are several situations where rebuilding or coalescing of indexes is beneficial and when I see a need for it, I will recommend it.
5. Why have I not attacked Don Burleson. Well, why have Howard and Tom never attacked Oracle for the many stupid things Oracle has done in the design and implementation of the Oracle product? For Oracle’s handling of support issues? For Oracle supports use of their own set of silver bullets? Also, Don has not attacked me. I am not confrontational by nature, I tend to let others have their opinions. When I can, I take Don to task in private for things which may be inaccurate, it is up to Don to change if he agrees with me. If I don't agree with everything Don advises, well, that is my opinion. It is rather like the TV, if you don't like what is being said, switch to another channel.
6. As to my opinions about asktom, yes, there are many good things there, there are also bad things as with any public forum. My major problem is that while it is supported by Oracle Corp. and supposedly moderated, I see things there that violate Oracles rules of conduct and should not be allowed on a moderated site. The examples are in the article, you can go to those links and see for yourself. Tom is excellent at providing proofs for his environment. But he rarely states "This proof should only be taken for proof in a similar environment to mine, your mileage may differ" and the very neofites you fear Don's and my advice will mislead, will be just as easily misled by correct proofs for an improper environment.
I attempt to avoid name-calling, bashing of others, and in general clouding issues with emotion charged clap-trap. In specific cases I have fought back when attacked by others and will continue to do so.
Oracle users tend to be a vocal lot. If advice causes problems it is generally well shouted from the roof tops. I task you Howard in presenting these shouts, if you cannot then you are but another chicken little proclaiming the sky is falling.
Any advice, when used incorrectly or without consideration is bad advice. Anyone who blindly follows suggestions, no matter what the source, without testing it in a test environment, deserves what they get.
If an automotive expert suggested I put water in my gas tank, I would question that advice, not blindly follow it. Compared to virtually all auto experts I am a neophyte in that area but I know not to blindly follow advice. When I go to a Doctor, an area I am definitely not trained in, if the advice sounds absurd I go to a second Doctor for their advice. There is no shortage of advice on Oracle, a quick check of Metalink, OTN and Google will find a plethora of opinions, demos, and advice on virtually any facet of Oracle administration you can imagine.
As I have said before, I have real work to do, I need to consolidate a list of 32 findings into something that can be accomplished with maximum return in 5 days. Not 2 weeks, not a month of study and testing, 5 days. Again, this is the real world.
Subscribe to:
Post Comments (Atom)
4 comments:
The original advice was from at least 2 years ago. Yes, the first retraction was 11 months ago.
I can see you are still as gracious as always.
I have publicly stated again and again, that I only suggest index rebuild into larger blocksizes when it would be beneficial to do so. Ditto on index rebuild and/or coalesces. Yet you seem stuck in a loop saying the same thing over and over again.
The quote was "and if anomalous behavior (i.e. reduction in CF) does occur, I will report back to the list with the scenario
and the statistics." since I haven't reported back it should be obvious that I haven't found it to occur. I have kept my word.
No one qualified the intial statements about index rebuilds as pertaining to only B-Tree, you and the other detractors seemed to indicate in your posts that all rebuilding of indexes was daft. I was simply pointing out there are other tpes of indexes other than B-Tree. Tom Kyte even says "but it is indexes on sequences that in general need watching (and the
del_lf_rows ratio so commonly used won't work as shown above!). when they sweep from the left to the right (like a queue -- put rows in, process them, delete
them) but you leave a couple behind -- it can get bad over time. A coalesce would fix them right up." and "So, some "right handed indexes" like that can benefit from a coalesce from time
to time, or maybe even a rebuild... " In the same thread I also stated: "I would like to apologize to folks who read my articles about rebuild of indexes
fixing cluster factors, I believe I recinded that advice where ever I could"
Give it a rest.
Why do I do what? Respond to unfair, outdated attacks? I gave the inital advice over two years ago, recinded it 11 months ago. By the quote you posted in asktom, I said if I found a case where it happened I would report it, since I haven't found a case, I haven't reported any, what part of this don't you understand?
In your and other experts postings you stated over and over again rebuilding ANY index was bad. In my inclusion of bitmap indexes I was simply pointing out that this doesn't apply to all indexes.
In thread "http://asktom.oracle.com/pls/ask/f?p=4950:8:3005220955240849280::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:2913600659112," Tom himself states that certain B-tree indexes benefit from rebuilding, I think his stated percentage of occurance is a bit low (how did he get that value? Looks a bit brown to me) but he does support the coalesce of sparse indexes and rebuilds of the so-called right-handed indexes.
Agan, let me state for the record, hopefully the last time, I do not advocate the rebuild of all indexes on a schedule. I advocate analysis of indexes to determine which, if any, will benefit from a rebuild, determine how they should be rebuilt (should parameters be changed to improve their structure to reduce required rebuilds?) and then only rebuild them as needed to maintain performance. I support the coalesce of sparse indexes, again, they may need to be examined to determine if they can be rebuilt with better storage to decrease the chances of becoming sparse again.
I advocate rebuild of bitmap indexes on an on going basis if they are used in high transaction environments.
What is unclear about any of this?
Mike, are you thinking of writing a report on the use of multiple block sizes in a database?
Howard,
Tom Kyte and several of the Oakies as well as many other experts support the limited rebuild or coalesce of indexes. These sources have also shown how to determine if an index may benefit using various trace methods. I am not going to cut and paste or waste time reinventing the wheel. I suggest you go there for your proofs.
I have stated, in the current blog, that I have found no case in which a rebuild, without changing the index structure, will cahnge the cf, how much clearer can I make it?
Sorry if you expect me to kiss your foot and beg forgiveness, ain't going to happen, not now, not ever. I made a mistake, I admitted it in many locations on line, even in my own blog. If that isn't good enough tough s***. I don't really care what you feel I should or shouldn't do.
Post a Comment