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?

  • What version of Oracle? Without an order by, the query you posted doesn't do what you appear to want. – Justin Cave Apr 25 '16 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 – Mikayil Abdullayev Apr 25 '16 at 8:17
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

  • So what I understand is, if I need rows between 10k and 15k, then Oracle will at least scan until 15k. Is that correct? – Mikayil Abdullayev Apr 25 '16 at 8:28
  • BTW, the feature added to 12C is like exactly the same with what Microsoft added to SQL Server 2012 :) – Mikayil Abdullayev Apr 25 '16 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 – Justin Cave Apr 25 '16 at 8:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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