Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: considerations for rebuilding and coalescing

Re: considerations for rebuilding and coalescing

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 31 Aug 2006 20:42:15 +0100
Message-ID: <CM6dnW-i67YKpmrZnZ2dnUVZ8sidnZ2d@bt.com>

"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
Received on Thu Aug 31 2006 - 14:42:15 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US