MySQL Forums :: Stored Procedures :: How to process very large result set from a dynamic cursor


Advanced Search

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


Subject Views Written By Posted
How to process very large result set from a dynamic cursor 4288 Joe Petronio 09/24/2010 04:27PM
Re: How to process very large result set from a dynamic cursor 1764 Rick James 09/25/2010 08:00PM
Re: How to process very large result set from a dynamic cursor 1064 Joe Petronio 09/28/2010 07:24AM
Re: How to process very large result set from a dynamic cursor 1028 Rick James 09/28/2010 05:08PM
Re: How to process very large result set from a dynamic cursor 957 Joe Petronio 09/29/2010 07:22AM


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.