I'm working with an oracle 11 database and need to process 53Million entries. This can take some time and my usual approach is to read the data into a Vector of records and then process them but this time I unfortunately run out of memory before all records are read.
you are right
Unless I'm grossly mistaken, everything that returns rows in Oracle is a cursor.
Opening an SQL query with prepared statement creates a cursor in Oracle database that is no different from, say, a ref cursor returned from a procedure.
As I understand it, the issue was not with the query but with the processing. Even if the data is used directly (not stored in a vector), the processing speed for a cursor in the DB procedure will be far more superior to a java program executing elsewhere. I am saying this out of personal experience, as I had to remove my java code and use procedures for processing of large volumes of records(of course I used java to call that procedure ). Db stored procedures will almost always have a better performance when compared to implementing the same logic java through jdbc. You are esentially eliminating a large number of IO calls and initiating just 1 IO call to start processing entirely in the DB.
So if there are problems with cursors returned from SQL query described in the initial post, I assume the same problems would appear with any other method that creates the same or similar cursor.