Limit return record from SQL query in Oracle

Question

One of the applications that I check the Oracle DB table every x seconds to see if there is new data to process (another application fills it in real time).

Our new client business process forces our real network to populate this table with many records at a time (say 10,000), but only several times a day. The next time my application checks to see if there is something to process, it encounters 10,000 entries and tries to process it.

It is not very well designed, and it just does not scale well. A quick fix will be limited by the number of records that the application receives from Oracle, next time it will select another 50 (or something else), etc.

How can I limit the number of returned records in Oracle SQL? Questions for the order!

  select * 
    from cool_table where date >= to_date('200901010000', 'YYYYMMDDhh24mi') 
order by seq_nr, entry_dts;

Answer 1

select * from
(select c.* from cool_table c
   where date >= to_date('200901010000', 'YYYYMMDDhh24mi') 
   order by seq_nr, entry_dts)
where rownum < 50

You need to ensure that the order is executed before rownum is filtered (otherwise it will take the first 50 rows that it will find and then order)

Answer 2

Starting with Oracle 12c, it finally supports the standard ANSI option fetch first n rows:

select * 
from cool_table where date >= to_date('200901010000', 'YYYYMMDDhh24mi') 
order by seq_nr, entry_dts
fetch first 50 rows only;

This can be combined with a swap offset:

select * 
from cool_table where date >= to_date('200901010000', 'YYYYMMDDhh24mi') 
order by seq_nr, entry_dts
offset 50 rows 
fetch first 50 rows only;