Re: Large deletes (Help)

In <1993Aug31.211721.19345_at_convex.com> cgraham_at_convex.com (Chip Graham) writes:
>In article <260cfe$9de_at_charm.magnus.acs.ohio-state.edu> dmoney_at_magnus.acs.ohio-state.edu (Dean R Money) writes:
>>
>>Also, as far as doing large end-of-year deletes go, is there a faster
>>way to delete selectively than DELETE FROM MY_TABLE WHERE...? Even
>>with an index, deleting hundreds of thousands of rows takes a long
>>time. It's especially bad when I have to break down my deletes to
>>alleviate ROLLBACK SPACE problems.
>>
>One solution, not the best,(but I don't know that one), is:
 

>create a new table as:
> create table new_table as (select * from old_table
> where .....[what you want to keep]);
>
>drop old_table.
 

>create old_table as (select * from new_table);

Or just :

     rename OLD_TABLE to NEW_TABLE;

>This will clear up all fragmentation from the old table, and put all
>of your data in as much contiguous space as possible. It should speed

Put the STORAGE clause in the create. That way the new table can be created with enough space to hold the expected growth in the size of the table.

>things up quite a bit. It is also faster than delete from. Be sure
>to not build your indexes until after the new table is built. (and
>of course, always back up your database before doing this!!).
 

>You may also want to change your rollback segments for large jobs like
>this one. Then change them back for production. That works for us. It
>is tedious though, but we found it to be necessary.

Alternatively make sure that one rollback segment has a lot of space available to it and then use SET TRANSACTION USE ROLLBACK SEGMENT roll_seg in the SQL script.

-- 
--
Ian Dixon                       Email : idixon_at_infocom.co.uk
Reading, England