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.

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: