Re: Index rebuilding

Hi Dick

Comments embedded

> Looked at Richard Foote's paper. Don't know about that.

That's fair enough, I'll be happy with a 67% hit ratio ;)

> I did prove to
> OTS several years ago that a block could get "lost" in an index due to
> deletion/updates that left it empty.

Hell, I lose stuff all the time.

>I believe that got finally fixed
> in Oracle 8i.

Phew !!

> I've still seen cases of index's becoming unbalanced, I
> know the docs day it's impossible, but it does happen without the index
> height increasing.

Are you confusing an "unbalanced" index, with a skewed one (a term I picked up from Jonathan Lewis) ?

Because, believe me, it just can't happen. I would love to see just one example, I honestly truly woolly would. Next time you "see" one, please document it, take a photo, whatever. Some evidence please, else such claims are just myth spreading hype...

> And I still believe that index deletes don't get
> flushed so efficiently, as Richard suggests. If that was the case then
> I can't explain why an index rebuild can cause an index to shrink by 30%
> or more.

Umm, perhaps because the deleted space that's cleaned out is not subsequently re-used. Remember, Oracle sometimes cleans out the deleted space but as the presentation suggests, there may be scenarios where this space is not subsequently reused (eg. when a table/index is dramatically shrink in size).

The presentation quite clearly describes how an index can shrink by 30%, it's no mystery ...

> And recent experience still shows that a rebuild can cause
> significant performance improvement.

Can you perhaps share with us all these recent experiences ? What were the characteristics of the index and associated table, what was the pre-performance, what was the post performance, were there any changes to the execution plans, how was the index accessed, what triggered the rebuild, what proof do you have that is was the rebuild and not other factors. Your 13 little words above make an interesting claim, but without any details, they remain 13 little words...

The presentation discusses various scenarios when an index rebuild may be beneficial. I've never said an index rebuild won't ever help, I clearly define scenarios when they could. Are you suggesting that your "recent experiences" don't match up with the scenarios discussed, if so please elaborate.

> And Oracle has provided the
> capability to rebuild indexes which is not trivial.

Of course. Great for moving indexes from one tablespace to another. Great for many other maintenance operations and in some rare cases, great for improving performance.

Thank you Oracle !!

> Therefore, NEVER
> use the word "never" unless your absolutely certain that under all
> circumstances it will be absolutely true. And in the current context,
> that is the truth, that is, never can never be an absolute.

Don't want to be all philosophical here, but please note I've never claimed that one *never* needs to rebuild an index. In fact, the presentation goes to some pains to explain why it *might* be necessary. The presentation mentions a number of common myths and tries to explain and show why they're a nonsense. If one's justification for regularly rebuilding is any of these myths, then one may just want to reconsider ...

>
> BTW: Since we've a few "myth busters" in the group. I appreciate the
> effort these people put into "myth busting", even if they are later
> proven to have erred.

>At a very minimum they start discussion and
> re-examination of commonly held beliefs that can have changed or lost
> significance over the years(like it's best to have all of a tables data
> in the first extent). Such discussion, although sometimes the start of
> "Holy Wars", is healthy (not the Holy War though) and a necessary part
> of all of us growing.

God, I hate "Holy Wars" (no pun intended).

>That being said, let it be noted that I agree to
> disagree, in part, with Mr Foote.
>

And that is your right.

Just let me know which "parts" and I'll gladly show why you're wrong :)

Cheers

Richard

--
http://www.freelists.org/webpage/oracle-l