I'm currently using hibernate to access the database with millions of records. I find out that I can't use hibernate generated queries (due to performace problems) if the result set is big. The setMaxResult function of hibernate always use rownum to limit returned data. This is IMHO not the best way to do it. Why don't you use row_number analytical function to limit returned data? The performance of this function is no affected so much by volume of database.

Thanks in advance