Re: Index management

From: Richard Foote <richard.foote_at_tbigpond.nospam.com>
Date: Mon, 10 May 2004 13:58:12 GMT
Message-ID: <UTLnc.31184$TT.15407@news-server.bigpond.net.au>

Hi Mike,

You know, when all is said and done, the simple, code, hard fact is that you really *don't* understand the significance of the CF and how Oracle implicitly maintains the CF. You *don't* understand how to alter the CF and you *don't* understand how rebuilding an index has *no* effect on the value of the CF.

Mike, you just don't get it !!

And because you don't get it you, I understand now how you just don't see how the quote of yours in my presentation is so utterly and totally wrong. Ignorance is making you blind to the truth.

See comments below ...

"Mike Ault" <mikerault_at_earthlink.net> wrote in message news:37fab3ab.0405090937.43179b22_at_posting.google.com...
>
> 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. Obviously, unless it is corrupt, an index can only point
> to dirty blocks in a table. Hence a "perfect" clustering factor would
> be equal or perhaps less than the number of dirty blocks, while for a
> very random index, it would be closer to the number of actual table
> entries (for some bitmap indexes, I have seen it greater than the
> number of rows.) Usually, clustering factor shouldn't be greater than
> the number of rows in the underlying table.
>
> Now, as a block fills and then reaches pctused, it is taken off of the
> free list and is not eligible for any more insertions, however it can
> under go updates which can cause row chaining, but that is another
> issue. Once deletions from the block cause the available space to drop
> below pctused and it is placed back on the free list for more inserts.
> This insertion back on the free list could be hours, days or some
> greater time later than the last time it was used for inserts. This
> means the index nodes that are referencing its inserted rows are most
> likely way across the index tree from the original ones that hold most
> of its entries.

Not necessarily at all. This implies that the previously inserted rows are mostly aligned with the index entries. With a random index (say a table containing a list of random customers), the customer name index may be never be aligned with the index entries. Mr Simith can be immediately followed by Mr Adams that can be immediately followed by Mr Zulu that can be immediately followed by Mr Foote, etc. in the *table*. But the *index* is always sorted in the order of the index. So if you delete say Mr Adams and replace it with another row where the Mr Adams row previously existed, the impact with the CF is also random (it may be 1 better, 1 worse or unchanged)

Also, even if you happen to have *1* index that currently has a good CF that may be impacted by subsequent deletes and inserts, what about all the *other* indexes on the table. You likely only have 1 index that has a good CF anyways, the table can only be ordered significantly on more than 1 column.

Also, you continually confuse the *physical* and *logically* location of the index nodes as if they have some significance with regard to the CF. They do *not* !! It's the physical order of the rows in the *table* in relation to each of the indexes that determines the CF.

The CF is a value that represent the number of physical reads of the *table* likely required to read the entire *table* via a full scan of the index. The index *must* be sorted in the order of the index entries. It must. Therefore, you are concentrating on the wrong object when you're trying the determine the impact of inserts and deletions on the CF. The logical order of the index is not impacted by DML. Therefore, when navigating the index during a full scan, you will always read the values starting by A, then the values starting by B. It's how well aligned the table is in relation to the index that's important.

Mike, you've got it the wrong way around !!

>
> Add to this the various node splitting algorithms Oracle uses for
> non-sequential inserts and updates and you can easily see why
> clustering factor increases and can become out of sync with reality.

This is totally and fundamentally wrong. An index node split has *NO* impact on the CF. None. After the node split, the index entries are still *logically* ordered. Therefore a full index scan will required exactly the same estimated physical reads to read the *table* before the index split as it does after the index split. When you insert an index entry, it has *NO* impact on the CF. None !! It's where the *row* in the *table* is physically located in relation to the index(es) that's relevant.

Mike, this is where your logic is no longer aligned with reality. Your statement above *proves* you simply don't understand the CF. Sad but very much true.

> An index rebuild coalesces nodes and aligns them with the underlying
> table.

Mike, Mike Mike ...

This is totally, absolutely and completely wrong, wrong, wrong !!

When you rebuild an index (or coalesce), the logical order of the index entries remain exactly the same. Let me say that again, it might just help. *When you rebuild an index (or coalesce), the logical order of the index entries remain exactly the same.* Therefore, after the rebuild, even if you reduce the number of nodes by half, the logical order of the index entries remain exactly the same. Therefore, the number of physical I/Os required to read the *table* remains *exactly* the same. It must. Therefore, the CF remains *exactly* the same. It must.

For some reason, you are under the delusion that the CF is impacted if you rebuild an index. If you rebuild an index, the CF is not impacted.

Again, the statement above, which to your embarrassment you keep repeating publicly *proves* that you do not understand how the CF works. If you did, you would know that the CF is not changed by simply rebuilding the index.

>Now, in many cases this reduces the clustering factor,

Mike no. This is getting silly. By simply rebuilding an index, the CF is *not* changed. Please, give us one demo, just one, only one, just a tiny, tiny little one demo that show the CF of an index changing after a simple rebuild of an index. Like I requested in my previous email that you've conveniently not replied to, you simply can't.

>however, I have seem it stay the same (usually on truly random keys such as
> text, or concatenated columns),

actually, you've seen it stay the same *every single time* you've bother to look

>decrease (the desired outcome) or
> increase!

Mike, only because (possibly) your statistics were not up to date to begin with.

Mike, please, please, please, please show one example where you have fresh, up to date stats, you perform an index rebuild, you take new stats and the CF has changed. Just one example. I throw this out to anyone listening in. Please pick any index, no matter how "old", no matter how "broad", no matter after any number of "node splits", no matter what "ratio" of dirty or brown, or green or pink with yellow spots blocks, no matter what, analyze the index, note the CF, rebuild the index, check out the new CF. In *every* case, the CF will be the same. Everyone, please prove it for yourselves ....

>
> However, I may have stated things unclearly, the goal in index
> rebuilding is not to reduce clustering factor, that is actually a
> desired by-product,

No Mike it is not. Please someone, how many times has Mike made this fundamental error ? Not just here but in his other posts as well. I've truly lost count. And no matter how many more times he says it, it simply is not t rue.

Anyone who truly understands the CF would realize this.

> the goal is to ensure that the index is properly
> aligned with the underlying table

But Mike, that's what the CF is, only the other way around !! A measurement of how aligned the *table* is in relation to the index with the CF value representing the estimated number of physical I/O required to read the entire table based on a full index scan of the corresponding index.

Why can't you see that ...

> and that its entries are not sparse.
> Clustering factor ratios are just one of several indicators that can
> tell you an index needs to be investigated.

Mike. You are publicly recommending rebuilding indexes based on some ratio (how typical) of blocks to CF. If the ratio is too high, rebuild the index. Problem is Mike, after the rebuild, the CF remains *the same*. So you then

recommend that this index be rebuilt again. And again, and again, and again,
and again, and again, and again, and again, and again, and again, and again,
and again, and again, and again, and again, and again, and again, and again,
and again, and again, and again, and again, and again, and again, and again,
and again, and again, and again, and again, and again, and again, and again,
and again, and again, and again, and again, and again, and again, and again,

and again, .....

Doesn't that strike you as being rather pointless. Hummm, me too.

And that Mike, is why your quote is featured in my presentation. Because such a recommendation is total and utter tripe. *No matter the context* !!

Fortunately, your posts have confirmed you lack understanding in this area and the validity of using your quote in the first place. Hopefully, you will now, finally get it and realize the errors of your understanding.

And hopefully, finally, you'll be big enough to admit it and maybe even considerate enough to thank me (rather than attack me) for showing you the light.

You're confused a lot of people with your rubbish but if it means finally, such rubbish will now cease, it might just be worth it. My presentation is simply an attempt of mine to try and put such rubbish where it belongs. In the bin.

One last time, repeat after me. The CF is *not* changed after an index rebuild.

Cheers

Richard Received on Mon May 10 2004 - 08:58:12 CDT