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

Gints Plivna wrote:
> 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.

Well the context was portability, not performance. The OP wanted queries that worked on different DBMS products including Oracle and whatever stuff that uses the LIMIT keyword.

Often what I have seen is someone want to display the first X rows on a report or web page. Then they com back asking how to get rows X+1 thru X+X. So they obviously are using the entire set in that case, and really need to program their application accordingly.

>
> 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).

I really do not see how it can bypass the sort. Both done from a Pro*C (or similar) application?

>
> 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
>

Trying the second version on one of my tables with over 4million rows doesn't seem to support your claim. Using X=10 and issuing the query from sqlplus it's now 20seconds and still running

60 seconds and still running

120 seconds and still running

>
> 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/

Your final comment is correct. the more info the optimizer has the better the results.

  Ed

btw that query took 128seconds on my table. ROWNUM may not be a magic bullet for performance either. Received on Wed Aug 02 2006 - 12:31:39 CDT