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

From: DA Morgan <damorgan_at_psoug.org>
Date: Wed, 02 Aug 2006 10:59:15 -0700
Message-ID: <1154541556.284176@bubbleator.drizzle.com>

Ed Prochak wrote:
> 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.

One possibility is that Oracle allows you to write your own operators.

Morgan's Library at www.psoug.org
Scroll down to: "Operators user-defined"

Build an operator in Oracle named LIMIT.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Wed Aug 02 2006 - 12:59:15 CDT