[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-3325) Pagination with Oracle ROWNUM is sub-optimal

[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-3325?page=c... ] Can Çermikli commented on HHH-3325: ----------------------------------- I don't know why this problem is still open, but I think this problem is already resolved in Hibernate Dialect classes which are Oracle9iDialect, Oracle9Dialect and Oracle10gDialect. It generates SQL's like following: select * from ( select row_.*, rownum rownum_ from ( OUR_QUERY ) row_ where rownum <= MAX_ROWS ) where rownum_ > MIN_ROWS But the way Hibernate's SQL generation for pagination in OracleDialect.java and Oracle8iDialect.java is the problematic one. Actually, it is not problematic, I think it is done intentionally in OracleDialect.java and Oracle8iDialect.java like this, because in Oracle 8i and below order by clause in the inner SQL stamement is not allowed. So, after 8i and Oracle's order by support, Hibernate's Dialect classes seem to change to support the new pagination. So, if you use OracleDialect or Oracle8iDialect, Hibernate will generate sub-optimal native SQL's for pagination (of course if you use Oracle 8i and below, you need to use those ones), and if you use Oracle9iDialect, Oracle9Dialect or Oracle10gDialect Hibernate will generate optimal native SQL's. I hope it will be useful for the people who are searching for an answer for the problem. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.... - For more information on JIRA, see: http://www.atlassian.com/software/jira