Its not realy a new feature because many other database vendors have this feature already but it is new to Oracle 12c. You can now limit the Output of rows in a SQL Query.
A small example:
Check the employees Table
SQL> select count(*) from employees; COUNT(*) ---------- 107
Now we are only want to returning 10 Rows beginning with the lowest employee_id.
select employee_id,first_name,last_name
from employees
order by employee_id
FETCH FIRST 10 ROWS ONLY;
EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- ------------- -------------
100 Steven King
101 Neena Kochhar
102 Lex De Haan
103 Alexander Hunold
104 Bruce Ernst
105 David Austin
106 Valli Pataballa
107 Diana Lorentz
108 Nancy Greenberg
109 Daniel Faviet
You can also use a offset for the Row Limit Clause, for example to start with the fifth record and show the next 10 rows.
select employee_id,first_name,last_name from employees order by employee_id OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY; EMPLOYEE_ID FIRST_NAME LAST_NAME ----------- ------------- ------------- 105 David Austin 106 Valli Pataballa 107 Diana Lorentz 108 Nancy Greenberg 109 Daniel Faviet 110 John Chen 111 Ismael Sciarra 112 Jose Manuel Urman 113 Luis Popp 114 Den Raphaely
A other way is to use PERCENT instead of ROWS. For example to show the first 5 Percent of a table.
select employee_id,first_name,last_name from employees order by employee_id FETCH FIRST 5 PERCENT ROWS ONLY; EMPLOYEE_ID FIRST_NAME LAST_NAME ----------- ------------- ------------- 100 Steven King 101 Neena Kochhar 102 Lex De Haan 103 Alexander Hunold 104 Bruce Ernst 105 David Austin
So what happen here is that a view is created during execution and a filter on the view limits the rows to 5 percent of the total rows. You can also observe this with the execution plan
For example a simple SELECT on the emp table to show the first 10 Rows:
select empno,ename from emp FETCH FIRST 10 ROWS ONLY; Plan hash value: 3611411408 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | |* 1 | VIEW | | 14 | 462 | 3 (0)| 00:00:01 | |* 2 | WINDOW NOSORT STOPKEY| | 14 | 140 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL | EMP | 14 | 140 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=10) 2 - filter(ROW_NUMBER() OVER ( ORDER BY NULL )<=10)
As always comments are welcome
