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.
