Mike Ault's thoughts on various topics, Oracle related and not. Note: I reserve the right to delete comments that are not contributing to the overall theme of the BLOG or are insulting or demeaning to anyone. The posts on this blog are provided “as is” with no warranties and confer no rights. The opinions expressed on this site are mine and mine alone, and do not necessarily represent those of my employer.

Tuesday, March 15, 2005

It is Totally Amazing...

I will begin blogging with a few thoughts on the time and energy other "experts" put into questioning everything said. I find it amazing the folks such as Howard J. Rogers can spend so much time and energy beating dead topics to death. Most of us have moved beyond arguments about index rebuilding and coalescing. I suggest them when it is indicated that it will help, it is one tool in a tuning experts arsenal. When I suggest a rebuild or a coalesce of an index in Oracle database systems it is only after a complete analysis points to the index as a problem.

Business must be slow down under for Howard to have so much free time. I have to grab a few minutes between running for the plane, working with customers all across the country and trying to keep up with my remote monitoring and tuning committments to swat at this annoying gadfly.

As to the whole debate (well, at least from Howards viewpoint it is a debate, for me it is just a tool to use when indicated) about reordering a column order in a concatenated index, again, Howard and others seem to feel that once an index is created it is sacred and can't be touched. Obviously they have never worked with a poorly crafted, poorly indexed third-party or custom generated application, for that matter, they must have never worked with Oracle's own Applications. I have seen applications where a table had 29 indexes. If I followed Howards way of thinking each would be a sacred object that I shouldn't touch because obviously the need for each was carefully considered and it would bring down the entire application to change it. Hog wash.

It merely shows that Howard spends too much time with theory and not enough in real-world tuning situations. I have been at over 24 client sites in the last 6 months doing tuning, optimizing and database evaluations. I find things that work and apply them as needed. My job is to help an ailing database back to health and to improve peformance of client applications. If this means that I drop, rebuild or otherwise change an index, then that is what it means. Heaven forbid, I even suggest adding an index occasionally!


Howard J. Rogers said...

Business is fine, thanks all the same. I do paid work for (approximately) 50% of the time. I do Oracle research for the other 50%. You could usefully do something similar.

I have never said indexes once built are sacred and untouchable, and it is rather silly of you to claim that I ever have. I have suggested that it would be wise to know what you were doing before you touch them, but that's quite a different matter. And it would help if that knowledge was up-to-date, and aware of things like index compression and skip-scanning. If it is, the DBA might happily trade a lousy clustering factor for a good compression factor, though you'd never imagine such a trade-off was legitimate from your writings on the subject.

If you're going to coalesce, rebuild, re-design, drop, add or whatever an index, know *why* you're doing it. Don't just fumble around and hope for the best.

But you do continue to advocate the fumble school of index maintenance, Mike. And you do so in books and articles and web postings... so how much 'complete analysis' you manage to pull off in those environments rather escapes me, I'm afraid. And the clustering factor stands mute but eloquent testimony to the rigorousness of your analyses.

Do I need to point out the evident contradiction in any case between the claim that you do 'a complete analysis' of a situation and your own assertion that you 'find things that work and apply them as needed'?

Which rather says it all regarding your credibility on the matter, so I'll leave it there.

Mike said...


Again you only focus on what you want to hear. CF is just one measure of an indexes condition. It is an easy one to see and the one that shows how well an index is ordered against its base table. The index size, and many other items are also used in analysis. Analysis of the SQL statements tht use the index is also done or recommended.

I would be afraid to turn my system over to you for analysis, you would spend so much time analyzing to the nth degree one problem that you would not see the forest of others out there.