openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Ognjen Blagojevic (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (OPENJPA-2416) setFirstResult/setMaxResults (Paging) with Oracle
Date Wed, 31 Jul 2013 07:49:48 GMT

    [ https://issues.apache.org/jira/browse/OPENJPA-2416?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13724981#comment-13724981
] 

Ognjen Blagojevic commented on OPENJPA-2416:
--------------------------------------------

Andreas,

1. Your 'inner' query does not have a sorting condition. Without sorting condition, results
of the 'inner' query will probably be sorted the same way as the results 'my statement' which
is not unique, so the problem still persists.
2. If your intention was to sort inner query by ROWNUM and then to select results in outer
query, that will work, but will require substantially more resources by RDBMS to execute.
For details please read:

  http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html

"One important thing about using this pagination query is that the ORDER BY statement should
order by something unique. If what you are ordering by is not unique, you should add something
to the end of the ORDER BY to make it so."

-Ognjen
                
> setFirstResult/setMaxResults (Paging) with Oracle
> -------------------------------------------------
>
>                 Key: OPENJPA-2416
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-2416
>             Project: OpenJPA
>          Issue Type: Improvement
>          Components: query
>    Affects Versions: 2.1.1
>            Reporter: Andreas Mader
>
> We are using OpenJPA with Oracle 11g Database. For Paging we use setFirstResult and setMaxResults
to Page through the result set.
> Lets take an example: PageSize 25.
> first Statement: firstResult: 0; maxResults: 26 (1 more to check if it is truncated)
> SQL generated: 
> SELECT * FROM ([my statement]) WHERE ROWNUM <= 26
> second Statement: firstResult: 25; maxResults: 26
> SQL generated: 
> SELECT * FROM (SELECT r.*, ROWNUM RNUM FROM ([my statement]) r WHERE ROWNUM <= 50)
WHERE RNUM > 25
> The way limiting the resultset for paging is sometimes not correct. The last result of
the statement should be equal to the first result of the new page - but it isn't always. If
the sorting is done on a non-unique column in the database, there are different sortings in
the resultset of these two statements. I don't know why it is done this way, I would prefer
the following statement (this works for all values of firstResult and maxResults with non-unique
sort column):
> SELECT outer.* FROM (
>   SELECT ROWNUM rn, inner.* FROM 
>      ([my statement])
>   inner)
> outer WHERE outer.rn > 25 AND outer.rn <= 50

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira

Mime
View raw message