Oracle Illustrated

Jun 25 2010   9:11AM GMT

Analytical way – TOP N rows from a table

Lakshmi Venkatesh Profile: Lakshmi Venkatesh

Non Analytical way

SELECT * FROM scott.emp inside WHERE 5 >= (SELECT COUNT (DISTINCT outside.sal) FROM scott.emp outside
WHERE outside.sal >= inside.sal) ORDER BY inside.sal DESC;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——— ———- ———- ——-
7839 KING PRESIDENT 17-NOV-81 5000 10
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10

6 rows selected.

Execution Plan
———————————————————-
Plan hash value: 1721702769

—————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 37 | 25 (4)| 00:00:01 |
| 1 | SORT ORDER BY | | 1 | 37 | 25 (4)| 00:00:01 |
|* 2 | FILTER | | | | | |
| 3 | TABLE ACCESS FULL | EMP | 14 | 518 | 3 (0)| 00:00:01 |
| 4 | SORT GROUP BY | | 1 | 4 | | |
|* 5 | TABLE ACCESS FULL| EMP | 1 | 4 | 3 (0)| 00:00:01 |
—————————————————————————–

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

2 – filter( (SELECT COUNT(DISTINCT “OUTSIDE”.”SAL”) FROM
“SCOTT”.”EMP” “OUTSIDE” WHERE “OUTSIDE”.”SAL”>=:B1)=:B1)

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

SQL>

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

EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO RNM
———- ———-
7369 SMITH CLERK 7902 17-DEC-80 800
20 1
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30 2
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

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”>=1 AND “RNM”<=6)
2 – filter(ROW_NUMBER() OVER ( ORDER BY ROWNUM)<=6)

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

SQL>

 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: