Retrieve say 5 to 8 rows from table

Non-Analytical way

SELECT a.* FROM
(select e.*,ROWNUM rnm FROM scott.emp e) a
WHERE rnm BETWEEN 3 AND 8;


SQL> SELECT a.* FROM
2 (select e.*,ROWNUM rnm FROM scott.emp e) a
3 WHERE rnm BETWEEN 3 AND 8;

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO RNM
---------- ----------
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30 3

7566 JONES MANAGER 7839 02-APR-81 2975
20 4

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30 5


EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO RNM
---------- ----------
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30 6

7782 CLARK MANAGER 7839 09-JUN-81 2450
10 7

7788 SCOTT ANALYST 7566 19-APR-87 3000
20 8


6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2077119879

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1400 | 3 (0)| 00:00:01 |
|* 1 | VIEW | | 14 | 1400 | 3 (0)| 00:00:01 |
| 2 | COUNT | | | | | |
| 3 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("RNM"<=8 AND "RNM">=3)


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
1142 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed

SQL>


OR

SELECT * FROM scott.emp WHERE rowid in (SELECT rowid FROM scott.emp WHERE rownum <= 8
MINUS
SELECT rowid FROM scott.emp WHERE rownum < 3);

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20

6 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 986843382
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 49 | 8 (38)| 00:00:01|
|* 1 | HASH JOIN | | 1 | 49 | 8 (38)| 00:00:01|
| 2 | VIEW | VW_NSO_1 | 8 | 96 | 4 (50)| 00:00:01|
| 3 | MINUS | | | | ||
| 4 | SORT UNIQUE | | 8 | 96 | ||
|* 5 | COUNT STOPKEY | | | | ||
| 6 | INDEX FULL SCAN| PK_EMP | 14 | 168 | 1 (0)| 00:00:01|
| 7 | SORT UNIQUE | | 2 | 24 | ||
|* 8 | COUNT STOPKEY | | | | ||
| 9 | INDEX FULL SCAN| PK_EMP | 14 | 168 | 1 (0)| 00:00:01|
| 10 | TABLE ACCESS FULL | EMP | 14 | 518 | 3 (0)| 00:00:01|
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access(ROWID="ROWID")
5 - filter(ROWNUM<=8)
8 - filter(ROWNUM<3)


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
1069 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
6 rows processed


Using Analytical functions


SQL> SELECT a.* FROM
(select e.*,row_number() OVER (order by rownum) rnm FROM scott.emp e) a
WHERE rnm BETWEEN 3 AND 8;

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO RNM
---------- ----------
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30 3
7566 JONES MANAGER 7839 02-APR-81 2975
20 4
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30 5
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30 6
7782 CLARK MANAGER 7839 09-JUN-81 2450
10 7
7788 SCOTT ANALYST 7566 19-APR-87 3000
20 8

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1602237660

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1400 | 4 (25)| 00:00:01|
|* 1 | VIEW | | 14 | 1400 | 4 (25)| 00:00:01|
|* 2 | WINDOW SORT PUSHED RANK| | 14 | 518 | 4 (25)| 00:00:01|
| 3 | COUNT | | | | ||
| 4 | TABLE ACCESS FULL | EMP | 14 | 518 | 3 (0)| 00:00:01|
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("RNM">=3 AND "RNM"<=8)
2 - filter(ROW_NUMBER() OVER ( ORDER BY ROWNUM)<=8)


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
1142 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
6 rows processed