-1

I have a set of 4000 records,I am only allowed to retrieve 300 records.

I am doing pagination on the resultset, but since we are limiting the result to 300 i am getting different results for each DB hit.

So is there any option to get the ordered first 300 of the 4000 records, without getting the entire result of 4000 records.

Below is the query:

select id from table where name='ronaldo' and rownum <= 300 order by id asc;

ashwinsakthi
  • 1,687
  • 3
  • 22
  • 55

1 Answers1

1

The problem is the query is processed in the following order:

  1. The FROM/WHERE clause goes first.
  2. ROWNUM is assigned and incremented to each output row from the FROM/WHERE clause.
  3. SELECT is applied.
  4. GROUP BY is applied.
  5. HAVING is applied.
  6. ORDER BY is applied.

For that reason, your query as written will return a random 300 records.

You'll need to do a nested query, something like this:

select * from
  (select * from table
   where name='ronaldo' order by id asc) 
where ROWNUM <= 300;

Also, if you're using Oracle 12c or higher, you can use the completely non-standard FETCH FIRST syntax:

SELECT *
  FROM table
  WHERE name='ronaldo'
  ORDER BY id asc
  FETCH FIRST 300 ROWS ONLY;

Why they don't support the LIMIT keyword? Because they're Oracle.

Mike Christensen
  • 77,193
  • 46
  • 189
  • 300
  • I am not allowed to do this as it will fetch all the records leading to my request getting timed out: select * from table where name='ronaldo' order by id asc – ashwinsakthi Jun 19 '14 at 17:02
  • I need to get the first 300 records ordered by id without losing any data ordering. – ashwinsakthi Jun 19 '14 at 17:03
  • No. it won't get all the record @ashwinsakthi; Oracle will optimise it for you. – Ben Jun 19 '14 at 17:03
  • @ashwinsakthi - You can't even do that within a nested select? – Mike Christensen Jun 19 '14 at 17:05
  • Thanks for the response people! But my understanding is as below: select * from (select * from table where name='ronaldo' order by id asc) where ROWNUM <= 300; Will work like: select * from (4000 records) where ROWNUM <= 300; So after getting all the 4000 records and then restricting to 300 will slow down the response and time out will occur. – ashwinsakthi Jun 19 '14 at 17:08
  • Have you actually tried it @ashwinsakthi? Did you have a problem? What was the problem? Did it time out? What are your DB timeout settings then? – Ben Jun 19 '14 at 17:09
  • @ashwinsakthi - Naw, Oracle is smart enough to not do that. According to [this article](http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html), my solution is the recommended way to do exactly what you want. – Mike Christensen Jun 19 '14 at 17:10
  • @ashwinsakthi - BTW, it has to go through all 4,000 rows *anyway* (though this might just be an index scan if `id` is indexed) to find the top 300, right? So, there's no way *not* to look at the entire table every time. – Mike Christensen Jun 19 '14 at 17:11
  • I have tried this approach and this takes around 30 seconds for 4000 records. – ashwinsakthi Jun 19 '14 at 17:13
  • @ashwinsakthi - Can you show an execution plan for this 30 second query? – Mike Christensen Jun 19 '14 at 17:13
  • @Mike - Cost is 170772 – ashwinsakthi Jun 19 '14 at 17:18
  • Cost doesn't mean anything by itself. Please post the entire query plan. – Mike Christensen Jun 19 '14 at 17:23
  • FETCH FIRST is part of the 2008 SQL standard. It is not proprietary. http://en.wikipedia.org/wiki/Select_(SQL)#FETCH_FIRST_clause – Brett Okken Jun 21 '14 at 13:46