Hi,

I am using Hibernate 2.1 and Oralce 9. I am using a query that may return a very large result set. I dont want to use list() because this would eat up my memory. I also dont want to use iterate(), beacuse this would do a select for every single object that i use and I am actually reading all of them. So I tried to use ScrollableResults. Here is my code:

...
Query q = hbnSession.createQuery("SELECT flight FROM PreSkedFlight flight JOIN flight.opLegs as leg WHERE leg.departureAirport = :airport OR leg.arrivalAirport = :airport ");
q.setString("airport", airport);
result = q.scroll();
...
if (result.first()) {
do {
curFlight = (PreSkedFlight)result.get(0);
...
} while (result.next());
}
...

I would suppose, that this does what i intended. But looking at my DB logs it seems like the query for the ScrollableResults only gets the object keys. Then for every result.get() a new select is called to fetch the object.

Is there a way to avoid this problem or how to deal with large result sets?
Thanks,
Conrad

you are supposed to use setMaxResults() for this.

In this case I have to give up Oracles statement level read consistency feature. Using setMaxResults() and setFirstResult() would do a new select and I might have dirty-reads ... I guess that I have to live with that anyway.

If you are in one transaction, you can set the appropriate isolation level to have repeatable read even with multiple selects.

Yes, I know. But I think oracle might handle concurrency better if its just one select. I was reflecting if there is a way of doing that, but thank you for your feedback.