Re: Fetch accross commit

On 18 Dec 2003 19:26:29 GMT, Chuck <chuckh_nospam_at_softhome.net> wrote:

>Oracle 8.1.7 on Win2k
>
>I have to delete a large number of rows from a very large table. I cannot
>do the delete as a single transaction as it will generate gigabytes of
>rollback data and blow out the largest RBS I have. If I delete individual
>rows inside a cursor loop with a commit every N rows, will that cause
>unnecesarry performance problems? For example, will the select be
>executed again after each commit? I remember being told this would happen
>by someone way back in version 7.1.
>
>The purge will run after hours and there should be no other selects going
>against the table so I should not create any ora-1555's.
>
>I am not deleting a high enough % of the rows to justify recreating the
>table with just the rows I want to keep. Plus there are several Fk's
>involved and I really don't want to have to go through recreating them
>which I would have to do if I took the "create a new table" route.

I agree with Brian's reply. Apart from that, the only sensible route seems to split up the big transaction into several smaller transaction according to a formal criterion like all records in a single state or all names starting with an 'A' and commit that smaller bulk transaction

--
Sybrand Bakker, Senior Oracle DBA