Re: "limit 10" vs "rownum <=10"

Ed Prochak wrote:
> The proper way of handling either case is in the application code that
> fetches the results. The app opens a cursor and fetches until it
> reaches its limit. If you do the application right, then you won't need
> either ROWNUM or LIMIT.

That is very disputable at least from performance viewpoint.

suppose you have
SELECT FROM <a very big table> ORDER BY <some columns> and fetch in application only some first X rows
versus
SELECT * FROM (
SELECT FROM <a very big table> ORDER BY <some columns> ) WHERE rownum <= X

The second result you'll get many times faster than first one, because Oracle knows that you'll need only first X rows and doesn't do all the (waste of) work necessary to prepare for returning all rows (i.e. reading all the rows in memory and doing monstrous sort).

Just to understand how big the difference is I created table big from dba_source and inserted rows again and again to finally get 2979920 rows.
So the following query
select * from big order by owner, name, type, line, text returned first 48 rows in ~397 sec in my plsql developer screen

but the query
select * from (
select * from big order by owner, name, type, line, text )
where rownum <=48
returned the same rows in ~18 secs

Of course usually one doesn't need to sort 3M rows just to display the first 48 ones, but the trend is obvious - if you need to sort many rows and return only few of them then give Oracle as much information as you can to avoid unnecessary work.

Gints Plivna
http://www.gplivna.eu/ Received on Wed Aug 02 2006 - 04:05:51 CDT