[
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
