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
Received on Thu Aug 31 2006 - 16:21:41 CDT
