Eye on Oracle

Dec 17 2007   12:19PM GMT

Oracle mythbusters

Ken Cline Profile: Clinek

Veteran Oracle DBA Richard Foote recently posted an interesting article/diatribe about the myth of rebuilding Oracle indexes. He states:

There are still many people who think indexes should be rebuilt regularly if they experience lots of DML, that indexes should be rebuilt if they’re have a height greater than some level. . . . [But] most indexes never need to be rebuilt. Never, ever.

It’s an entertaining read, with some lively discussion from readers. On a related note, we also have posted several myth-busting articles you might be interested in. For example,

1. Tom Kyte’s frequently asked questions and myths about indexes, including

  • Myth: Space is never reused in an index
  • Myth: Most discriminating elements should be first

2. Mike Ault’s Oracle myths debunked

  • Myth: Indexes and tables do not need to be separated
  • Myth: Multiple blocksizes don’t improve performance

Sure, Mike and Tom are no Adam and Jamie (they’ve caused no explosions that I know of), but newbies need to be aware of some of the controversies, half-truths and outright errors percolating in the Oracle community. Do you know of any myths that you’d like to see busted? Let us know!

Have a good week, Tim

2  Comments on this Post

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.
  • Howard Rogers
    Not sure why you would list Mike Ault's two contributions as examples of myth-busting: they both actually propogate two nasty myths. Indexes and tables do NOT need separating **for performance reasons** (which was the original myth). If you would like to separate them for administrative or personal reasons, feel free, but it won't make a blind bit of difference to the performance of anything. Which is, indeed, the conclusion Ault laboriously comes to... but in a way which might fool some people into thinking the old myth about performance benefits is actually true. And the use of multiple blocksizes as a performance tuning technique merely complicates database management whilst simultaneously manageing to potentially introduce some very nasty performance bottlenecks (no ASMM, no KEEP/RECYCLE caches for the non-default blocksize caches, increasing contention for large blocks, etc etc). Meanwhile, Ault's article makes no attempt to prove that multiple blocksizes improves performance. He merely asserts that it does and quotes someone else asserting that it does... but there's no attempt to discriminate, for example, between the effects of the new block size and the effects of the one-off reorganisation of segments which the use of the new block sizes mandated. Ault's work you cite, therefore, is not myth-busting but rather falls into the category of half-truth or outright error you go on to mention.
    0 pointsBadges:
  • Tim DiChiara
    I'm glad you pointed that out, Howard. Yes, there have certainly been tensions between the "Oracle scientists" and others who shall go nameless. And I'm sure Mike would disagree with Richard's post. I'll try to contact him so he can respond himself. --Tim
    0 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: