Thursday, June 27, 2013

Oracle 12c New Features - FETCH FIRST ROWS, OFFSET

Oracle 12c introduces sql syntax for row limiting.  This makes it easier to retrieve records in sets for display or processing.

Example :-

create table employee (id integer primary key, name varchar2(10));
insert into employee values (1,'Adam');
insert into employee values (2,'Ben');
insert into employee values (3,'Colin');
insert into employee values (4,'Dean');
insert into employee values (5,'Evan');
insert into employee values (6,'Frank');
insert into employee values (7,'Greg');
insert into employee values (8,'Hank');
insert into employee values (9,'Ian');
insert into employee values (10,'Jack');
commit;

SQL> select * from employee order by id fetch first 3 rows only;

        ID NAME
---------- ----------
         1 Adam
         2 Ben
         3 Colin

SQL> select * from employee order by id offset 3 rows fetch next 3 rows only;

        ID NAME
---------- ----------
         4 Dean
         5 Evan
         6 Frank

SQL> select * from employee order by id fetch first 50 percent rows only;

        ID NAME
---------- ----------
         1 Adam
         2 Ben
         3 Colin
         4 Dean
         5 Evan