To find Nth maximum value from a table

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)<=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

SQL>
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