12C
Fetch limited number of rows
User Rating: 
/ 0
- Details
-
Parent Category: Articles
-
Created on Wednesday, 27 November 2013 21:22
-
Last Updated on Wednesday, 27 November 2013 21:22
-
Published on Wednesday, 27 November 2013 21:22
-
Written by Guy Lambregts
-
Hits: 3990
Fetch limited number of rows ( 12C New Feature )
SQL> show release
release 1201000100
SQL> create table T (c number) tablespace users;
Table created.SQL> begin
2 for i in 1 .. 1000 loop
3 insert into t values (dbms_random.value(1,10000));
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> select * from (select c from t order by c desc ) where rownum < 6; -- prior to Oracle 12C
C
----------
9979.11467
9978.94882
9965.11985
9943.11883
9942.26384
SQL> select c from t order by c desc fetch first 5 rows only; -- Oracle 12C
C
----------
9979.11467
9978.94882
9965.11985
9943.11883
9942.26384
SQL> select * from (select c from t order by c desc ) where rownum < 11; -- prior to Oracle 12C
C
----------
9979.11467
9978.94882
9965.11985
9943.11883
9942.26384
9896.19683
9889.74996
9882.63859
9876.80243
9859.98545
10 rows selected.
SQL> select c from t order by c desc fetch first 10 rows only; -- Oracle 12C
C
----------
9979.11467
9978.94882
9965.11985
9943.11883
9942.26384
9896.19683
9889.74996
9882.63859
9876.80243
9859.98545
10 rows selected.
SQL> select c from t order by c desc offset 5 rows fetch next 5 rows only;-- rownum 6 to 10
C
----------
9896.19683
9889.74996
9882.63859
9876.80243
9859.98545
SQL> select c from t order by c desc offset 2 rows fetch next 5 rows only; -- rownum 3 to 7
C
----------
9965.11985
9943.11883
9942.26384
9896.19683
9889.74996
