Oracle Illustrated

Jun 25 2010   9:13AM GMT

Analytical way – To find Nth maximum value from a table



Posted by: Lakshmi Venkatesh
Tags:

To find NTH maximum value from a table, we may need to query the same table twice to get the desired output. This will lead to table scans / index scans for the same table twice. The same can be achieved via analytical functions by querying the table once. Have enclosed the execution plan and have highlighted the difference.

Non analytical way

SQL> SELECT DISTINCT SAL FROM SCOTT.EMP A WHERE &N=(SELECT COUNT (DISTINCT B.SAL
)
2 FROM SCOTT.EMP B WHERE A.SAL<=B.SAL);
Enter value for n: 5
old 1: SELECT DISTINCT SAL FROM SCOTT.EMP A WHERE &N=(SELECT COUNT (DISTINCT B
.SAL)
new 1: SELECT DISTINCT SAL FROM SCOTT.EMP A WHERE 5=(SELECT COUNT (DISTINCT B.
SAL)

SAL
———-
2450

Execution Plan
———————————————————-
Plan hash value: 3556451907

—————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 4 | 25 (4)| 00:00:01 |
| 1 | HASH UNIQUE | | 1 | 4 | 25 (4)| 00:00:01 |
|* 2 | FILTER | | | | | |
| 3 | TABLE ACCESS FULL | EMP | 14 | 56 | 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 “B”.”SAL”) FROM “SCOTT”.”EMP” “B” WHERE “B”.”SAL”>=:B1)=5)
5 – filter(“B”.”SAL”>=:B1)

Statistics
———————————————————-
187 recursive calls
0 db block gets
127 consistent gets
4 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
17 sorts (memory)
0 sorts (disk)
1 rows processed

USING Analytical functions -

SELECT * FROM
(SELECT e.*, RANK() OVER ( ORDER BY e.SAL DESC) rnk FROM scott.emp e) qry
WHERE qry.rnk = 6;

EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO RNK
———- ———-
7782 CLARK MANAGER 7839 09-JUN-81 2450
10 6

Execution Plan
———————————————————-
Plan hash value: 3291446077

——————————————————————————–
| 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 | TABLE ACCESS FULL | EMP | 14 | 518 | 3 (0)| 00:00:01|
——————————————————————————–

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

1 – filter(“QRY”.”RNK”=6)
2 – filter(RANK() OVER ( ORDER BY INTERNAL_FUNCTION(“E”.”SAL”) DESC)
SQL> SELECT * FROM
2 (SELECT e.*, RANK() OVER ( ORDER BY e.SAL DESC) rnk FROM scott.emp e) qry
3 WHERE qry.rnk = 6;

EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO RNK
———- ———-
7782 CLARK MANAGER 7839 09-JUN-81 2450
10 6

Execution Plan
———————————————————-
Plan hash value: 3291446077

——————————————————————————–

-

| 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 | TABLE ACCESS FULL | EMP | 14 | 518 | 3 (0)| 00:00:01|
——————————————————————————–

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

1 – filter(“QRY”.”RNK”=6)
2 – filter(RANK() OVER ( ORDER BY INTERNAL_FUNCTION(“E”.”SAL”) DESC
)<=6)

Statistics
———————————————————-
144 recursive calls
0 db block gets
28 consistent gets
0 physical reads
0 redo size
888 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: