Oracle – How to Perform Skip N Rows and Fetch M Rows Without Full Scan

optimizationoracleoracle-11grow

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?

Best Answer

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 <<something>>) a
         WHERE rownum <= 60) b
 WHERE rnum > 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

Related Question