Oracle 12c New Features - TOP N Query

Syntax:

[ OFFSET offset { ROW | ROWS } ]
[ FETCH { FIRST | NEXT } [ { rowcount | percent PERCENT } ]
    { ROW | ROWS } { ONLY | WITH TIES } ]

oracle@s11:~$ sqlplus donghua/donghua

SQL*Plus: Release 12.1.0.1.0 Production on Fri Nov 15 04:07:09 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Last Successful login time: Fri Nov 15 2013 01:28:46 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options


SQL> select first_name,last_name,salary from hr.employees
  2  order by salary desc fetch first 5 rows only;

FIRST_NAME           LAST_NAME                     SALARY
-------------------- ------------------------- ----------
Steven               King                           24000
Neena                Kochhar                        17000
Lex                  De Haan                        17000
John                 Russell                        14000
Karen                Partners                       13500

SQL> select first_name,last_name,salary from hr.employees
  2  order by salary desc fetch first 2 rows with ties;

FIRST_NAME           LAST_NAME                     SALARY
-------------------- ------------------------- ----------
Steven               King                           24000
Neena                Kochhar                        17000
Lex                  De Haan                        17000


SQL> select first_name,last_name,salary from hr.employees
  2  order by salary desc fetch first 2 percent rows only;

FIRST_NAME           LAST_NAME                     SALARY
-------------------- ------------------------- ----------
Steven               King                           24000
Neena                Kochhar                        17000
Lex                  De Haan                        17000

SQL> select first_name,last_name,salary from hr.employees
  2  order by salary desc fetch first 2 percent rows with ties;

FIRST_NAME           LAST_NAME                     SALARY
-------------------- ------------------------- ----------
Steven               King                           24000
Neena                Kochhar                        17000
Lex                  De Haan                        17000


SQL> select first_name,last_name,salary from hr.employees
  2  order by salary desc offset 2 rows fetch next 2 rows only;

FIRST_NAME           LAST_NAME                     SALARY
-------------------- ------------------------- ----------
Lex                  De Haan                        17000
John                 Russell                        14000

What is behind the sense?

SQL> set autotrace  traceonly explain
SQL> select first_name,last_name,salary from hr.employees
  2  order by salary desc offset 2 rows fetch next 2 rows only;

Execution Plan
----------------------------------------------------------
Plan hash value: 2698234872

--------------------------------------------------------------------------------------
| Id  | Operation                | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |           |   107 |  6955 |     3   (0)| 00:00:01 |
|*  1 |  VIEW                    |           |   107 |  6955 |     3   (0)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK|           |   107 |  2033 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL     | EMPLOYEES |   107 |  2033 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=CASE  WHEN
              (2>=0) THEN 2 ELSE 0 END +2 AND "from$_subquery$_002"."rowlimit_$$_rownumber">
              2)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("SALARY") DESC
              )<=CASE  WHEN (2>=0) THEN 2 ELSE 0 END +2)