Description Ralf Hockner 2016-10-19 08:09:26 EDT

Created attachment 264937 [details]
JUnit test class with dynamic entity

With Oracle 12c there comes a new feature for paging of results using "OFFSET m ROWS FETCH NEXT n ROWS ONLY". We would like to have EclipseLink using this feature.
Currently a JPQL query of

SELECT e.lastName FROM Employees e ORDER BY e.lastName

with setFirstResult(100) and setMaxResults(100) results in an inefficient SQL of

SELECT * FROM (SELECT /*+ FIRST_ROWS */ a.*, ROWNUM rnum  FROM (SELECT LAST_NAME AS a1 FROM HR.EMPLOYEES ORDER BY LAST_NAME) a WHERE ROWNUM <= ?) WHERE rnum > ?

but a better SQL for 12c would be

SELECT LAST_NAME FROM HR.EMPLOYEES ORDER BY LAST_NAME OFFSET 100 ROWS FETCH NEXT 100 ROWS ONLY