Re: considerations for rebuilding and coalescing

From: Ben <balvey_at_comcast.net>
Date: 1 Sep 2006 04:36:55 -0700
Message-ID: <1157110615.762440.224590@m73g2000cwd.googlegroups.com>

DA Morgan wrote:
> Jonathan Lewis wrote:
> > "Ben" <balvey_at_comcast.net> wrote in message
> > news:1157050935.233652.309200_at_i3g2000cwc.googlegroups.com...
> >> joel garry wrote:
> >>> Ben wrote:
> >>> <snip>
> >>>
> >>> On deleting and rebuilding quickly:
> >>> http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:6407993912330
> >>>
> >>> On index internals:
> >>> http://www.miracleas.dk/images/upload/Docs/Richard%20Foote.pdf
> >>>
> >>> Freelists:
> >>> http://groups.google.com/groups?q=jonathan+lewis+insubject%3Afreelists&start=0&scoring=d&num=10&hl=en&lr=&as_drrb=q&as_mind=1&as_minm=1&as_miny=1981&as_maxd=18&as_maxm=8&as_maxy=2006&safe=off&
> >>> http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:950845531436
> >>>
> >>> jg
> >>> --
> >>> @home.com is bogus.
> >>> http://online.wsj.com/public/article/SB115586867122639111-cW9juzaIBrDbHoHmZEplq3_dltM_20070818.html?mod=blogs
> >>
> >> Whew, I finally got through that enormous thread on asktom. I've looked
> >> at these and none of them really answer my question on how much space I
> >> need to account for to rebuild my indexes. If someone can please help
> >> me out here, I'd appreciate it.
> >>
> >
> >
> > As a rough guideline -
> >
> > Assume that since you have eliminated 40% of the
> > table data that you will also be able to claim back
> > 40% of the 50GB index space currently allocated.
> >
> > Oracle will build the replacement index in the
> > "right place" so if you rebuild it into the same
> > tablespace, you need about 30GB for the new
> > index as well as 50GB for the old index.
> >
> > However, you may not be able to rebuild the
> > index without dumping and merging sort runs
> > to the temporary tablespace before copying
> > the final sorted data set into the new index.
> > If this occurs, you will probably need quite a
> > lot more space in the temporary tablespace than
> > the final 30GB. The actual requirement is highly
> > variable - depending on how many sort runs you
> > produce and how large they are. But as a minimum
> > you can add
> > (6 + number of columns in index) *
> > number of rows in index
> > to the 30GB that the index will eventually be, as
> > the way that Oracle holds sort data has more
> > overheads per item than normal row data.
> > If you are VERY unlucky, you may then have to
> > double the total, to allow for Oracle reading and
> > merging nearly the whole data set back to the
> > temporary tablespace.

>

> To know the size an index will be, in advance of building it,
> use DBMS_SPACE.CREATE_INDEX_COST. There is a demo in Morgan's
> Library at www.psoug.org.
> --
> Daniel A. Morgan
> University of Washington
> damorgan_at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Group
> www.psoug.org

The TEMP space that is used, is that actually in the TEMP tablespace or is that TEMP space in the tablespace that you are building the index? Received on Fri Sep 01 2006 - 06:36:55 CDT