Re: Suggestions for first exam ...

Thanks, Richard, for this. Good stuff. One or two comments interspersed.

>Richard Foote wrote:
> 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.

Absolutely agree. I call them 'hammered' indexes in class, because it's the right-most leaf-node that gets hammered with all the I/O. But I've used the term 'skewed' as well. 'Slewed' has also made an appearance, but that was after a lunch-time beer, so who knows!

I objected to Sybrand's use of the term because it came in the context of his comments about indexes being capable of being 'unbalanced'

>
> 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.

Again, agree entirely. However, as I also mentioned in my first post, if the deletes are 'bulky' deletes, then entire leaf nodes will have been emptied, and thus they become available to be the new 'right-most' leaf node. It's only 'sporadic' or perhaps I should say 'patchy' deletes that give rise to a genuine need to 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.

See above, though. Mass deletes mean leaf nodes probably become available for fresh inserts (ie, a leaf node with entries for Adam, Bob and Charles in it, where Adam and Bob get deleted, is still the 'A-B-C-ish' leaf node, and hence Wilma can't find a home there. But delete Charles as well, and you have a totally empty leaf node that, irrespective of its physical location, is perfectly capable of accepting a new entry for Wilma, Xerxes or Zebedee. Truly bulk deletes are not normally a problem requiring a rebuild).

>
> 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.

Again, absolutely agree: I've measured it several times. You want to know something *really* sad? Student in performance tuning class last week announced she's running a SAP application, and SAP have told her that *daily* index rebuilds are a requirement, otherwise the support contract's at risk. I frankly can't believe it, but she swore it was true. Anyway, point being: there are thousands of people out there doing regular weekly or monthly rebuilds to precisely no end, and thinking they are doing good.

Sad isn't the word I would have used: infuriating is more like it.

> 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 !!

Which is again all precisely true, and one of the reasons why if you really feel the urge to rebuild, I think the coalesce is a better bet. At least the freshly-emptied space still belongs to the index and doesn't have to be re-claimed the second a piece of DML starts happening on the table.

But if there's one thing we can confidently state about indexes, it's that everyone thinks they know how they work, but they usually, on closer inspection, don't. One or two "well-known names" spring to mind in that regard especially, such as Don Burleson. Meanwhile, the subject has become encrusted with myths and nonsense and (the sad bit) bad practice.

A peace offering to Sybrand: I accept that in pushing for the correctives that are needed on this subject because of all those myths, I can appear to believe in black and white, when I realise that the world is actually grey. Sometimes to make a point, one has to make it rather more emphatically than others care for. When that happens, it helps not to take it personally, but to understand the context in which things are said.

Regards
HJR
>
> Like I said, it's all a little sad ...
>
> Cheers
>
> Richard
  Received on Sat Sep 27 2003 - 10:09:49 CDT