FIRST and NEXT means the same ....

In the past , I have had 2 posts (  http://mfzahirdba.blogspot.com/2013/08/fetch-x-rows-in-oracle-12c-new-feature.html and http://mfzahirdba.blogspot.com/2013/07/oracle-12c-installed-new-fetch-feature.html)  on Pagination feature in oracle 12c ( Fetch ... ) . 

Semantically , FIRST  and NEXT means the same in this SQL construct . Sounds bit odd , a'int it ?

Here is an example.

SQL> select empno,ename,hiredate from scott.emp  order by hiredate;

     EMPNO ENAME      HIREDATE
---------- ---------- ---------
      7369 SMITH      17-DEC-80
      7499 ALLEN      20-FEB-81
      7521 WARD       22-FEB-81
      7566 JONES      02-APR-81
      7698 BLAKE      01-MAY-81
      7782 CLARK      09-JUN-81
      7844 TURNER     08-SEP-81
      7654 MARTIN     28-SEP-81
      7839 KING       17-NOV-81
      7900 JAMES      03-DEC-81
      7902 FORD       03-DEC-81
      7934 MILLER     23-JAN-82
      7788 SCOTT      19-APR-87
      7876 ADAMS      23-MAY-87

14 rows selected.

SQL> select empno , ename , hiredate from scott.emp order by hiredate fetch first  5 rows only;

     EMPNO ENAME      HIREDATE
---------- ---------- ---------
      7369 SMITH      17-DEC-80
      7499 ALLEN      20-FEB-81
      7521 WARD       22-FEB-81
      7566 JONES      02-APR-81
      7698 BLAKE      01-MAY-81

SQL> select empno , ename , hiredate from scott.emp order by hiredate fetch next  5 rows only;

     EMPNO ENAME      HIREDATE
---------- ---------- ---------
      7369 SMITH      17-DEC-80
      7499 ALLEN      20-FEB-81
      7521 WARD       22-FEB-81
      7566 JONES      02-APR-81
      7698 BLAKE      01-MAY-81


If you want to display the next n records , you will need to use OFFSET clause . 

SQL> select empno , ename , hiredate from scott.emp
  2  order by hiredate
  3  offset 5 rows
  4  fetch first  5 rows only  ;

     EMPNO ENAME      HIREDATE
---------- ---------- ---------
      7782 CLARK      09-JUN-81
      7844 TURNER     08-SEP-81
      7654 MARTIN     28-SEP-81
      7839 KING       17-NOV-81

      7900 JAMES      03-DEC-81

SQL> select empno , ename , hiredate from scott.emp
  2  order by hiredate
  3  offset 5 rows
  4  fetch next  5 rows only  ;

     EMPNO ENAME      HIREDATE
---------- ---------- ---------
      7782 CLARK      09-JUN-81
      7844 TURNER     08-SEP-81
      7654 MARTIN     28-SEP-81
      7839 KING       17-NOV-81

      7900 JAMES      03-DEC-81


Update:

The same semantic issue is with SQL Server as well. 
Examples below . 

The only ( major) difference is that FETCH clause has to be used in conjunction with OFFSET clause , as can be seen from the last example below. 

1> Select table_name  from information_Schema.tables
2> order by table_type ;
3> GO

table_name
----------
spt_fallback_db
spt_fallback_dev
spt_fallback_usg
SpServerDiagnosticsResult
MSreplication_options
spt_monitor
spt_values

(7 rows affected)
1> Select table_name from information_Schema.tables
2> order by table_type
3> offset 2 rows
4> fetch first  3 rows only;
5> GO
table_name
----------
spt_fallback_usg
spt_fallback_dev
spt_fallback_db

(3 rows affected)
1> Select table_name from information_Schema.tables
2> order by table_type
3> offset 2 rows
4> fetch next   3 rows only;
5> GO
table_name
----------
spt_fallback_usg
spt_fallback_dev
spt_fallback_db

(3 rows affected)
1>
2>
3>
4>
5> Select table_name from information_Schema.tables
6> order by table_type
7> fetch first  3 rows only;
8> GO
Msg 153, Level 15, State 2, Server DDCVSQL02, Line 7
Invalid usage of the option first in the FETCH statement.
 

Comments Welcome.

FETCH X ROWS in Oracle 12c ( New Feature !)

Oracle 12c has introduced "FETCH" n rows clause in the SELECT statement. Though , it is a "new" feature , Oracle seems to be doing the same good old techniques what the developers were doing for the last decade or so using Analytical functions.

Let us look at the example below.

The highlighted one in the following select statement is one of 12c feature. As it can be seen from the predicate information in the execution plan , Oracle is using ROW_NUMBER function. If you look at the execution plans' predicate information and results , it is pretty much the same.

Have to admit , the new feature shortens the SELECT statement and avoids the subquery .


SQL> select * from scott.emp order by empno fetch first 4 rows only;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20


Execution Plan
----------------------------------------------------------
Plan hash value: 2801941731

----------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |    14 |  1582 |     2   (0)| 00:00:01 |
|*  1 |  VIEW                         |        |    14 |  1582 |     2   (0)| 00:00:01 |
|*  2 |   WINDOW NOSORT STOPKEY       |        |    14 |   532 |     2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| EMP    |    14 |   532 |     2   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN           | PK_EMP |    14 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=4)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "EMP"."EMPNO")<=4)
 

Here is the equivalent SQL from pre-12c .

SQL> Select * from ( Select  e.* , row_number() over ( partition by 1 order by empno) rn from scott.emp e ) where rn <= 4 ;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO         RN
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
      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


Execution Plan
----------------------------------------------------------
Plan hash value: 2801941731

----------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |    14 |  1400 |     2   (0)| 00:00:01 |
|*  1 |  VIEW                         |        |    14 |  1400 |     2   (0)| 00:00:01 |
|*  2 |   WINDOW NOSORT STOPKEY       |        |    14 |   532 |     2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| EMP    |    14 |   532 |     2   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN           | PK_EMP |    14 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   1 - filter("RN"<=4)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "EMPNO")<=4)


Comments Welcome.