Re: Index rebuilding
- From: "Richard Foote" <richard.foote@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 16 Nov 2004 00:10:52 +1000
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
