One of the new features with Oracle database 12c is the new syntax for Top N queries and pagination. Did we really need this? Should you choose for the new syntax over the way we used to do it, with an inline view? I think so, it simply adds syntactic clarity to the query, and in this blogpost I will show the difference between the "old" and the "new".
For the examples I will use my all time favourite demo data: the EMP table.
SQL> select ename 2 ,sal 3 from emp 4 order by sal desc 5 / ENAME SAL ---------- ---------- KING 5000 FORD 3000 SCOTT 3000 JONES 2975 BLAKE 2850 CLARK 2450 ALLEN 1600 TURNER 1500 MILLER 1300 WARD 1250 Widlake 1250 ADAMS 1100 JAMES 950 SMITH 800 14 rows selected.As you can tell from the output above, KING has the highest salary and FORD and SCOTT have the same salary which is the second highest.
If you wanted to write a Top N query before Oracle database 12c, let's say the Top 2 of most earning EMP, you would probably have written something with an inline view.
SQL> select ename 2 ,sal 3 from (select ename 4 ,sal 5 from emp 6 order by sal desc 7 ) 8 where rownum <= 2 9 / ENAME SAL ---------- ---------- KING 5000 SCOTT 3000This query might do the job most of the time, but the results might not be what you were looking for. In this case the requirement is "the Top 2 of most earning EMP", SCOTT and FORD should have both been in the results as they have the same salary.
To resolve this, you would have to rewrite your query using an Analytic Ranking Function in the inline view:
SQL> select ename 2 ,sal 3 from (select ename 4 ,sal 5 ,rank() over (order by sal desc) rn 6 from emp 7 ) 8 where rn <= 2 9 / ENAME SAL ---------- ---------- KING 5000 SCOTT 3000 FORD 3000
Using the new Top N syntax in Oracle database 12c, the query is a lot easier to understand.
SQL> select ename 2 ,sal 3 from emp 4 order by sal desc 5 fetch first 2 rows with ties 6 / ENAME SAL ---------- ---------- KING 5000 SCOTT 3000 FORD 3000On line 4 the results are sorted based on the salary (highest on top) and line 5 instructs to get the first two rows. Because of the addition "with ties" both SCOTT and FORD are shown in the results.
To see what happens under the covers, an explain plan is created for this query.
SQL> explain plan for
2 select ename
3 ,sal
4 from emp
5 order by sal desc
6 fetch first 2 rows with ties
7 /
Explained.
SQL> select *
2 from table (dbms_xplan.display())
3 /
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3291446077
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 644 | 4 (25)| 00:00:01 |
|* 1 | VIEW | | 14 | 644 | 4 (25)| 00:00:01 |
|* 2 | WINDOW SORT PUSHED RANK| | 14 | 154 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL | EMP | 14 | 154 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("from$_subquery$_002"."rowlimit_$$_rank"<=2)
2 - filter(RANK() OVER ( ORDER BY INTERNAL_FUNCTION("SAL") DESC )<=2)
16 rows selected.
The output above shows that the Analytic Function RANK is used.
