"limit 10" vs "rownum <=10"

[Mark Harrison]

So, we've got some inter-database portability issues we're trying
to address.

sqlite uses: select * from foo limit 1;
oracle uses: select * from foo where rownum <= 1;

What's the conventional wisdom on how to handle this?
Is there something in oracle we can turn on to help?

Right now we're thinking of some logic like:
if oracle, string substitute "limit x" with "where rownum <= x"
and try to handle the cases when there is already a where clause, etc.

Many TIA,
Mark

--
Mark Harrison
Pixar Animation Studios

[Laurenz Albe]

Mark Harrison <m...@pixar.com> wrote:
> So, we've got some inter-database portability issues we're trying
> to address.
>
> sqlite uses: select * from foo limit 1;
> oracle uses: select * from foo where rownum <= 1;
>
> What's the conventional wisdom on how to handle this?
> Is there something in oracle we can turn on to help?
>
> Right now we're thinking of some logic like:
> if oracle, string substitute "limit x" with "where rownum <= x"
> and try to handle the cases when there is already a where clause, etc.

What does 'limit 1' do in sqlite?

If it is similar to PostgreSQL, there is a big difference.
You wouldn't see it in your example though, but then randomly
selecting any row from a table is not a frequent request.

In Oracle, if you

SELECT col1, col2 FROM tab WHERE ROWNUM <= 1 ORDER BY col1;

you will NOT get the row with the smallest col1.

Yours,
Laurenz Albe

[Ed Prochak]

LIMIT is not in tthe SQL standard, so it is a vendor specific solution.

ROWNUM is a pseudoColumn in ORACLE, and so does not violate the
standard (at least not directly)

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.

HTH,
ed

[Gints Plivna]

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/

[Ed Prochak]

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.

[DA Morgan]

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
damo...@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

[Gints Plivna]

Ed Prochak wrote:
> Gints Plivna wrote:

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

Oracle cannot bypass scanning all the source. But it can bypass sortong
all the result set. E.g. even if you have to do that on paper there is
big difference if you have 1000 rows and need to sort all of them or
just get 5 top rows. For 5 top rows you'll sort only first 5 rows, then
scan all the source and immediately throw away anything that is greater
than the last 5th value. If you need sort all the rows it will take
much more time. This is just the case here.
As usual all the credit to Tom Kyte, for detailed explanation look at
his book Effective Oracle by Design page 501, Top-N Query Processing
with ROWNUM.
One more note - according to this book it is valid starting from 9i.

Gints Plivna
http://www.gplivna.eu/