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