Re: Suggestions for first exam ...

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:3f7575cb$0$9828$afc38c87_at_news.optusnet.com.au...
> Sybrand Bakker wrote:
>
> > On Sat, 27 Sep 2003 18:47:15 +1000, "Howard J. Rogers"
> > <howardjr2000_at_yahoo.com.au> wrote:
> >
> >>And why in God's name would I want to convince a customer to "replace a
> >>server" when all that is actually needed, it would seem, is a bit more
> >>disk space??
> >
> > Which isn't possible in this situation, as I explained. Anyway, you
> > seem to think you just call or email your customer to order a drive.
> > That's NOT the way things work here.
>
> Fine. It's not OK for you and your situation. But that's your situation,
 and
> your foolish customer. The fact of the matter remains that index rebuilds
> are extremely seldom needed.
>
> No, I haven't said 'never'.
>
> But don't extrapolate from your particular situation to the general case.
>
> >May be it penny-wise or
> > pound-foolish, customer pays to maintain the server, customer doesn't
> > want to pay for extra hardware. Seems like you are unable to
> > understand that.
>
> I understand it perfectly well. Maybe you should try explaining things to
> them in calm, clear language, without coming on too heavy as you so often
> do here. Maybe they would be persuaded of the virtues of a modest extra
> piece of hardware in that event.
>
> Or maybe your negotiation skills are a bit lacking.
>
> Who can say?
>
> > BTW: You aren't saying you are never making rude comments here?
>
> No I'm not. But I'm not racist, and I usually give people a chance to ask
 a
> question before jumping down their throats. Perhaps you could extend them
> the same courtesy one day?
>
> >I have
> > to laugh.
>
> Good. And when you stop laughing, maybe you can stop making silly comments
> along the lines of 'One customer needs 5Gb of disk space, so index
 rebuilds
> are just fine'.
>

Hi Howard/Sybrand,

Coincidently, it was only late last week I read a truly dreadful article by Mike Hordila (at www.dbazine.com) about automating index rebuilds that propagates every index myth know to man. It included a diagram (or too common unfortunately) that described an "unbalanced" Oracle index with the leaf nodes on the RHS being of a greater height or distance from the root node than those fortunate leaf nodes on the left.

IT CAN'T HAPPEN IN ORACLE !! I screamed at the screen to which the other DBAs in my area looked nervously in my direction.

Coincidently again, I was motivated enough to write a complaint to dbazine a few weeks ago regarding articles by a John Weeg which are so appalling in their inaccuracies that I've used them as a basis of how Oracle doesn't work and what not to do as an Oracle DBA. Among the inaccuracies listed there (a rather long list) was this (mis)concept of unbalanced indexes in Oracle and the consequent requirement for index rebuilds.

Coincidently again (first the last time), I've noticed a new article by Jonathan Lewis at dbazine which nicely describes why the unbalanced Oracle index is myth of utter crappy proportions (perhaps my complaint had an effect ?)

Howard, you might be interested to know that Jonathan used the term "skewed" and to describe the concept of "data skewness" where incrementally increasing insert values in the "RHS" of the index in combination with deletes on the "LHS" of the index results in a balanced index (as it *must* be) but one in which many of the index blocks on the left hand side are far less dense in their data content. I quite like the term but it's still open for mis-interpretation.

As index values are not to be reinserted in the "emptying" index blocks and as many many of these blocks are not totally emptying, the condition exists for a rebuild. But I agree with Howard, the above scenario is not generally the norm and hence nor should index rebuilds. The other possible scenario that springs to mind is where a table (and hence the associated indexes) have a substantial deletion of data without the likelihood of data being re-inserted soon. Both a table and hence an index rebuild would then be in order.

Sybrand, for you to have 5G of space "wasted" in an index, you must have deleted 5G of data. Not only that, but for the space to be truly wasted, you must have no intention to re-inserting data again such that the deleted space can be re-used (as it can be, another common myth if one reads and believes John Weeg articles). That being the case, if you have 5G sitting in an index, with no subsequent insert likely to reuse that space soon, (hence meeting one of the two scenarios above) then yes, the index could and possible should be rebuilt (your index scans could possibly appreciate it for a start).

But it's not *that* common a scenario (at my current site with 10s of thousands of Oracle tables, not one meets this criteria).

Something that's all a little sad about all this is that those poor souls that commonly go to the substantial expense of frequently rebuilding indexes in the generally false hope that performance consequently improves often find that performance actually eventually worsens as a result. That's because after rebuilding the index, you now have tightly packed index blocks (else why rebuild right). That means however, that index splits become more common as these blocks fill. That requires extra I/O and CPU processing for your not so better running transactions. And after an index split, you now have 2 blocks sitting there 50% empty. This goes on for a while and guess what, it's time for an index rebuild again to re-claim all this wasted space !!

Like I said, it's all a little sad ...

Cheers

Richard Received on Sat Sep 27 2003 - 08:56:39 CDT