Limiting returned record from SQL query in Oracle

Question

One of apps I take care of in checking a Oracle DB table every x seconds to see if there are new data to process (other real-time app is populating it).

Our new client business process forces our real-time up to populate this table with lots of records in a same time (say 10 000), but just few times a day. Next time my app checks if there is anything to process it encounters 10 000 records and tries to process that.

It's not very well engineered and it just not scale good enough. Quick fix would be limit the number of record the app gets from Oracle, next time it will pick another 50 (or whatever) etc.

How can I limit in Oracle SQL the number of returned records? Order matters!

  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 the ordering is done before the rownum filtering (otherwise it will take the first 50 rows it finds, then order them)

Answer 2

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

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 an offset for paging:

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