Re: Large deletes (Help)

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

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

-- 
Chip Graham      cgraham_at_convex.com         +1 214 497 4642
Convex Computer Corporation 
3000 Waterview Parkway     
Box 833851, Richardson,Tx,75083