Hi,
I'm wondering how the hibernate paging working.
let's say I have a big result(10000 rows) need to be paged.there are functions like setFirstResult(0), setMaxResults(9) in hibernate,

hibernate will
1. only retrieve 10 rows from database, or
2. retrieve 10000 rows and put to a collection,then filter out first 10 rows from the collection.


if Hibernate use the 2nd way, it's still take time for each page even there is only few rows on each page

please explain.
thank you

Joh

If you take a look at the SQL Hibernate generates you'll see that it uses option 1. Possibility 2 would kill any application that potentially returns more than a few thousand results.

Erik

I use hibernate with Oracle,the genarated sql like:

select * from (
select
id ,
col1,
col2,
from
theTable
) where rownum <= ?

this is reasonable since Oracle has rownum.
what if others database(for ex. MS SQL server) doesn't have the similar function?

Thank you for reply

With me, the Oracle performance is low using hibernate paging.
The Hibernate generate a subquery, when in this case the subquery is in FROm clause and it´s called as in line view. So, the inline view or subquery read all rows from database and, finally takes the paging.
I think this is a problem with oracle and hibernate paging. :(