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
ian_at_SLAC.Stanford.edu
-----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
- John.Dailey_at_ing-fsi-na.com 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"
>
>
> <jkstill_at_cybcon.c To:
> "Multiple recipients of list ORACLE-L"
> <ORACLE-L_at_fatcity.com>
> om> cc:
> (bcc: John Dailey/NAC/ING-FSI-NA)
>
> Sent by:
> Subject: Re: Rebuilding Indexes...
>
> root_at_fatcity.com
>
>
>
>
>
>
>
>
> 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: jkstill_at_cybcon.com
>
> 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: ListGuru_at_fatcity.com (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: John.Dailey_at_ing-fsi-na.com
>
> 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: ListGuru_at_fatcity.com (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: hamcdc_at_yahoo.co.uk
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: ListGuru_at_fatcity.com (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: ian_at_SLAC.Stanford.EDU
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: ListGuru_at_fatcity.com (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).
Received on Tue Dec 31 2002 - 09:54:20 CST
