Oracle Illustrated

Jun 25 2010   9:12AM GMT

Analytical way – Retrieve say 5 to 8 rows from table

Lakshmi Venkatesh Profile: Lakshmi Venkatesh

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”=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

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: