Is it possible to perform skip N rows and fetch M rows without full scan Let's say I've got this query:

SELECT EMP_ID,LAST_NAME FROM EMPLOYEES
   WHERE POSITIONID IN (1,3) ORDER BY POSITIONID,LAST_NAME;


Now if I want to show only the first ten rows between 50 and 60, the only way I can think of is to first run the above query with ROWNUM pseudocolumn and then select from the result of this query. Something like this:

SELECT EMP_ID,LAST_NAME FROM 
 (SELECT  ROWNUM RNUM,EMP_ID,LAST_NAME FROM
  (SELECT EMP_ID,LAST_NAME FROM EMPLOYEES
     WHERE POSITIONID IN (1,3) ORDER BY POSITIONID,LAST_NAME))
  WHERE RNUM BETWEEN 50 AND 60;    


But this way, I'll end up first fetching all the employees whose positions are 1 or 3 and then extract the 10 rows among them. I find this a bit inefficient. Is there any way to achieve this without making full scan first?
The standard approach to get rows N through M is to do something like

SELECT *
  FROM (SELECT a.*, rownum rnum
          FROM (SELECT emp_id, last_name
                  FROM employees
                 WHERE positionID in (1,3)
                 ORDER BY &lt;&lt;something&gt;&gt;) a
         WHERE rownum &lt;= 60) b
 WHERE rnum &gt; 50


Note that you need to include the order by in the query in order for it to make sense.  Oracle may use a table scan (depending on whether or not positionID and/or the column(s) in the order by are indexed) but there will be a stopkey limiter so Oracle knows it can stop scanning the table (or the index) once 60 rows have been read.

In 12.1 and later, you can simplify this a bit

SELECT emp_id, last_nae
  FROM employees
 WHERE positionID in (1,3)
 ORDER BY val
OFFSET 50 ROWS
 FETCH NEXT 10 ROWS ONLY


More examples of using the new row limit clauses in 12.1