Rebuilding Indexes Every Sunday Afternoon !! October 28, 2009
Posted by Richard Foote in Index Rebuild, Oracle Indexes, Richard's Musings.trackback
I just had to share this amusing article on “Scheduling Oracle Index Rebuilding“.
Regular readers of this blog can no doubt pick the various inaccuracies and strawman arguments with this article. It’s was nice however to reminisce and take a stroll down memory lane back to the 80’s and 90’s when many DBAs indeed did spend every Sunday afternoon rebuilding indexes and the such during so-called maintainance windows.
However, if you’re like me and now work on sites where there is no such Sunday maintainance window because your users actually require and demand 24 x 7 access to their applications, because organisations still want to sell their products and services online during Sunday afternoons, because governments still want border control applications functioning on Sunday afternoons, because consumers still want access to their bank savings on Sunday afternoons, because police still need to access critical information about possible criminal activities on Sunday afternoons, because airlines still want to fly aircraft on Sunday afternoons, etc. etc. etc. etc. etc. etc. etc. etc. etc. etc. etc. etc. etc. etc. etc. etc. etc. etc. etc. etc. etc. etc. etc. etc. ……..
then perhaps the article may not be that useful to you afterall.
Of course, maybe you do have a maintainance window of some description but then have that other really annoying problem associated with modern databases, of them being big, really really big and so more difficult to maintain. Back in the 80’s and 90’s, databases were relatively small by todays standards and it was conceivable for indexes to be rebuilt with little or no thought. There was generally little point, but at least you could get away with it. However, if you’re like me and you have databases with indexes that total in the many terabytes, it just isn’t feasible or practical to rebuild all such indexes, even if you wanted to.
No. If you do have a maintainance window, you may want to make sure the valuable time is spent well, on activities that actually make a difference.
Of course you may also look after databases like I do where the percentage of indexes that actually benefit from a rebuild is not around 30% as suggested by the article but significantly below 1%. Therefore rebuilding 99+% of indexes for no practical gain or purpose may not be considered a “wise” use of maintainance opportunities.
One thing this article did make me ponder though is the number of database sites out there where the DBAs demand and force their businesses and associated end-users into having forced downtimes, into having their applications and business processes impacted and made unavailable (say) every Sunday morning, not because it’s actually necessary or because there’s a business or technical benefit but just because their DBAs say or think they still need to follow the processes and maintainance activities of the 80’s and 90’s. Interesting question that …
One last point I would make with this article (which BTW will change and be modified in the future, I can guarantee that, just save a copy and see). Why would you bother with validating the structure of all indexes if you plan to rebuild them all anyways ? And who is the more inept ? Someone in the Netherlands who attempts to run a script that attempts to validate structure of all indexes or someone who writes a script to validate structure indexes with an ALTER INDEX command 😉
Personally, I spend every Sunday afternoon relaxing and enjoying the weekend, playing sport, perhaps doing a bit of gardening and on a lovely sunny day, just sitting back with family and friends enjoying a barbecue and a few cold beers.
Meanwhile, all my database applications just run on happily along with no impact on business activities at all …
Richard Foote's Oracle Blog 
You are so wrong, Richard.
Didn’t you read that part of the article that reads “the latest consensus on index rebuilding”? Since it is ‘latest’ and it is a ‘consensus’, that means you are … outdated and … outnumbered. :p
He even has a ‘common definition for ‘unbalanced-index”. *cough*
By the way, I have saved a copy of that page, as per your instruction. Am waiting for the article to be ‘updated silently’ … :p
LikeLike
Hi Andy
I just love all the inconsistencies, they just make it such a fun read.
It all starts so well with:
“Important 2009 update: The rules for identification of candidates for index rebuilding are changing.”
It makes one wonder what has changed since 2008 as he then goes on to say:
“I am a production DBA and I’ve been rebuilding my indexes every Sunday afternoon for decades” !!
It’s the latest consensus of just one me thinks 😉
Love it, just hope future changes don’t ruin it too much 😉
LikeLike
I propose all the web sites be built on Oracle with flashback turned on so that we all have past references at our fingertips …. :p
LikeLike
Heh Heh.
You should know by now, that guy only talks garbage. It’s amazing that he still gets away with it.
I have to confess, I don’t rebuild my indexes on Sunday afternoons… or the rest of the week. Can I still call myself a DBA? Please? 🙂
LikeLike
Hi Fred
You would think in all those many decades there would have been at least one site that would demand all indexes be rebuilt on a Tuesday ???
😉
LikeLike
Too bad I never had a chance to experience such nice index-rebuilding Sunday. All of those huge DB I have supported are built on IOT and very little real indexes to play with. Maybe Burleson can lend me his production DB for a rebuild exercise ….
LikeLike
Not tuesday, but I used to work for a company with a strict rule that the latest point in time during the week for any releases/upgrades or maintanance was thursday afternoon, primarily because no one wanted to spend all weekend at the office if something went wrong , and most of our activity was at the weekend anyway.
Anyway I’m not qualified enough to rebuild indexes as my (almost) degree in physics doesn’t qualify me as a database professional.
LikeLike
but then he did not even need any big downtime or maintenance windo if he rebuilds all the index with online option because he is “World’s top Oracle DBA” to adminster any Oracle database lower than Enterprise Edition would be demaning to his skills 😉 🙂 🙂
LikeLike
Hi Raj
Remembering of course that there can still be significant and performance crippling locking implications of rebuilding indexes, even with the online enterprise edition option.
You have to wait until 11g for significant improvements in this regard and 11g hasn’t unfortunately been available for “decades” …
LikeLike
The interesting part is MR Burleson never mentioned about the amount of redo generated during this operation. It was madness amount of redo for 1TB database when we had to do it at my old company because of the managers who reads Mr Burleson.
Time consuming
Space consuming
For what ?
Nothing.
If Mr Burleson do only believe real production systems not the test ones, I can proudly say that as a DBA of a very very important and big production system after I convinced my managers that, this is wrong and complete bollocks, we never rebuild %99.9 of our indexes again.
LikeLike
Hi Coskan
That’s a very important point. Not only does one have to manage the redo stream and somehow ensure recoverability is not comprosed (having several TB archive destinations is somewhat tricky for many sites) but if you have standby databases, the subsequent load on the network and keeping them up to date as well can be problematic to say the very least.
Generating redo is extremely expensive and managing tons of redo is a right pain.
And yet the article says:
“Rebuilding indexes in a schedule is a DBA best practice because the cost of rebuilding indexes is zero.”
Yeah right, of course it is …
LikeLike
Well, unless using FORCE_LOGGING, then one could rebuild NOLOGGING, surely? No redo (to speak of) then.
This thread’s turned into a slagging-off of DKB… AGAIN!!!
FFS: get the BBQ lit, open a tube and chill!
LikeLike
Hi Andy
I’ve decided not to give away too many of the blatantly wrong technical details in the article. No doubt some of the mistakes will silently disappear over time but as it stands, it’s a nice example of misunderstanding key indexing concepts.
LikeLike
In my view, a very important point of this whole “We have to rebuild these indexes regulary – therefore need maintenance time!”-thing is, that it makes Oracle Databases really look bad.
“Oh Man, this d*** Database needs that much downtime! Why do we pay that much money for it?”
When in fact that is more than often a complete waste of precious maintenance time and effort. I really do appreciate that you and the other Oak Table members spread the knowledge that it aint so! Similar could be said about repeatedly reorganization of tables also 🙂
Kind regards
Uwe
LikeLike
Thanks Uwe
Indeed with tables reorgs as well.
I think people within Oracle are very conscious of the fact false suggestions such as all indexes should be rebuilt weekly can impact the perception of the value of the Oracle database software. I’m sure it’s one of the reasons why my index seminar has been so successful. People, be they DBAs or Developers or Solution Architects or whoever need a better understanding of how to actually proceed with index maintenance and usage, because the option given in the article is simply a non-option for most sites these days.
In the end , it comes down to a misunderstanding of how indexes actually work and function within Oracle. This article with the nonsense regarding freelists for example is a classic case of where a misunderstanding of index behaviour can lead to an inappropriate course of action.
Some applies to medicine, to mechanics, to law, etc. Don’t understand what is really going on, make the wrong diagnosis, make the wrong decision and you end up potentially doing the wrong thing, maybe even making things worse, not better.
Once you begin to really understand Oracle indexes, one begins to really understand those scenarios when a rebuild or coalesce might actually be beneficial. However, get the foundations wrong and the whole argument above just falls to pieces.
LikeLike
Richard,
regarding your index semiar: Incidentally, I am in the same office as the Oracle University EMEA Marketing Director. We talked about seminars recently, when I mentioned you as one with a very high reputation inside the Oracle Community, especially when it comes to indexes. She then rolled her eyes and said that your charge is also very high 🙂 And there was apparently a little misunderstanding, whether you will be paid that amount you charged in Dollars or in Pound – which is a big difference… Anyway, I hope to be able to attend your seminar when you are in Germany! I will be the guy in black (first row), asking all the annoying nitpicking questions 🙂
LikeLike
This is a feedback from OTN forum discussion. It’s pity that Mr Burleson cut only very very tiny part of it as a example that other people do that same – but Hemant AFAIR was writing about special case which can be included in Richard 1 % of indexes which required that kind of operation.
LikeLike
Hi Marcin
I think people, including those that run OTN, are really beginning to get tired of the Burleson nonsense that continually ruins so many of the OTN discussions.
I remember reading a recent discussion involving Hemant and he certainly appeared to have a scenario where a rebuild/coalesce might be beneficial. If you have an example where it does indeed help, great, go for it. However, understanding why and whether the issue could be avoided is always a good course of action to take as well.
LikeLike
This is a point.
If you know why you have to rebuild your index and you can’t do anything else due to 3rd party code – do it.
In my opinion Hemant should ask Mr Burleson to remove this quotation from his article – this is something which has been cut off from whole discussion without any additional comments.
LikeLike
Richard,
I didn’t know that Burleson had quoted my posting on forums. I’ve updated my forums posting with a clarification.
That “I have scheduled weekly rebuild” is for the indexes on a specific table (and I have found only 3 such tables whose indexes might behave in that manner). So it is less than the 1% of cases.
Other likely needs for a Rebuild are also posted on my blog. So I have added links to those posts in my update on forums.
Hemant K Chitale
LikeLike
Since I updated my forums posting, I see that Mr Burleson, too, has updated his article. He adds “However. Hemant notes that scheduling an index rebuild was a rare case for him and the root cause for the scheduled rebuild was an unresolved Oracle bug” and then quotes me “”I do not recommend rebuilding of ALL indexes weekly. This was a specific table ….”
So I’ll give him credit for that !
LikeLike
Hi Hermant
Yes, he updates things a lot.
I suspect when he realises that your comments and link now kinda matches those “Neophytes claim that 99% of index rebuilding is a waste of time”, as you indeed only schedule rebuilds of less that 1% of your indexes, the link might just be deleted altogether.
Fingers crossed your name is removed eventually.
Interestingly, all the technical errors mentioned in the article as reasons for rebuilding indexes have yet to be addressed.
Perhaps because I purposefully haven’t mentioned them 😉
It’s a classic example of how misunderstanding how things work leads to incorrect assumptions which in turn leads to bad DBA practices. I don’t want to giveaway too much and lose my lovely example altogether 😉
The new introduction in the article is just brilliant though 🙂
LikeLike
Richard,
Do you really need to waste time pointing out these issues. Get back to the good stuff! 🙂
LikeLike
Hi Brian
I don’t think pointing out these issues is necessarily a total waste of time. One could certainly argue there are more inertesting things to write since most people would react the same way upon reading the article, that’s it’s a hillarious piece of nonsense. But there are always the newbies to consider who might read such suggestions and think it actually is a “DBA best practice” to blindly schedule index rebuilds or that “the cost of rebuilding indexes is zero”.
You know it’s not true, so do I, so do most DBAs but it never hurts to highlight the obvious every now and then 🙂
LikeLike
I just read an OTN thread where this Burleson goes on and on abusing Jonathan Lewis, calling Lewis a godless heathen who quotes Carl Sagan, that he has no experience and hence no right to call himself an expert and all sorts of nuttiness.
A very strange man..
But the guy seems to be good in Google optimization. Whenever you query something related to Oracle his website (or one of his numerous websites) pops up at the first page, sometimes at the top of first page on Google.
He should stick to Google optimization and stop wandering into Oracle world like a drunken elephant damaging everything on it’s path and also misleading most junior DBAs. Many junior DBAs probably read him and believe what he writes.
But once they become veterans of 1 or 2 years in Oracle , I doubt they take him seriously.
LikeLike
Hi Ajit
Some of the things on the OTN forums lately have been simply disgraceful. Absolutely shameful.
I believe and hope that action will finally be taken, let’s hope so anyways.
A little fact for you. Every single DBA and Oracle professional who I know and respect and there are a great many of them around the world now whom I’ve had the pleasure to work with or met in person or via a computer link, every single one of them bar none, have the same opinion as me on “certain matters”.
Our views, bar none, are totally consistent …
LikeLike
I’m with Brian.
(and with Coskan on redo, and with Uwe on effort and management-bs and quality. But mainly with Brian.. tired of this yes-no game – get on with the important stuff)
Mud sticks, even to those who point out where the mud comes from.
Btw: sorry to have to miss your seminar in Paris, but I’m not in the neighborhood. Some other time perhaps.
LikeLike
Hi Piet
Sometimes one has to say the same thing again and again and again and highlight the same errors and inconsistancies again and again and again for the message to get through to those a little slower on the uptake than you and I 😉
Sorry you can’t make Paris, I believe there’s going to be quite a crowd. I’m not sure when I’ll get the chance to run the seminar again in EMEA ?
LikeLike
The good news is this: There may be a time where indexes on some databases are no longer required. Then these folks will have to find something else to waste their time on.
http://www.oracle.com/us/corporate/press/036688
LikeLike
Hi Greg
I heard the same message when I attended a Exadata presentation lately. Hopefully, I’ll be long retired before indexes finally die out else I’ll just have to talk about David Bowie a lot more 🙂
LikeLike
Instead of all the bashing (which I do enjoy some), I have a serious question.
It’s one thing to rebuild an existing index but Burlson says,
“DBA – The DBA will apply patches, reanalyze optimizer statistics, add histograms and reorganize tables and rebuild indexes.
It’s malfeasance for a DBA to not use the downtime windows to perform database administration tasks.”
Why would one just slap on histograms to a production database? Couldn’t there be serious repercussions to explain plans. Why would one consider that an “administrative” task? Typical “administrative”, in this sense, mean perfunctory, not investigatory problem solving.
Also, isn’t there real risk to a rebuilt index causing delays in inserts? the empty space eliminated may be needed again and cause an insert to wait for a block split? He says it’s ok because there’s either some improve or no improvement but never anything bad. That’s not completely true. Any change, every change could make things better, the same, or worse. Are there really any production changes with zero risk?
I don’t pretend to be an expert but these are just my observations.
LikeLike
@Mark
Tom Kyte did a great presentation on Reorgs. Download it here.
Take note to slide 27 about the increased redo created after rebuilding indexes. There certainly are risks associated with any change, even one as seemingly innocent as index rebuilds.
LikeLike
Exactly my point. I made a totally innocuous change, added a view, broke a process. How could a view that didn’t exist and now does break something.
USER_TAB_COLUMNS contains columns for views, not just tables as the name suggests. Add a view when you’re expecting only tables and it breaks… yes, yes, should join to user_tables. We know that…….. now.
LikeLike
@Greg
Thanks for posting the link to that presentation. It was an excellent presentation. Concise. Simple. Easy to understand.
Thanks again!
LikeLike
Hi Mark
You wouldn’t slam on a histogram of course, you would usually determine a possible requirement and test the implementation thoroughly.
All changes have an implied risk. Hopefully, the benefits and possible gain, in combination with proper testing ensures such benefits outweigh the possible risks.
But certainly, you rebuild an index and things can possibly change and not necessarily for the best. As mentioned by other, the simple fact of having new stats on the index as a result of the rebuild could on its own, make matters better or worse.
There are certainly possible overheads in an index expanding out again due to subsequent block splits following a rebuild. The risk might be minimal but they’re not necessarily zero either.
LikeLike
I’m quite impressed with an article that in paragraph 1 can link to itself. I like recursion me. (Now wash your hands notices anyone). The thing I’ve never got about the ‘zero cost’ activity is why you would be arranging to do it in a maintenance window at all. Maintenance windows are for necessary but costly activities (rather than unnecessary but costless ones!).
Oh and the article has already changed since it’s been published, and refuted, previously.
LikeLike
Hi Niall
Just the downtime itself, regardless of anything else, generally comes at a huge cost and inconvenience to the business. there just ain’t that many organisations that have a Sunday afternoon each week to spare.
Therefore, if you don’t have the downtime opportunity and you really really believe in rebuilding all your indexes, guess what, you make the business suffer a downtime regardless.
That’s when things get really sad and unfortunate.
BTW, I still haven’t got over the mental picture you painted with your recent email !!
LikeLike
Hi,
Indeed, that’s why you want maintenance windows to be planned (so it isn’t an unexpected cost) and infrequent. In addition when I was responsible for managing E-Business suite (for which the answer to every problem is a new patch 😦 ) we arranged downtime windows once a month, and then had a system of notifications if we were actually going to use them. This was rather better than the previous ‘system’ where every dept (HR,Finance etc) determined that they needed a downtime *right now* to fix a *critical* business issue, and IT got all the flak for the system being ‘down again’. Entertainingly the number of critical issues fell by approximately 60% when people knew they had to a) wait till the scheduled window and b) had to explain to the rest of the business what the business driver was.
So I’m all in favour of maintenance windows, especially those that don’t get used, the maintenance though has to have a business case (yes a real one with costs and benefits attached).
As for the mental image, well I know that you are a musical man of taste and judgement so I thought it only fair 😦
LikeLike
Sunday afternoons must be really boring down there, if rebuilding indexes is the highlight…
LikeLike
Hi Noons
That’s because not everyone is lucky enough to live in Australia. In some places, maybe you need those index rebuilds to fill in the time 🙂
LikeLike
The horrible grammatical and spelling errors alone make the article (in its current form) a real hoot.
LikeLike
Hi David
Oh yes !!
Shhusssh, don’t give away too much 🙂
LikeLike
Hi Uwe
Regarding the seminar, you should have seen my eyes roll when I walked into a packed room in Germany (Dusseldorf) last year and I thought just have much (or little) I was making out of all the revenue generated.
There’s two sides to every story 😉
Unfortunately, I’m not sure what’s happening yet for next year, nothing has been decided yet. Hopefully I’ll get the opportunity to try and answer all your tricky questions 🙂
LikeLike
Thanks for that Richard, that article really is a scream, though I haven’t yet worked out whether that’s in the excruciatingly horrifying or excruciatingly comical sense. Perhaps both, if you can’t laugh…
LikeLike
Hi Sean
Given the choice, always go for the laugh whenever possible 😉
LikeLike
Hi Niall
Excellent points. The problem with downtimes / maintenance windows having to have a business case, it that often those that actually need to approve the business case are dependent on those requesting the window for the justification. “We need to bring the application down every Sunday else the application will run slow” is difficult to refute if it’s your (so-called) technical experts making the claim.
LikeLike
A few years ago, I picked up a book on tuning by old Don as it was going for 2 quid in one of those second rate book stores. I must say that I was horrified by most of it’s content (not that I anywhere near finished it). This was around the same time that I was reading Tom Kyte’s Expert Oracle Database Architecture, so you can imagine the difference!
However, I have found one good thing to come from his 5 million page website(s)…
On a Friday morning everyone is given 5 minutes to find the most outrageous quote they can from the ‘great’ man – that becomes the quote of the day and you have to use it as much as possible, that quote can not then be used again.
Try it, it brightens the end of each week!
LikeLike
A few years ago I started with a new employer. There was a Rampant book by Mr Burleson that was handed on to me as the new starter to litter my desk. I sat there unopened for over a year until one day we had Jonathan Lewis on site to dig us out of a hole. I didn’t really get to speak with him until during a stroll around the office he wandered my way, saw the book and asked me how I’d found it! Dammit, if only “Practical Oracle 8i” hadn’t been at home on the bedside table! 🙂
LikeLike
Hi Neil
LOL !! Yes, I not too sure that a Burleson book would have created the best of impressions with Jonathan 🙂
LikeLike
I’m now with you on “certain matters” (your point 9 response). Thank you for this article Richard (and many others!), I’ll be more selective with my google search results from now on.
LikeLike
[…] Foote and his readers discuss another time-based ritual of old, rebuilding indexes every sunday afternoon. “I just had to share this amusing article on ‘Scheduling Oracle Index […]
LikeLike
Foote, you are doing a service. Actually its extremely necessary to expose such misleading authors and their articles.
I tell you from experience that hundreds of thousands of people get misled through the author and his article you mentioned. Especially in countries where the only way to get Oracle information is Internet, the searches for many terms go to Mr. Don’s site, because he spends more time in SEO of his site, and has filled it with every imaginable Oracle keywords.
Back in 2003, when I was just 1 year old in Oracle DBA, fresh from Oracle University, I got misled by the same author sometimes and still know many who still follow his articles, because they simply DONT KNOW. I make my own humble efforts to redirect them to asktom or such sites.
Is it a way to tell Google which content is right and which is wrong?
LikeLike
This is what I have been doing from last several years.
In google.com you can actually filter results like this.
oracle index -burleson
Google will filter out all the results where it finds “burleson” .
Or like this
oracle index -dba-oracle.com
Or
oracle index -dbaforums.org -dba-oracle.com
Hope this helps.
Thanks,
Nayab
LikeLike
Hi Nayab
All good tips 🙂
LikeLike
Hi Fahd
Thank you.
See Nayab’s post on a way to filter out the rubbish.
LikeLike
Nayab reminded me that Google gives you a way of defining your own search … parameters… you can include the sites you want to search… not sure if there’s a way to just ignore sites and search the rest of the internet. I’ll try it… see what it takes to share the list editing with others.
we could pass that around the community.
LikeLike
Thanks for the tip Nayab. I’ve been darting around those urls for years to try and decrease the rank order now I don’t even have to see them.
LikeLike
Hi Nigel
Yes you could rebuild an index with NOLOGGING, but you miss the point that such an option is simply not possible with you’re one of the many sites with standby databases.
And it also misses the point that redo is but a part of the potentially huge costs associated with rebuilding all your indexes (think undo, think massive I/Os, think massive sorting, think locking implications, etc.etc.
The point being that rebuilding indexes is not a zero cost operation. It requires lots of resources or a downtime that many also just can’t afford.
BTW, it’s hard to chill out when you have weeks of 30+ degree C on end 😉
LikeLike
As I predicted, the article has changed somewhat. No surprise there although all the changes are illuminating, especially the subtle little ones (those that have the original will see what I mean). Classic quotes such as “I am a production DBA and I’ve been rebuilding my indexes every Sunday afternoon for decades” are sadly now gone.
It’s still very amusing however and rather than “Neophytes”, it now makes references to “foreign people” and “foreign Oracle scientists”.
Odd, as I had always thought Tom Kyte was from the US …
I wonder who he could be referring to 😉
LikeLike
Hi, I like some of the changes even more, especially the link to the SAP guy at http://www.sdn.sap.com/irj/scn/weblogs;jsessionid=(J2EE3417400)ID0437089950DB00792386764563478157End?blog=/pub/wlg/8750%3Fpage%3Dlast who doesn’t want material reproduced outside the SAP domain and uses a queue table for his example, which is to say the least a somewhat specialized, though not uncommon, scenario. In addition I’d have thought his comment that “I am not recommending blindly rebuilding indexes” and “we rebuild indexes following large purges” and “you should know what effect you are intending to achieve” all somewhat run counter to the tenor of Don’s article. There’s a moral here somewhere, something about what to do when in a hole, methinks. 😦
LikeLike
Hi Niall
I can only imagine the embarrassment of both Hemant and Jim of being quoted in the article 😦
Sometimes, no matter how hard you try and squirm and wiggle, how hard you try and back peddle, claw and crawl your way out, the hole is just too deep and wide to get out 😉
LikeLike
Hey! I knew the original article I kept will come in handy one day! :p
I’m going to auction it at EBay. Any buyer? :p
LikeLike
Hi Andy
LOL !!
I told you the original was worth saving 🙂
LikeLike
Thanks for cool article i think is worth service. I am not oracle veteran and i sometimes find – via google of course – some cool dons tips with which i disagree. And sometimes i am thinking who is wrong me or this dba guru ;).
Latest topic with which i disagree found on this web was that histogram is good only for columns with indexes :-).
Thanks again.
J.
LikeLike
Hi J
I’ll back you being right every time 🙂
Yes, only needing to collect stats and histograms on indexed columns is a dangerous myth.
There you go, I told you you’ll be right 🙂
LikeLike
Hi Hemant
It’s really unfortunate and quite shameful you’ve been quoted out of context in this manner 😦
What you’ve have done is exactly what I’ve been trying to get across. Identify those indexes that might actually be problematic and implement an appropriate strategy to address them.
You have 3 problem indexes due to specific data loading characteristics and you fix just those indexes as necessary.
Perfect 🙂
LikeLike
Now you’ve gone and done it Richard, the next version will no doubt have text that says something like “in this discussion we see Oracle Scientist Richard Foote recommending index rebuilds”.
LikeLike
Sounds like I have to take a snapshot of the current version. I’m going to be rich!
LikeLike
Hi Andy
Keep saving all the changes. If you have the complete set, it will be far more valuable in years to come 😉
The latest version is just hilarious. It has a new introduction:
“While it remains true that the vast majority of Oracle indexes will not see any SQL performance benefit from rebuilding, there are isolated cases whereby rebuilding has been proven to improve SQL throughout and other cases where it has been necessary to schedule a rebuild of problematic production indexes”
which totally contradicts his introduction in the original version:
“I am a production DBA and I’ve been rebuilding my indexes every Sunday afternoon for decades. My end-users say that they notice a definite improvement in response times on Mondays, but now I hear people making bizarre statements that because their testing on a PC shows case single case where rebuilding does not help, they make faulty over-generalizations and say that index rebuilding is largely a waste of time and that index rebuilding rarely improves query performance.”
Ummm, don’t these “bizarre comments” now match his back-peddling new comments ???
“vast majority of Oracle indexes will not see any SQL performance benefit from rebuilding” vs. “rebuilding rarely improves query performance” kinda sounds the same to me.
And:
“there are isolated cases whereby rebuilding has been proven to improve SQL throughout” vs. “index rebuilding is largely a waste of time” except perhaps for those isolated cases …
Already looking forward to the next instalment 😉
LikeLike
Hi Niall
Considering the number of changes to this thing now, I guess anything is possible !!
However, I suspect I might still be considered one of those “foreign people making bizzare comments” with claims such as “index rebuilding rarely improves query performance”.
Then again, as the latest version (as of 2 Dec 09) now has a new introduction that says:
“While it remains true that the vast majority of Oracle indexes will not see any SQL performance benefit from rebuilding, there are isolated cases whereby rebuilding has been proven to improve SQL throughout …”
he’s beginning to sound like one of those bizzare foreigners himself 🙂
LikeLike
I note that today’s so-called “Oracle news” item (http://www.dba-oracle.com/oracle_news/) is yet again, this very same article on scheduling index rebuilds !!
Perhaps if you say it enough times and make it the days big news enough times, it might become true 😉
LikeLike
Really? Just repeat saying it and it will come true?
Let me try ….. I’m going to be rich … I’m going to be rich … I’m going to be rich …. [ with a full collection of ‘B’-grade web articles ….]….
LikeLike
Hi Andy
It doesn’t really work does it 😦
BTW, stop trying to oversell your web article collection by rating them as high as b-grade 😉
LikeLike
Dang! That means I can’t retire next year… how sad 😦
By the way, the ‘B’ in “‘B’-grade” is the first alphabet of the name of the highly acclaimed author, not the rating itself 😉
LikeLike
After much deliberation, I have decided to classify those articles as ‘D-grade’ articles. ‘D’ is also part of the initial, but it sure sounds much better 😉
D-grade ….. D-grade … Degrade ….
LikeLike
Hi Andy
LOL !!
When I was a kid, “F” always signified a Failed mark. Personally, I would give it an “F” grade although I’ll leave to others as to what “F” could stand for …
😉
LikeLike
nc-17, I would have thought myself. North Carolina revision 17.
LikeLike
Hi Niall
Surely, there have been more than just the 17 revisions. I think you must have missed some 😉
LikeLike
Hi Niall,
Excellent Wikipedia link 😉 Definitely all of those D-grade articles are not suitable for children under 17. If you think about it deeper, it should not even be NC-17. It should be NC-70. But according to one particular Oracle Expert, any opinion written by anyone who does not publish his/her CV/credentials cannot be trusted. :p
LikeLike
17 revisions so far? I hope they are using some sort of source code control system to manage them 😉
LikeLike
andy and richard. I’ll admit the number 17 wasn’t chosen entirely at random 🙂 wikipedia might tell you why http://tinyurl.com/ydzvnn5
LikeLike
I know this is slightly off topic, but I read the article and this heading leapt out at me.
“Keeping dangerous techniques out of the hands of inapt DBA’s”
I suspect that the phrase was meant to be inept. But it set me to wondering where a DBA would be inapt, and why you would only want to keep a technique out of their hands at this time and not others.
I can think of several locations in which a DBA would be considered inapt (operating theatre, wrestling ring, car assembly line). The bit I am struggling with is the appropriate use of the techniques when the DBA is not being inapt (ie at work). Any ideas?
LikeLike
Hi Howler
Are you suggesting that it’s apt someone who is inept has used inapt when perhaps meaning inept ?
😉
LikeLike
Hi Niall
LOL 🙂
I think these articles need a similar system. Only read if you have say at least 17 weeks of experience with Oracle. Any less and you might be fooled into thinking they might have merit or be correct. Any more and you’ll have enough experience to recognise all the errors for yourself 😉
LikeLike
Hi Richard,
I just read the above comments and frankly speaking i’m “confused”. I’am a junior DBA and have an experience of around 12 months.
I just want to know clearly whether we need to go for Index rebuilding? Is it really helpful? And if yes, under what circumstances, kindly elaborate.
I’m sorry, i’m just a beginner, if this question is a repetitive one and amuses you.
Regards,
Nikhil.
LikeLike
Hi Nikhil
Don’t apologise if you think you might have amused me, I enjoy being amused 😉
There’s no simple way to answer such a question within a comment, except to say that the vast majority of indexes in the vast majority of databases don’t need to be rebuilt.
Even Burleson now says “While it remains true that the vast majority of Oracle indexes will not see any SQL performance benefit from rebuilding, there are isolated cases whereby rebuilding has been proven to improve SQL throughout …”
To find out these “isolated cases”, I suggest having a good read through my various blog entires on this matter and checking out this presentation of mine:
Click to access index-internals-rebuilding-the-truth2.pdf
LikeLike
[…] https://richardfoote.wordpress.com/2009/10/28/rebuilding-indexes-every-sunday-afternoon/ […]
LikeLike