title: EclipseLink with Oracle: "limit by rownum" does not use index
body: <p>we're facing performance issues with EclipseLink 2.7.7 when accessing Oracle 12.1 tables with paging. Investigation showed that Oracle does not use its indexes with EclipseLink paging.</p>
  <p>I've extracted the sql sent to the database and was able to reproduce the issue using a database tool (DataGrip).</p>
  <p>Example:</p>
  <pre class="lang-sql prettyprint-override"><code>-- #1: without paging
          SELECT col1 AS a1, col2 AS a2, col3 AS a3, ...
          FROM &lt;TABLE&gt;
          WHERE colN &gt; to_timestamp('2021-12-08', 'yyyy-mm-dd'))
          ORDER BY col1 DESC;
  </code></pre>
  <p>Explain plan shows that the index on <code>colN</code> is used. Fine.</p>
  <p>When the same query is executed with paging, the original query is wrapped in two subselects:</p>
  <pre class="lang-sql prettyprint-override"><code>-- #2 with EclipseLink paging
  SELECT * FROM (
      SELECT a.*, ROWNUM rnum  FROM (
          SELECT col1 AS a1, col2 AS a2, col3 AS a3, ...
          FROM &lt;TABLE&gt;
          WHERE colN &gt; to_timestamp('2021-12-08', 'yyyy-mm-dd'))
          ORDER BY col1 DESC    
      ) a WHERE ROWNUM &lt;= 100
  ) WHERE rnum &gt; 0;
  </code></pre>
  <p>For this query, the explain plan shows that the <strong>index on <code>colN</code> is not used</strong>.
  As a result, querying a table with millions of rows takes 50-90 seconds (depending on the hardware).
  Side note: on my test database, this query returns 0 records since colN values are before 2021-12-08.</p>
  <p>Oracle 12c introduced the OFFSET/FETCH syntax:</p>
  <pre class="lang-sql prettyprint-override"><code>-- #3
          SELECT col1 AS a1, col2 AS a2, col3 AS a3, ...
          FROM &lt;TABLE&gt;
          WHERE colN &gt; to_timestamp('2021-12-08', 'yyyy-mm-dd'))
          ORDER BY col1 DESC
          OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY;
  </code></pre>
  <p>Using this syntax, indexes are at least sometimes used as expected. When they are used, execution time is below 1s which is acceptable.
  However, I could not figure out how to convince EclipseLink to use this syntax.</p>
  <p>If <code>ORDER BY col1 DESC</code> is removed from the original paged query (#2), the index is used  the query returns fast enough. However, it will not return the desired records, so that does not help.</p>
  <p>How can I implement performant paged queries using EclipseLink and Oracle 12?
  How can I force oracle to use the index on <code>colN</code> when using paging and <code>order by</code>?</p>