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?