Some of the queries we run have 100'000+ results and it takes forever to load them and then send them to the client. So I'm using ScrollableResults to have a paged results feature. But we're topping at roughly 50k results (never exactly the same amount of results).

I'm on an Oracle9i database, using the Oracle 10 drivers and Hibernate is configured to use the Oracle9 dialect. I tried with the latest JDBC driver (ojdbc6.jar) and the problem was reproduced.

We also followed some advice and added an ordering clause, but the problem was reproduced.

Here is a code snippet that illustrates what we do:

Criteria crit = sess.createCriteria(ABC.class);
crit.add(Restrictions.eq("property", value));
crit.setFetchSize(pageSize);
crit.addOrder(Order.asc("property"));
ScrollableResults sr = crit.scroll();
...
...
do{
  for (Object entry : page)
    sess.evict(entry); //to avoid having our memory just explode out of proportion
  page.clear();
  for (int i =0 ; i < pageSize && ! metLastRow;  i++){
    if (resultSet.next())
      page.add(sr.get(0));
    else 
      metLastRow = true;
  }
  metLastRow = metLastRow?metLastRow:sr.isLast();

  sendToClient(page);
}while(!metLastRow);

So, why is it that I get the result set to tell me its at the end when it should be having so much more results?