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

[e. wernli (JIRA)]

Pagination with Oracle ROWNUM is sub-optimal
--------------------------------------------

                 Key: HHH-3325
                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3325
             Project: Hibernate3
          Issue Type: Bug
    Affects Versions: 3.2.5
            Reporter: e. wernli


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