Re: Index management

Mike Ault wrote:

> Richard, Roger,

Er, that would be "Howard", not "Roger".

[snip]

>I may show why general old saws are bad, but I don't take the
> delite in castigating others they way you seem to!
>
> For example, speaking for myself, when I tell people to rebuild
> indexes

...you make your first mistake, since the advice should be 'not to unless', not 'rebuild unless'.

[snip]

>I also say they should look at the type of index, how it is
> used, if it is on concatenated columns and several other factors. I
> also tell them to compare it to the number of dirty blocks in the
> table and if it is several times that value the index may benefit from
> rebuilding.

That sentence doesn't even make sense to me. "I tell them to compare it [what "it" are we talking about? From the context, it sounds like it should be "the number of leaf nodes of the index"] and compare it to the number of dirty blocks in the table [if it's "in" the table, safely on disk, it's not a dirty block is it? Since I know you know that, I have to assume you mean 'blocks from the table currently in the buffer cache that have been dirtied'] and if it is several times that value [again, what value are we talking about? Index leaf node count?] the index may benefit from rebuilding."

So if I've got this right, you're saying "if the number of table dirty buffers at any one moment is several times larger than the number of index nodes, a rebuild might be beneficial".

But that is clearly a ludicrous proposition, so that can't be what you meant at all. I would like to know what exactly we're supposed to be comparing to what.

>I also tell them sometimes rebuilding will not improve the
> clustering factor and then, they should consider re-ordering the table
> data (hmmm...where have we heard that one from?)
>
> I have also showed papers and proofs that clustering factor is
> dramatically affected by column order (which should be intuitive) in a
> concatenated index and that since it is a major factor in cost
> calculations, reordering the columns in the indexes and the SQL it
> supports, can make a less attractive index more palatible.

I would suggest that changing the column order in a concatenated index is not so much an index rebuild as an index redesign. And of course column order is important, and if you get it wrong, you should of course change it. And I'm sure Richard wouldn't disagree with that either.

> Anyway, I wish if you would quote my suggestions, you would also quote
> the qualifying material that goes with them, but that wouldn't be as
> much fun now would it?

It would mean trying to squeeze one hell of a lot of material into a presentation (and apparently using mind-reading techniques to work out what some of it means in the first place)! If you don't like being quoted, then don't say it in the first place. If it is so easy to quote you "out of context", then it rather suggests you have been careless with your choice of words. If your published works told the facts of the matter, that an index rebuild hardly ever makes a difference, but that there are always exceptions, then I doubt anyone would be quoting you otherwise.

HJR Received on Wed May 05 2004 - 17:21:58 CDT