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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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: