limit/offset vs setFetchSize()

[Johan Oskarsson <jo...@oskarsson.nu>]

Hi.

I'm refactoring a program that indexes database entries in a lucene index.
I decided to move all the jdbc code to using ibatis instead.
However I seem to have run into a problem.

The old code simply iterated through the resultset
and processed everything that way, using a: st.setFetchSize(10000);
to limit the number of rows to be returned.

I first copied the query and ran it using ibatis. But since there's 
roughly 30 million
rows in the resultset the memory quickly runs out.

I switched to using a custom rowhandler that processes the row without
saving anything into memory. This unfortunately didn't help. Now I'm using
the limit offset tactic to process chunks of the data. This requires me 
to add
"order by" to the query, which in this case means the query will take 
forever to run.
The easiest way would probably be to go back to the old code, but I 
prefer ibatis.

What can I do to avoid this problem?
I'm not exactly an sql expert so any advice is welcome.

/Johan