Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Index management

Re: Index management

From: Richard Foote <richard.foote_at_bigpond.com>
Date: 6 May 2004 01:01:50 -0700
Message-ID: <69f6c1c8.0405060001.1dc23462@posting.google.com>


Comments embedded

mikerault_at_earthlink.net (Mike Ault) wrote in message news:<37fab3ab.0405051352.11555818_at_posting.google.com>...
>
> Richard, Roger,

Mike,

In your typical fashion, you can't even get the names right ...

>
> My what a mutual appreciation society! Warms my heart to see you two
> so cuddly.

I know it's lovely. Still, we have a long way to go to match the "closeness" between Don and yourself ...

>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,

Not only do you digress , but you're of course utterly wrong (again). I haven't twisted anything, each quote stands up in it's own right (your's especially) and this attempt (again) to suggest otherwise and to try and deflect the issues is both a little tiresome and immature.

> 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 haven't torn anyone down, Mike, you're getting emotional again. I've simply highlighted how many of these silly Oracle myths are spread by many of these so-called experts, by giving very clear and concise examples. I purposely listed a range of quotes by a range of different authors to highlight how common this myth spreading is. Each quote is inaccurate no matter the context, as we'll look at later with your specific example.

With respect to "original material", if you actually *read* the presentation, you'll find a great deal of it is original and based on subject matter that is not so well documented.

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!

Mike, you're getting emotional again. Highlighting common errors and misunderstandings and *correcting" them is not castigating anyone. It's a real shame that you find it so hard to understand that ...

Dealing with constructive criticisms is not one of your strengths is it Mike.

>
> 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?)

Yes, let's speak about you for example. Your quote above about the "number of dirty blocks in the table" is a clear example of you not having a clue what you're talking about. Your other quote about "sometimes rebuilding will not improve the CF" is yet an example.

Mike, please do me a favour and try out the following little test.

Pick any index you want, any, compute stats so they're 100% accurate and note the CF in dba_indexes. Jot it down on a piece of paper.

Then rebuild this index with any pctfree you like, compact it as much as you like. Then re-compute the stats and check out the new CF with the previous value.

What do you see ?

The values are identical. While you scratch your head and manually close your lower jaw, pick another index, and repeat the test.

Keep repeating the test until you finally get the point that by simply rebuilding the index, you do not change the CF. Why is this ? Because the order of the index entries is unchanged, the order of the table remains unchanged, therefore if you only realized what the CF actually represents, you would see that the CF can therefore not change as the relative order of the *rows in the table* do not change with respect to the index.

You've just confirmed in your post here that you clearly don't understand this but let's now look at your quote in my presentation:

"Deleted space is not reclaimed automatically unless there is an exact match key inserted. This leads to index broadening and increase in the indexes clustering factor. You need to reorganize to reclaim white space. Generally rebuild index when the clustering factor exceeds eight times the number of dirty blocks in the base table, when the levels exceed two or when there are excessive brown nodes in the index."

Looking at the quote "Generally rebuild index when the clustering factor exceeds eight times the number of dirty blocks in the base table", we can now see that it is entirely wrong, no matter in what *context* you want to look at it. Rebuilding an index based purely on the value of the CF as you describe is rubbish, the CF remains unchanged.

Now, let's look at the other parts of the quote:

"Deleted space is not reclaimed automatically unless there is an exact match key inserted." is rubbish, again no matter the context. My presentation clearly demonstrates why this is the case.

"This leads to index broadening and increase in the indexes clustering factor" is rubbish, again no matter the context. Having deleted or wasted space does not increase the CF. Inserting (or migrating) rows so that the order becomes less aligned *in the table* in relation to the ordering of the index is how the CF degrades.

"You need to reorganize to reclaim white space" is in most cases wrong. My presentation describes those rare scenarios where excessive white space may be an issue but the suggestion that you "need" to reorganise to reclaim white space is wrong.

"Generally rebuild index when the clustering factor exceeds eight times the number of dirty blocks in the base table, when the levels exceed two or when there are excessive brown nodes in the index" is wrong, wrong wrong. The CF bit we've discussed, totally wrong. The exceeds 2 levels myth is covered in the presentation. Why you recommend rebuilding *all* your large indexes again and again for no benefit is beyond me. Simple fact. Large indexes require more levels and if by rebuilding these larger indexes you don't reduce the level, why bother. The excessive brown nodes is discussed in the presentation where it shows how this space is reusable.

So really Mike, your quote, no matter the *context*, doesn't really stand up at all well does it ...

>
> 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.

Yes Mike but aren't you clouding the waters here. This is dropping and re-creating a totally *different* index, which is hardly the same thing as recommending the rebuilding of an existing index so that you can end up with the same CF ...

>
> It is odd that in nearly every case where a rebuild has reduced
> clustering factor, performance is also improved.

Really odd I would say as rebuilding an index doesn't effect the CF !! The old "performance has definitely improved although I can't really tell why, or precisely by how much ..."

And you *still* promote this rubbish. How many books have you written ...

>From all of your
> postings this should not be the case.

If you *actually read* my presentation you'll see that an improved CF most definitely can improve performance. Unfortunately, you have no clue how to improve the CF ...

>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.

Mike, suggesting there is a co-relation between the size of an index and the *effective* CF is wrong wrong wrong. Yes, large tables have larger "values" for the CF because they obviously have more rows. However, large indexes can have a perfect CF (equal to the number of blocks in the table) and small indexes can have an awful CF (equal to the number of rows in the table). Mike, I have the very very strong suspicion you have no idea what the CF represents and how it's the order of the *table* in relation to the index that governs the CF, pure and simple.

My suspicion is right isn't it ?

>Yes, an index will reuse space,

Didn't you say only if the new index value is the same ? I'm sure you did, oh, look at your quote ...

That's funny you're got to admit !!

>but not very often in a high IUD environment

Why is that Mike ? Actually read my presentation first, then answer my question, it might just change your mind ...

> and a rebuild or
> coalesce will reduce these indexes in size, thus reducing clustering

Mike, this is rubbish, we've been through this. The fact you get this wrong again and again kinda highlights your lack of understanding here ...

Was that 10 books Mike ...

> factor and blocks used, making them once again more palatible for the
> optimizer and give higher performance.

All sounds very good and practical in these performance books you write. Unfortunately, the truth is somewhat different to your sense of reality. *Read* my presentation to learn and find out why.

Perhaps I should have my presentation published ...

>
> 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.

Of course rebuilding a table is not something one can do at the drop of the hat, if at all. But at least by rebuilding and re-ordering the table, you *do* influence the CF.

>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!

When Don is right, I agree with him. When Don is wrong, I don't agree with him. For some reason, you have a problem with that.

>
> 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?

As I've explained, your quote in whatever *context* you like to place it in, is utter tripe. Pure and simple. My presentation explains, as I've briefly attempted to here, why that is the case.

Mike, why don't you get off your little pedestal of how you are sooooooo good and how I'm soooooo bad, and simply thank me for pointing out why you are so utterly wrong and for teaching you how indexes and the CF in particular work in Oracle. You might even mention me in your next book for showing you the light (thought I'll use a biblical example too).

Be a nice touch don't you think.

One last point.

Just sit back and think about all the people you've taught over the years and who have read your book and who now wrongly think that deleted index entries are only reused if identical values are inserted, who rebuild indexes in the naive hope that the CF will improve, who rebuild all their massive indexes again and again in the hope they will drop a level, who think that a large value for the CF is bad simply because the table is large, etc. etc. etc.

And yet Mike, you think *I'm* the bad guy ...

Now that really is funny.

Richard Received on Thu May 06 2004 - 03:01:50 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US