RE: Index rebuilding

>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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Goulet, Dick Sent: Friday, November 12, 2004 1:41 PM
To: oraclel_at_weikop.com; oracle-l_at_freelists.org 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_at_gmail.com]=20 Sent: Friday, November 12, 2004 12:44 PM To: oraclel_at_weikop.com
Cc: oracle-l_at_freelists.org; steve_at_trolltec.co.uk Subject: Re: Index rebuilding

On Fri, 12 Nov 2004 11:49:46 +0100, Karsten Weikop <oraclel_at_weikop.com> 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

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


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