Mailing-List: contact dev-help@openjpa.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@openjpa.apache.org
Date: Wed, 31 Jul 2013 07:49:48 +0000 (UTC)
From: "Ognjen Blagojevic (JIRA)" <jira@apache.org>
To: dev@openjpa.apache.org
Message-ID: <JIRA.12660398.1375110822437.142786.1375256988503@arcas>
In-Reply-To: <JIRA.12660398.1375110822437@arcas>
References: <JIRA.12660398.1375110822437@arcas>
Subject: [jira] [Commented] (OPENJPA-2416) setFirstResult/setMaxResults
 (Paging) with Oracle
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


    [ 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