RE: Index rebuilding
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <DGoulet@xxxxxxxx>, <oraclel@xxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 12 Nov 2004 14:41:22 -0500
>in Oracle 8i. 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.
Depends what you mean by "unbalanced."
If you mean that number of levels from the root to the leaves varies, I
think Richard is pretty clear about why that can never happen, since in
Oracle's implementation indexes grow in height by splitting the root.
(Richard represents the tree as upside down, metaphorically, from trees you
see growing, so perhaps they grow in depth. I guess that makes the leaves
all blades of grass.) For Richard, at least in the paper in question, that
is certainly what he is talking about.
Now, on the other hand, if by unbalanced (versus balanced) you mean there
are more leaves to the right than to the left, Richard illustrates nicely
when and why that happens. So if you're looking at the diagram with a whole
pile of 25% full or 50% full buckets toward the left and all full or nearly
full buckets toward the right, I'd call that unbalanced. It looks as if the
tree, represented as a tree with the root at the bottom, would fall over. I
think that is the case, for example on his next to last page (but not by
much). Since it is running out of buckets that forces you to grow in height,
then if this is what is meant when someone says that an index has grown
higher than it needed to because it is unbalanced, it can certainly be a
valid observation.
If you're thinking someone means that leaves are at varying levels from the
root in an Oracle index when someone says a tree is unbalanced, then you
probably think they are expressing ignorance, idiocy or both.
Likewise, if you reduce the height by rebuilding so densely that you
immediately get the reclaimed height back, then that's a problem too. Of
course there are many pointers at each level, so if you go from 50% full to
60 or 75% full you'll be getting back a lot of buckets, especially if you're
always pushing toward the right and deleting from the left. Almost every
presentation of index structure I've ever seen is a bit misleading on that
point and Richard's is better than most. (Try making a readable picture with
8K worth of pointers to the next layer.) Likewise, there are more pointers
per level as well as more leaves per level if you move to a larger block
size.
When the index itself satifies all the columns required for a query, that
also presents a different performance dynamic.
Regards,
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Goulet, Dick
Sent: Friday, November 12, 2004 1:41 PM
To: oraclel@xxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: Index rebuilding
Looked at Richard Foote's paper. Don't know about that. 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. I believe that got finally fixed
in Oracle 8i. 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. 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. And recent experience still shows that a rebuild can cause
significant performance improvement. And Oracle has provided the
capability to rebuild indexes which is not trivial. 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.
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. That being said, let it be noted that I agree to
disagree, in part, with Mr Foote.
Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA
-----Original Message-----
From: Jared Still [mailto:jkstill@xxxxxxxxx]=20
Sent: Friday, November 12, 2004 12:44 PM
To: oraclel@xxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx; steve@xxxxxxxxxxxxxx
Subject: Re: Index rebuilding
On Fri, 12 Nov 2004 11:49:46 +0100, Karsten Weikop <oraclel@xxxxxxxxxx>
wrote:
> Please read the execellent paper from Richard Foote (which can be
> downloaded from Miracle's site):
> http://www.miracleas.dk/images/upload/Docs/Richard%20Foote.pdf
> Conclusion form this paper: Never Rebuild, but find the course to the
> problem.
Never?
I think you will find that statement as difficult to support as
'always rebuild'.
--=20
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
