Help! - Rollback Segment Problem

RE: Blowing rollback segments when updating a large database.

We're currently developing a database application on a HP-9000 HP-UX platform using Oracle 7, using the Oracle Pro-C precompiler.

This is what we're doing in our programs:  

First, I select a very large number of records from a database table. Secondly, for each record selected from this table, I obtain additional information from other tables in the database. Then, lastly, I want to update the original database table with this modified or enhanced information.

This type of processing is done in several different places in our precompiler 'C' programs. I have gotten several types of rollback segment errors in different stages of the process.

The first thing that I tried was to use the CURRENT OF clause in conjunction with the FOR UPDATE clause. The problem here is that I cannot COMMIT any of my database updates inside of the SELECT while loop. The COMMIT conflicts with the FETCH statement. For this reason I modified the program to use ROWID instead of the FOR UPDATE OF / CURRENT OF combination. Now I can COMMIT inside of the SELECT while loop; however, now the data may be compromised due to conflicts of concurrent executions of the program using ROWID. Since the program was blowing with rollback segment errors, the DBA created an extremely large rollback segment. I specified this large rollback segment in a SET TRANSACTION which is placed immediately before the SELECT statement. The problem is that if I try to COMMIT any of my database updates inside of the SELECT while loop, the selected rollback segment is released I am right back where I started. The process is likely to blow on the next FETCH statement. If I commit ONLY at the very end of the entire SELECT while loop, the rollback segment must be prohibitively large.

Soon we are to move to an even larger set of input files and we are in a quandry. If anyone has any tips, insights, or helpful thoughts, they would be greatly appreciated.

 James Strickland             |    jstrickland_at_dbmsmail.dsac.dal.mil     
 FSACO/BE                     |    Voice: 614 692 9649                   
 Building 27-4                |    Fax: 614 692 9960                     
 P. O. Box 1605               |                                          
 Columbus, OH 43216-5002      |                                          


-------------------------------------------------------------------------