Re: Index management

Hi Mike,

Not sure why you've chosen to make your reply at this point in the thread, but anyways, please find my comments embedded.

"Mike Ault" <mikerault_at_earthlink.net> wrote in message news:37fab3ab.0405101544.4267b424_at_posting.google.com...
> Well,
>
> After extensive testing (a couple of hours worth) using various
> combinations of insert, update and delete I was not able to achieve
> differing values for clustering factor with index rebuilds unless the
> base table is also rebuilt (which would be expected due to the
> redistribution of rows as pctfree is applied to rows that have been
> updated.) However, I was not able to fully simulate the effect of
> possibly hundreds of simultaneous IUD activities, however, that is a
> moot point.

Thankfully, at last, we finally come to the same conclusion.

>
> Note that rebuild of the base table after thousands of IUD actions
> resulted in a net increase in clustering factor. To test this create a
> simple CTAS of the dba_objects view, create a couple of indexes on it,
> analyze the table using compute and check the CF of the indexes, then
> create a PL/SQL or other script that does insert, update and delete of
> various combinations of indexed values (I like to use a modulus of
> various constants against the object_id so I get a spread across the
> table with my statements). Once you complete your changes, do another
> table analysis, then recheck the clustering factors. Then rebuild the
> indexes and recheck (afer re-analysis). If you have done a number of
> updates, then rebuild the base table (alter table x move;), you will
> also have to rebuild any indexes on the table, then analyze the table
> again and check CF.

This has never been disputed.

>
> The problem lays with the fact that this was not a complex environment
> involving millions of rows and many users, but for the time being I
> will concede the point that a simple index rebuild, in a simple
> environment, will not result in a change to clustering factor for a
> standard b-tree type index. I will change my lectures and update the
> tuning materials I have published to reflect this finding (I may even
> give a word of mention to the fellows Foote and Rogers...) when I am
> wrong, I admit it.

Let me assure you, the same will apply to whatever environment you choose as the same principles always apply.

An appropriate acknowledgment is always nice, but what is more appreciated is your commitment to correct your publications for future benefit.

>
> However, the CF to used baased table block ratio still tells you what
> indexes are becoming totally randomized and can still be used as a
> diagnostic pointer should the cf to used blocks ratio change
> substantially (this of course means tracking it for major tables.)
> Also, since it is a direct factor in index cost analysis for the CBO
> steps such as re-ordering concatenated index columns or rebuild of the
> base table in index order to reduce clustering factor are still valid.

Again, rebuilding the *table* when possible and appropriate has never been disputed.

>
> I still advocate rebuild of indexes that are sparse or that
> demonstrate improper levels for their complexity
>
> The decreases in clustering factor I noticed at sites may have been
> due to stale statistics, use of estimate rather than compute or other
> factors.
>

As I mentioned elsewhere in this thread, that has been my suspicion when you insisted on seeing the CF change after a rebuild. Again, we agree.

> However, I will endeavor to check this in a more complex IUD situation
> than I can simulate on an XP laptop and if anomalous behavior (i.e.
> reduction in CF) does occur, I will report back to the list with the
> scenario and the statistics.

Please do.

Mike, allow me please to bring this discussion around full circle to where it all began. You accused me of using your quote (and others) inappropriately in my presentation, suggesting that I was quoting you out of context and suggesting that my motives were not honourable. Please re-read your first post to remind you what you said.

Now, in the clear light of day, I hope you now see that the whole purpose of the presentation was to try and squash these types of "myths" and technical misconceptions that are so common in the Oracle community. The purpose of using the various quotes was not to somehow discredit the authors (we *all* make mistakes) but to highlight just how common these misconceptions are and by so many different, well known people in the Oracle community.

Each quote stands on its own and each quote clearly illustrates a common area of misunderstanding.

By finally admitting that you were wrong in your perception of how the CF works, I truly hope you will also now agree with me that your quote is likewise wrong, with no ifs or buts. As a direct result of my presentation (and the following discussions here), you now have a much better understanding of the CF and how it's impacted in Oracle. That is a huge positive and if others can also follow a similar path, the presentation would have achieved it's goal.

Mike, I have to acknowledge that admitting one is wrong is not always an easy thing to do (I've had much practice myself believe me). By doing so, I give you much credit and my opinion of you has increased immeasurably as a result.

Time to move on I think ...

Regards

Richard Received on Tue May 11 2004 - 05:03:18 CDT