2

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?

2
  • What version of Oracle? Without an order by, the query you posted doesn't do what you appear to want. Apr 25, 2016 at 8:13
  • Oracle 11G. Yes you are right. But I wanted to focus on the full scan only so, I omitted that part. Anyway, I'll edit the question Apr 25, 2016 at 8:17

1 Answer 1

5

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

3
  • So what I understand is, if I need rows between 10k and 15k, then Oracle will at least scan until 15k. Is that correct? Apr 25, 2016 at 8:28
  • BTW, the feature added to 12C is like exactly the same with what Microsoft added to SQL Server 2012 :) Apr 25, 2016 at 8:30
  • 1
    @MikayilAbdullayev - Yes. Though I would question why you would do such a thing. You use paging queries when you have humans that want to look through the first few rows of data. Humans aren't going to read row 10,000. A computer system that wants to read all the data eventually would not use this sort of query Apr 25, 2016 at 8:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.