Re: Index management

"Richard Foote" <richard.foote_at_tbigpond.nospam.com> wrote in message news:<yUIkc.6619$TT.2282_at_news-server.bigpond.net.au>...
> "Howard J. Rogers" <hjr_at_dizwell.com> wrote in message
> news:409334fa$0$25007$afc38c87_at_news.optusnet.com.au...
> > Richard Foote wrote:
> > >
> > > Go to the download section at www.actoug.org.au, make what you can of
> the
> > > PowerPoint presentation, wait for the accompanying whitepaper/book, read
> it
> > > carefully too and hopefully you'll get the picture.
> >
> > Is there an emoticon for a big, BIG, BIIIIIIGGGG beaming smiley face.
>
> Hi Howard,
>
> I'm not sure. What about :) x 100 !!
>
> >
> > After having read the first slides containing the delicious quotes from
> > Herr Burleson und seiner freunde, I had to go and bake a doughnut.
>
> The difficult part was selecting which quotes to use, there are so many. I
> didn't want to single out any one person and I wanted to highlight how so
> many have got it so wrong. So I focused on those "experts" that seem to
> publish a lot of "stuff" ...
>
> >
> > After that I had indigestion with your 50-50 leaf block claim. Jonathan
> > claims it's 50-50 *on average*, but not by volume, I think. Interesting.
>
> No, it's definitely by volume. I showed this in a thread here a while back
> where after a leaf block split, the number of index entries in each block
> varied but the amount of used space sat at the 50% mark in each block.
>
> Look here:
> http://groups.google.com/groups?q=g:thl1973251333d&dq=&hl=en&lr=&ie=UTF-8&oe
> =UTF-8&selm=ant%259.39604%24jM5.100537%40newsfeeds.bigpond.com. where I
> demonstrate my "volume" claim The white paper also has an example.
>
> It's the same thread whereby Don Burleson showed his total ignorance of how
> indexes work and kinda opened my eyes that he wasn't quite what he claimed.
> Interestingly, almost the entire history of Don Burleson posts have been
> removed from the google archives, there's hardly a one left. As an example,
> all the posts in this thread where he made a fool of himself are no longer
> there, although thankfully for prosperity sake, his "words of wisdom" can
> still be found in among the posts of others. It's all very interesting don't
> you think, although it's probably a good move from Don's point of view to
> reduce the embarrassment of some of the stuff his written.
>
> >
> > However: I just wanna know why that Sharman guy gets all the invites and
> > I don't :-((
> >
>
> A lot of it has to do with the fact he's a local lad who lives in the
> neighbouring suburb from me !! However, our next User Group event is on 23
> June so if your interested in conducting a presentation in front of 60 odd
> folks and would like a lovely free lunch to boot, please let me know and
> I'll grab you a spot no worries at all !!
>
> Cheers
>
> Richard

Richard, Roger,

My what a mutual appreciation society! Warms my heart to see you two so cuddly. Richard, interesting presentation, of course as usual you ignore all the caveats and expansions that go with the expert quotes, rather reminds me of the fundamentalist bible thumpers we have in Georgia who take quotes and twist them to support all sorts of things...but I digress, I just wish you would give complete examples, of course it is always easier to tear someone else down than come up with original material. I try to never denigreate other folks in public, 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 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. 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.

It is odd that in nearly every case where a rebuild has reduced clustering factor, performance is also improved. From all of your postings this should not be the case. It is also odd that when indexes show up very broad, they also usually have large clustering factors and, a high percentage of non-reused space. Yes, an index will reuse space, but not very often in a high IUD environment and a rebuild or coalesce will reduce these indexes in size, thus reducing clustering factor and blocks used, making them once again more palatible for the optimizer and give higher performance.

Other than violating relational tenents, reordering table data will dramatically improve access times in many cases. Of course as the table ages it must be rebuilt over and over again, along with the index, to maintain this ordering. I hope those that follow this advice have lots of maintenance window time! Most of my clients barely have time to backup their terabyte size databases, let alone reorder all of their major, multi-million row tables. I heard that first from Don Burleson when I reviewed one of his first Oracle books. It was a carry over from Mainframe tuning that is still valid. Nice of you to give the same advice as Don!

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?

Mike Received on Wed May 05 2004 - 16:52:21 CDT