Re: Index management

Mike Ault wrote:
> Snip>"To distill my advice on index rebuilds, I say to only rebuild
> indexes
> which show, through proper analysis, to have problems. Examples of
> possible probelms are: a large clustering factor to dirty base table
> block ratio, [and he does go on to list a number of other tests which
> are not at issue in this thread]">
>
> As usual, you only quote the part of the posting that supports your
> argument and ignore other relevant data.

Don't be bloody stupid Mike. I quoted the part which makes reference to the thing under discussion, namely the clustering factor. The other tests, which I acknowledged existed, aren't to do with the clustering factor.

Mike, in case you haven't noticed, we're not particularly interested in this thread any more with your words of wisdom on a range of topics.

We are interested in one simple fact: do you actually have even the vaguest idea of what an index's clustering factor is?

That's all.

> Using this method I can prove
> that because we in the USA drive on the opposite side of the road than
> those in England, we get tornados and they don't. As I have tried to
> tell you in words of one syllable or less, the clustering factor to
> "used base table block" ratio is only one thing that should be used to
> see if an index needs to be rebuilt.

And as I have gone to great lengths to point out, since an index rebuild DOES NOT change the order of entries in an index; and since an index rebuild DOES NOT change the order of rows in a table; and since the clustering factor is a measure of the correlation between those two sort orders... the clustering factor will remain precisely, exactly the same AFTER a rebuild as BEFORE.

Now I don't care if you have 57 other tests to determine whether an index should be rebuilt: it's not relevant to this thread. You happen to have this one particular test in your kit bag... and it is *that* *one* *test* that is under discussion here, and which usefulness or lack thereof is being assessed.

The scope of this thread, despite many diversionary tactics by some, is really very narrow. Does Mike Ault's test for clustering factor/in-use table blocks ratio have any actual meaning or value? That's all.

Once we get an answer to that, I suppose readers will be able to draw their own conclusions about a related, but subsidiary matter: Does Mike Ault have a clue about what he's talking about?

But we can't answer that question until we get an answer to the clustering factor one.

>The number of levels and the
> amount of "white" space in the index are also determining factors. As
> someone so appropriately stated, rebuild the index when the benefits
> of doing so exceed the cost, unfortunately you can't determine this
> until after they are rebuilt. I am just trying to provide guidelines
> as to when the index becomes a candidate for rebuild. Notice, I said
> "guideline" not absolute rule, not law of nature, just guideline.

Guideline or 11th commandment. It doesn't matter if, whatever it is, is meaningless drivel, wouldn't you say? So the question before us is to assess whether this particular guidelines actually tells us something useful, or is a complete waste of time.

> You want to force me into this idiot box you have created, sorry, I
> don't fit, I hope you like it in there. If you refuse to see the
> entire picture, then no one can correct your misunderstandings.

Blather, blather, blather. Jeez Louise: you're making an awful mess as you try and wriggle and slither out of this one.

No-one has put you in an idiot box Mike. I spent several posts trying to work out what a 'dirty table block' was. I've now spent several posts trying to work out what you mean by 'clustering factor'. I have, in short, spent considerable time and effort trying to get you to elaborate what you mean, precisely so that I don't mischaracterise what it is that you're saying.

I wanted to be absolutely 100% sure that what you called the clustering factor is what ordinary users of Oracle would know as the clustering factor. That wasn't to force you into a box. It was merely to be certain we are talking the same language.

Because *when* you use Oracle's definition of clustering factor, which is invariant after an index rebuild, your proposed test, 1 of several hundred I am sure, is *stupid*. A complete waste of time. A wild goose chase.

Now, you can defend your test if you like, and prove it does what you claim it can do. You can post some actual evidence that the clustering factor is variable under the impact of an index rebuild. Or you can try and kick up some "I've-never-been-so-insulted-in-my-life" diversionary dust so we all don't notice that you can do none of those things.

I'll give you a clue, though, Mike. I haven't put you in an idiot box, but your continual refusal to post anything resembling *evidence* is making an awful lot of people consign you to that location in their own minds. One simple bit of evidence that we can all try out at home would be enough to severely embarrass me, and justify you. Just one.

> The idea that you should optimize an index for inserts and updates is
> totally odd to me as indexes are used for optimization of select
> processing. If indexes are causing that big of a problem during
> inserts and updates then maybe you need to review what you are
> indexing and why. Avoiding index rebuilds by crippling the performance
> of indexes by making them too-broad (i.e. sparse) is also an odd idea.

Well, I have news for you Mike. In the wide world of "odd", nothing is odder than advocating spending more than, Oh I dunno... a nanosecond?, on calculating a ratio to indicate whether an index should be rebuilt. Only to have that ratio remain unaltered AFTER the rebuild. That really *is* odd advice.

> The next time I have a client who needs to consider index rebuilds to
> improve performance I will capture some stats for the list and will
> post before and after data.

You mean, you don't actually *have* any data to hand? You mean, the author of so many Oracle books, the self-proclaimed expert, can't actually lay his hands on a bit of research data right now to justify his preposterous claims?

Why am I not surprised?

And no doubt this is also the answer to those several posters here who have asked to see a demo they can run at home to see the clustering factor change after a rebuild: no you can't kiddies, because this is much too hard and complex to be easily reproducible. World-renowned expert has to acquire data from big, important clients before the effect of which he has written ever becomes visible.

It's a total load of Horlicks, bombast and fudge. Which is not as delicious a confection as it might sound.

> Those that want to see what report I use
> to gather the index stats data can email me, I supply it to any who
> ask. I have no magic formula for clustering factor, Oracle calculates
>
> The
> number of dirty, used, occupied or whatever euphemism you wish to use,
> blocks is also readily available in dba_tables. A ratio is determined
> by the division of two values, so when I say the ratio of clustering
> factor (a single, easy to obtain number) to dirty blocks (another easy
> to obtain number) then I have stated my formula. Since the actual
> value of this ratio for "badness" varies from table to table and index
> to index, I usually just say, when it exceeds several times the number
> of dirty blocks and when really pressed I say start at 8 to 10 times
> the value and do further analysis (many people want a hard number,
> sorry, there isn't one.)

Right. Finally, we have an answer.

You DO use DBA_TABLES.BLOCKS. And you DO use DBA_INDEXES.CLUSTERING_FACTOR.

And you therefore cannot possibly, ever, have seen the ratio change after a simple alter index X rebuild command for a regular old b*tree index.

Not once, not ever.

And any claims you make to that effect are just so much hot air.

And we are left with the uncontestable and astonishing fact that Mike Ault, prolific "author" and self-proclaimed "expert", advocates the calculation of a ratio which DOES NOT CHANGE after you take the action he claims you should consider taking when the ratio is bad.

Mike: when will the penny finally drop? Users don't want hard numbers or soft numbers, but MEANINGFUL numbers. And truthful ones.

> I don't say, rebuild all indexes, which would be as bad as never
> rebuild indexes.

Sniff. Uh huh. Smells like a strawman.

Nobody said you ever suggested "rebuild all indexes", Mike. We've just had it confirmed in your own words, however, that you do recommend thinking about rebuilding indexes if the ratio of clustering factor to in-use table blocks gets too high. That is quite bad enough.

>Both of these can lead to problems. Analyze (as in
> look at various statistics), rebuild when indicated. Think for
> yourself.

I couldn't agree more. Let's be just a *little* bit more specific, however: "Don't let Mike Ault do the thinking for you, because at least in one measurable and testable respect, he hasn't a clue what he's talking about".

There. A little more accuracy never did anyone any harm.

HJR Received on Mon May 10 2004 - 15:38:50 CDT