Re: Index management

Mike Ault wrote:

> Anyway, I thought the concept of dirty base table blocks was
> self-evident to those using Oracle, but since there seems to be some
> confusion let me digress into an explanation. When a table is created
> all of its blocks are in a sense "clean", that is, they contain no
> data. As Oracle writes data to the database tables, their blocks
> become "dirty", a reference no doubt to the "dirty buffers" the
> dirty-buffer writer writes to them (while the politically correct
> "database writer" has supplanted the original "dirty buffer writer" in
> the original database documentation in version 6 and earlier it was
> "dirty buffer writer".) So, a dirty block contains data, while a clean
> one does not.

Extraordinary. Mike just keeps on re-writing the Oracle lexicon. A dirty block is one with data in it (we all know it as a used block, or even just a block below the high water mark). A clean block is an empty block or a block above the high water mark. Fair enough. He has his own private language on these matters that none of us can understand until it's translated, but whatever keeps him happy, I suppose.

Anyway, we can now, finally, work out what Mike was talking about a few posts ago.

Here is his original quote:

"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]"

That can now be translated to: "I say to rebuild an index if its clustering factor exceeds the number of used table blocks by a large margin"

That's all.

I honestly thought (and hoped) it would be subtler and more cunning than that. But that's what he was saying. If DBA_INDEXES.CLUSTERING_FACTOR > DBA_TABLES.BLOCKS (by a lot) then REBUILD.

The only slight problem is that an index rebuild cannot possibly change the number of BLOCKS for the table (hopefully, that one is obvious). And as I showed in my post which Mike won't actually reply to, the clustering factor won't change as a result of an index rebuild either.

Meaning that the formula Mike is using to suggest the rebuild suggests it equally as well after the rebuild as before it -which by any definition makes it meaningless.

No doubt, however, we will now discover that Mike has his own definition of an index clustering factor and that he doesn't use the one in DBA_INDEXES at all. Or some other definitional issue will arise. So in the interests of clarity.

Mike: could you please confirm that you use the formula DBA_INDEXES.CLUSTERING_FACTOR > DBA_TABLES.BLOCKS (by a lot). In other words, to assess an index's clustering factor, you take the figure shown in the column of that name in the DBA_INDEXES view. And that to assess the number of "dirty base table blocks" you use the BLOCKS column in the DBA_TABLES view. If you get your figures from some other source or computation, could you please explain how you do it?

And you claim to have seen CLUSTERING_FACTOR in DBA_INDEXES change after an index rebuild, right?

Thanks
HJR Received on Sun May 09 2004 - 15:33:59 CDT