MySQL Forums
Forum List  »  Stored Procedures

How to process very large result set from a dynamic cursor
Posted by: Joe Petronio
Date: September 24, 2010 04:27PM

I need to fetch all the records from a 200,000,000 row table, apply some transforms to most of the columns, then Insert them into a new table . I can't use Insert/Select or Create Table as Select because it locks the table and causes deadlocks. I can't use a simple cursor because the brain-dead JDBC driver tries to load the entire result set into memory (causing out of memory errors), so I want to dynamically construct a cursor that limits the result set to 10,000 records at a time, by manipulating the primary key column.

Is it possible to FETCH a record at a time from a Dynamic (Prepared) cursor ? I know it can be done in Java, C, C++ , PHP, etc, but I would like to do it using Stored Procedure Language. I've googled around a bit an cannot find any examples. I'm running MySQL 5.1 with InnoDB 1.0.9.

Options: ReplyQuote

Written By
How to process very large result set from a dynamic cursor
September 24, 2010 04:27PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.