Re: Large deletes (Help)

uidixon!idixon_at_infocom.co.uk (Ian Dixon) writes:

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

Both of these solutions will cause problems if there are referential data integrity constraints which refer to the table.

---
Michael Nolan, Sysop for the DBMS RoundTable on GEnie
nolan_at_notes.tssi.com, dbms_at_genie.geis.com
(posted from nolan_at_helios.unl.edu)