Re: Delete Rows without Rollback ?

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:23:55 CDT