12c - SQL Query Row Limits and Offsets

Oracle Database 12c introduced   row_limiting_clause in the Select command. This new clause limits the number of records returned in a Select more effectively than previous releases of the Oracle database.

The new keywords OFFSET  and  FETCH provide the ANSI SQL standard to limit the number of rows returned and to specify a starting line for the return set. 

SQL> create table table_test (code number, name varchar2 (20));
Table created.

table_test

insert into VALUES (1, "Alex");
INSERT INTO table_test values (2, 'John');
insert into table_test values (3, 'Maria');
insert into table_test values (4, 'Pedro');
insert into table_test values (5, 'Paul');
insert into table_test values (6 'Fernando');
INSERT INTO table_test values (7 'Rafael');
INSERT INTO table_test values (8 'Samuel');
insert into table_test values (9, 'Daniel');
INSERT INTO table_test values (10 'Luciano');

commit;

Commit   complete.

SQL> select * from table_test

order by code desc

fetch first 5 rows only;

CODE NAME
---------- --------------------
10 Luciano
9 Daniel
8  Samuel

7  Rafael

6  Fernando

SQL> select * from table_test

order by desc

code 4 offset rows

fetch next 4 rows only;

CODE      NAME

---------- --------------------

6 Fernando

5 Paul

4 Peter

3 Maria

SQL> select * from table_test

order by desc code

fetch first 20 percent rows only;

CODE NAME

---------- --------------------

10 Luciano

9 Daniel