Re: Running out of rollback

John Blackburn (jb2_at_qdot.qld.gov.au) wrote:
: Gary Nilges (gnilges_at_tyrell.net) wrote:
: > I'm trying to delete about 7 million rows from a 12 million row ORA 7.1
: > database with a Pro*C program. I open a cursor selecting all the rows that
: > meet my age criteria (older then 90 days). I then spin through my cursor rows
: > and do a 'delete where current of'.
: >
: > My problem is this. I keep running out of rollback space in the declare cursor
: > statement. Below is the text of my statement and the error. My rollback
: > segment grows to about 300 meg before bombing.
: >
: > I was told that under certain circumstances declare cursor and select
: > statements cause rollback activity, though I really didn't expect it in this
: > case. Any ideas how this application can be changed to avoid rollback
: > activity? Any help is appreciated.
: >
 

: In my experience, deleting rows causes rollback activity for the simple
: reason that you might need to rollback your transaction thus 'undeleting'
: all your rows that you just deleted.

Gary,

I had the same problem as you with a table of about 1 million rows. The problem is that declaring the cursor means that the database has to store a read consistent image of the data you are selecting. Since the table is so large and hence the cursor is open for a while it keeps a copy in the rollback so that other users can continue to update the data without you seeing any changes in what the cursor contains. In your case, as the data is deleted the database creates rollback entries such that the cursor still sees the data before the deletions. Add on top of that the actual overhead for being able to roll back the deletion and you have all of the deleted data going into the rollbacks twice! Got that?

It is a bastard of a problem, one way around it (though it is slow) is to loop the outer cursor and using rownum restrict it to select say 5000-10000 rows at a time. Delete where current of and then commit after each batch.

Ao the structure looks something like

commit_limit := 10000; /* or as much as you can fit in your rollback */

declare cursor c1
...
and rownum < commit_limit;

loop

   select null from

• equivalent to cursor above, without rownum restriction. if sql%notfound then break; open c1; loop when sql%notfound then break; fetch c1 into temp; delete where current of c1; end loop; close c1; commit; end loop;

So the outer cursor returns only 10000 rows at a time before a commit, before looping back to select the next 10000.

The code above is psuedo-code only, you will need to fix the syntax.

I think something that also worked was selecting the rows using a pro-c program and creating a memory structure containing the rowid's of the rows to be deleted rather than deleting them while the cursor was open.

Then do a loop through the memory structure deleting the row relevant to that rowid and commit as often as you like.

Presumably creating a temporary table cointaining the rowids of the rows to be deleted and then looping through that would work as well.

Hope this helps, if not feel free to drop me mail,

cheers,

--
Tony Sampson - alive and well and living in the state of South Australia.
email to: sampson_at_apanix.apana.org.au

		         TANSTAAFL, so I'm buying!