06 | 11 | 2024
Latest Articles
Popular Articles

12C

Fetch limited number of rows

User Rating:  / 0
PoorBest 

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