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

[Seth Wilcox (JIRA)]

     [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-3325?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Seth Wilcox updated HHH-3325:
-----------------------------

    Attachment: OracleLimit.patch

Patch of file Oracle9iDialect.java against Hibernate 3.2.7
Changed Limit generation to always have a lower limit, even if it is 0 to allow oracle to perform optimizations if link above.
Added in the first N rows hint as suggested here http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html
Moved the manipulation of the SQL for isForUpdate until after the StringBuffer to necessitate less GC.

> 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
>
>
> 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