Oracle Illustrated

Jun 25 2010   9:12AM GMT

Analytical way – Retrieve the Nth row from a table

Lakshmi Venkatesh Profile: Lakshmi Venkatesh

To retrieve Nth row from a table using the usual way it might require to query the same table thrice (can write it in different way also). But, with Analytical function the same can be made to hit only once. Using this kind of analytical functions is particularly useful when the query needs to be part of a huge query – helps to avoid group by – so the need to query the same table multiple times is reduced.

Non Analytical way

One way to do this – Usual – three table hits.

select sal FROM scott.emp WHERE rowid = (SELECT rowid FROM scott.emp WHERE rownum <= 5 MINUS SELECT rowid FROM scott.emp WHERE rownum < 5);

SAL
———-
1250

Execution Plan
———————————————————-
Plan hash value: 791824717

——————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|
——————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 16 | 5 (40)| 00:00:01 |
| 1 | TABLE ACCESS BY USER ROWID| EMP | 1 | 16 | 1 (0)| 00:00:01 |
| 2 | MINUS | | | | | |
| 3 | SORT UNIQUE | | 5 | 60 | 2 (50)| 00:00:01 |
|* 4 | COUNT STOPKEY | | | | | |
| 5 | INDEX FULL SCAN | PK_EMP | 14 | 168 | 1 (0)| 00:00:01 |
| 6 | SORT UNIQUE | | 4 | 48 | 2 (50)| 00:00:01 |
|* 7 | COUNT STOPKEY | | | | | |
| 8 | INDEX FULL SCAN | PK_EMP | 14 | 168 | 1 (0)| 00:00:01 |
——————————————————————————–

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

4 – filter(ROWNUM<=5)
7 – filter(ROWNUM<5)

Statistics
———————————————————-
1 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
414 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)
1 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 = 5;

EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO RNM
———- ———-
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30 5

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”=5)
2 – filter(ROW_NUMBER() OVER ( ORDER BY ROWNUM)<=5)

Statistics
———————————————————-
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
892 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)
1 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: