Cursor and Memory problems

Hi All,

My application has to load 5 million records into a temp table everyday. After it loads it has to process all the records, multiple records for the key field (for e.g. SSN #) and squish them into one record per key and insert into another table. I know this is an ideal scenario for the latest 9i ETL (Extraction, Transformation and Loading) but we are using 8.1.7. My question is that I have a cursor that process all the 5 million records and inside the FOR Loop of the cursor all the logic to compress multiple rows for the same key into one key takes place. It currently takes 5 hours to process all the records on my dev machine. I have tuned every other SQL but the one that opens the cursor, Explain plan says that it is a FULL TABLE SCAN, as expected. How does cursors work while fetching a large number of rows.

My understanding was that it would do a full table scan once, retrieve the number of records that can fit into memory, establish a watermark at that level, process the records and then retrieve the next set of rows from the disk into memory. Am I correct in that assumption? If So, how can I speed it up. I need to reduce the number of hours to 3 hours. I increased the db_block_buffers parameter to 3 times its original size with no impact on performance. Any help will be appreciated?

On a separate note, Does anyone know how I can get all the postings in this newsgroup in a CD. I know most of the questions I have, have been asked by someone else already. The old postings are either gone or my machine becomes too slow when I download all the headers.

Thanks a lot, Venki Received on Wed Sep 19 2001 - 16:12:58 CDT