title: Oracle query results pagination without TABLE FULL SCAN data access method
body: <p>There are plenty of questions on stackoverflow on how to perform pagination correctly, for oracle the most popular answer is something like this:</p>
  
  <pre><code>select * from (
      select row_.*, rownum rownum_ 
      from (select * from some_table) row_ 
      where rownum &lt;= N) 
  where rownum_ &gt; M;
  </code></pre>
  
  <p>I've seen this query everywhere and Hibernate also generates it for pagination. It's probably ok for most cases but it requires full table scan and slows down significantly on huge amounts of data.</p>
  
  <p>There is a hint that's supposed to help selecting first N rows</p>
  
  <pre><code>/*+ FIRST_ROWS(5000) */
  </code></pre>
  
  <p>but it doesn't help in case of selecting second page and seems to use full scan also, at least that's what "explain plan" says to me.</p>
  
  <p>To deal with this problem I'm currently implementing custom pagination - reading ids of all rows in a table and splitting them on ranges so that pagination query can look something like this:</p>
  
  <pre><code>select * from some_table where id between N and M;
  </code></pre>
  
  <p>I was hoping to find a vendor solution to this problem but haven't succeeded so far. </p>
  
  <p><strong>So, the question is - am I reinventing the wheel or there is realy no way to implement pagination on oracle without full scan?</strong></p>
  
  <p>Upd: in Oracle 12c they introduced a new syntax for paging:</p>
  
  <pre><code>OFFSET N ROWS FETCH NEXT M ROWS ONLY;
  </code></pre>
  
  <p>I've tried explain plan on this and it seems to be just an alias for</p>
  
  <pre><code>select * from (
      select row_.*, rownum rownum_ 
      from (select * from some_table) row_ 
      where rownum &lt;= N) 
  where rownum_ &gt; M;
  </code></pre>
  
  <p>UPD2: just found a similar question - <a href="https://stackoverflow.com/questions/13221912/oracle-and-pagination">Oracle and Pagination</a>
  looks like I've been inattentive while searching for duplicates before. So, most probably the answer on my question is negative but still, maybe something has changed since then...</p>