Hi,
I have performance problem using hibernate's paging strategy(setFirstResult() and setMaxResults()) with Oracle DB. The first 10 records is fast, but when I want to view the records with rownum between 2000 and 2010 is very very slowly.
I looked at the Sql generated, and this script is coming slowly when the rownum is “big”:

select * from
( select row_.*, rownum rownum_ from
(
select FIELD1, FIELD1 … from MY_TABLE
)
row_ where rownum <= 2230)
where rownum_ > 2220

Thanks

try ScrollableResultSet (see documentation) - setFirstResult and setMaxResult is hack for oracle
(mysql and postgreslq have limit clause - it isn't sql standard) and it work bad for oracle

- with ScrollableResultset you make one query for all rows and with setFirstResult and setMaxResult you make query for every page (and it is slow query, special with order by)
It doesn't work with oracle < 8.1 and order by

hibernate developers favorite setFirstResult/setMaxResult - I don't know why, but I think because mysql and postgresl haven't scrollable cursor (last postgresql have this feature)
- this databases load all query rows in memory and have limit for big query list

regards