Re: considerations for rebuilding and coalescing

"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.

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html