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

[Can Çermikli (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-3325?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=38772#action_38772 ] 

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.

> Pagination with Oracle ROWNUM is sub-optimal
> --------------------------------------------
>
>                 Key: HHH-3325
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3325
>             Project: Hibernate Core
>          Issue Type: Bug
>    Affects Versions: 3.2.5
>            Reporter: e. wernli
>         Attachments: OracleLimit.patch, OracleLimit.patch
>
>
> Pagination with Oracle ROWNUM is sub-optimal
> The feature works but result in sub-optimal SQL. The generated SQL is the following:
> select * from ( select row_.*, rownum rownum_ from ( select this_.id as id3_0_, this_.version as version3_0_, this_.name as name3_0_, this_.type as type3_0_, this_.marketstatus as marketst5_3_0_ from Customer this_ order by this_.id asc ) row_ ) where rownum_ <= ? and rownum_ > ?
> But this SQL is faster:
> SELECT *
>   FROM (SELECT row_.*, ROWNUM rownum_
>           FROM (SELECT   this_.ID AS id3_0_, this_.VERSION AS version3_0_,
>                          this_.NAME AS name3_0_, this_.TYPE AS type3_0_,
>                          this_.marketstatus AS marketst5_3_0_
>                     FROM customer this_
>                 ORDER BY this_.ID ASC) row_
>          WHERE ROWNUM <= ?)
>  WHERE rownum_ > ?
> The second solution allows Oracle to use an optimization that can dramatically reduce the time of the query, especially one of the first page is retrieved.
> See this link for an explanation of this optimization: http://decipherinfosys.wordpress.com/2007/08/09/paging-and-countstopkey-optimization/

-- 
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.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira