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.