Re: Delete Rows without Rollback ?

There may be another option as well,

you could create a very large rollback segment and use the SET TRANSACTION statement to tell your delete to use this rollback segment. This will eliminate any 'SNAPSHOT TOO OLD' errors provided you size the new rollback segment large enough.

Allan Nelson <nels212_at_austin360.com> wrote in article <35D6EB7B.77530AE9_at_austin360.com>...
> John Finn wrote:
>
> > Does anyone know how to "DELETE FROM TABLE WHERE ..." without the
> > rollback overhead? If I have 1000000 rows and I want to delete 50% of
> > them without posibility of a rollback and reduced transaction overhead,
> > is there a way to do this?
> >
> > Thanks
> > jfinn
> >
> > pls reply to group only.
>
> You won't be able to do this. Oracle will always post the deletes to
> rollback segments to permit transaction rollback. The best I think you
> can do, if snapshot too old messages are a problem, is to select the row
> id's in question into a PL/SQL data structure and then commit between
> appropriate groups of deletes. This will keep your active rollback
> segment usage down, but it will not prevent the change records from each
> delete being written in the first place.
>
> Another way to do it if you are really determined to do this without
using
> rollback segments is to dump the table to an ascii file, truncate the
> table, do the deletes on the OS and then import the data back in with the
> fast path loading options.
>
> Allan
>
>
Received on Sun Aug 16 1998 - 09:55:35 CDT