Re: Problem with Cursor and Commit

"Rick Agolia @ Home" <sagolia01_at_earthlink.net> wrote in message news:jbWr6.15077$R_6.1563005_at_newsread2.prod.itd.earthlink.net...
> I have a situation where I'm setting a cursor on a very large table
 (between
> 3 and 10 million records). I'm extracting data from each row in that
 table
> and writing it to a second table. I want to do a commit at some interval
> (about 1000 updates). The problem that I'm having is that performing the
> commit on the updates is closing the cursor on the 'input' table.
>
> The apps are written in Pro-C on an HP-Unix box. Help, I don't really
 want
> to go 3 million recs without a COMMIT. Actually, I may be committed if I
> let that go into production. The program was written MODE=ORACLE.
>
>

Perhaps a similar problem I encountered may help. I worked on a system containing large tables - but each table contained a 'batch number' column. Loads occurred in such a way that there were never more than 5000 recs in a batch...

My original query covered the entire table - and encountered snapshot too old after ~24hrs (there were a fair number of recs, and the update was complex). After modification to include two cursors, the problem went away.

New definintions were something like...

outer curs -- select a list of batch numbers requiring processing (eg by using DISTINCT, if the update is to all rows)

innter curs -- process all records for a given batch number

The program logged status into a control table and committed after completion of each batch number (one processing rule we had was that it must be possible to kill and restart batches without side-effects). I've used similar approaches ever since, although I suspect that recording the outer cursor values into a PL/SQL table would be slightly cleaner... Where there is no batch number available, I pick a column as a substitute - for example, key ranges of an integer PK would probably be suitable.

You'll have to consider a locking scheme also...

Cheers,

Richard Gowan Received on Fri Mar 16 2001 - 01:12:24 CST