Re: fragmentation

Hi!

> - Are you trying to keep the index available to users while you're
> rebuilding? I'm assuming this is the reason you are looking at rebuilding
> the index twice. Or is it because rebuilding an index probably won't cause
a
> large sort?

Sort is still needed, even when rebuilding. It's just that less data is required to read when rebuilding from index than building from table.

Rachel, you probably already planned using nologging and possibly parallel clause + setting sort_area_size and maybe db_file_multiblock_read_count, depending on your extent size.
I would recommend you to rebuild one index partition as you planned and then build other, similarly sized partition from scratch. Then measure the time & IO difference.
When building from scratch, you have to read lots of data from table, sort it (to temp), then copy it back from temp. For double rebuilding, you will have to read lesser amount of data, sort it to temp, copy it to your big tablespace. Then read it again, sort it to temp and copy it to your original database again. Of course, verify that there's no other extents in your original tablespace, otherwise you might not be able to resize your files smaller.

Also, depending on your IO layout, if you got any spare disks for temporary use, you could make additional temp tablespace on them, set the index recreating user's temp ts to that one to avoid disk contention..

Tanel.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tanel Poder
  INET: tanel.poder.003_at_mail.ee

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