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

[Sébastien Lesaint (JIRA)]

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

Sébastien Lesaint commented on HHH-3325:
----------------------------------------

I stepped upon this issue in one of my projects.
I was startled to see that display of a paged table took about 10 times more time on the first page than on any other page.

Activating hibernate's SQL logs, turns out that Oracle takes much more time to query something like "select * from ( blabla ) where rownum < 15" than query "select * from (select row_.*, rownum rownum_ from ( blabla ) row_ where rownum <= 15) where rownum_ > 0".

Hibernate execute the first query whenever one sets the firstResult property of the Query class to 0 or less. This seems to be an obvious way to optimise the generated SQL string but it turns out to be quite a bad performance choice when executing against Oracle.

An obvious fix would be to NOT simplify the SQL when Oracle dialect is selected. I have no idea how easy it is to actually implement.

In the meantime, as a workaround, we included the paging parameter in our native query and it works like a charm.


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