Great point. A perfectly randomized used ideal B-Tree index would be 75% full: just split blocks would be 50% full, there would also be some blocks that had reached 100%, and of course most blocks would fall somewhere in between. The average for all blocks would be 75% full.
Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -----Original Message----- Sent: Monday, December 30, 2002 11:09 AM To: Multiple recipients of list ORACLE-L The space saving is good if you are not planning on doing much more with those indexes (ie dml). The space saving might be very very bad if there is lots of dml to come. If you're indexes reached "equilibrium" during normal operation of (say) 70% used, and then you rebuild them with (say) pctfree 10, they pop up to 90% used and thus the saved space. But if normal operations continue, they will eventually drift out to 70% used again and what's worse, you might get a bucketload of index block splits during the process...could be a nasty hit on dml performance hth connor --- [EMAIL PROTECTED] wrote: > > Jared, thanks for the "asktom" article, very > interesting reading - I'm not > 100% sure I agree with him though. I just > recently went through the > exercise to rebuild our indexes into locally-managed tablespaces and I > saved over 100GB of space through the rebuild > process - as an example I had > a 51GB unique index that went to 30GB after the > rebuild (I'm not implying > locally-managed had anything to do with the space > savings, that was just > for reducing/eliminating fragmentation). The > indexes had not been > rebuilt in over a year. Our DSS database is > next; I expect to save at > least that plus more in space. Not to discount > Tom's arguments but I was > very happy with the space savings and depending on > your environment that > can be very helpful/worthwhile. > > Just my opinion - guess it depends on your > objectives. This > discussion has definitely got me thinking in some > different directions > which is always a good thing. > > thanks again, > > John D. > Atlanta, GA > > > > > > > > > > "Jared Still" > > > <[EMAIL PROTECTED] To: > "Multiple recipients of list ORACLE-L" > <[EMAIL PROTECTED]> > om> cc: > (bcc: John Dailey/NAC/ING-FSI-NA) > > Sent by: > Subject: Re: Rebuilding Indexes... > > [EMAIL PROTECTED] > > > > > > > > > 12/26/2002 10:13 > > > PM > > > Please respond to > > > ORACLE-L > > > > > > > > > > > > > > Though I have published a script for determining > indexes that > need to be rebuilt, and then rebuilding them, I > have to say that > this is almost never necessary. > > Why are you rebuilding indexes? About the only > reason for ever > doing so is that the BLEVEL >= 5. > > goto asktom.oracle.com, and do a search on 'index > rebuild'. > > Currently, the third article may be of interest. > > Jared > > On Thursday 26 December 2002 12:24, Richard Huntley > wrote: > > Anyone have any useful scripts for doing this? > > > > TIA, > > Rich > > ---------------------------------------- > Content-Type: text/html; charset="iso-8859-1"; > name="Attachment: 1" > Content-Transfer-Encoding: 7bit > Content-Description: > ---------------------------------------- > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: Jared Still > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 > http://www.fatcity.com > San Diego, California -- Mailing list and web > hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). > > > > > > > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 > http://www.fatcity.com > San Diego, California -- Mailing list and web > hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). > ===== Connor McDonald http://www.oracledba.co.uk http://www.oaktable.net "GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day" __________________________________________________ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: MacGregor, Ian A. INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
