Home » SQL & PL/SQL » SQL & PL/SQL » Slow query with rownum (11g)
Slow query with rownum [message #391039] Tue, 10 March 2009 14:49 Go to next message
psk1
Messages: 30
Registered: August 2008
Location: Seattle, WA
Member
Hi,

I have the following query:

SELECT * FROM (
    SELECT field1, field2
    FROM   table
    WHERE  field1 < :bindvalue)
ORDER BY field1
WHERE rownum=1


It takes about .18 seconds to run. If I change the query to this:

SELECT field1, field2
FROM   table
WHERE  field1 < :bindvalue
and rownum=1
ORDER BY field1


It takes .05 seconds to run.

My question is, are these queries equivalent? In other words, will they return the same results? If so, why does the second incarnation run that much faster?

Re: Slow query with rownum [message #391041 is a reply to message #391039] Tue, 10 March 2009 14:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This first querty is syntaxically wrong.
But if you thought with "order by" before ")", just think what they do.

The first query sorts the whole result set then takes the first row.
The second query takes the first row then sorts this unique row.

Regards
Michel

[Updated on: Tue, 10 March 2009 14:55]

Report message to a moderator

Re: Slow query with rownum [message #391042 is a reply to message #391039] Tue, 10 March 2009 14:54 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
First query is invalid, so I doubt that it returns any result in 18 seconds (or was it 0.18 seconds? Doesn't matter, really).
Re: Slow query with rownum [message #391043 is a reply to message #391041] Tue, 10 March 2009 15:01 Go to previous messageGo to next message
psk1
Messages: 30
Registered: August 2008
Location: Seattle, WA
Member
Right, for the first one I meant the following:

SELECT * FROM (
    SELECT field1, field2
    FROM   table
    WHERE  field1 < :bindvalue
    ORDER BY field1)
WHERE rownum=1


Ideally I'm trying to mimic the following Postgres query:

SELECT field1, field2
FROM   table
WHERE  field1 < :bindvalue
ORDER BY field1
LIMIT 1;


From your replies, I take it the second query will not return the appropriate result since I am looking for the first row of the result after sorting.

That said, is there any way to make the first query run faster?

I already have an index on columns field1 and field2.


Re: Slow query with rownum [message #391047 is a reply to message #391043] Tue, 10 March 2009 15:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I'm afraid not but .18 second is not so much.

Regards
Michel
Re: Slow query with rownum [message #391056 is a reply to message #391039] Tue, 10 March 2009 17:10 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/84315/74940/
which have you tried & what were the results
Re: Slow query with rownum [message #391058 is a reply to message #391056] Tue, 10 March 2009 17:18 Go to previous messageGo to next message
psk1
Messages: 30
Registered: August 2008
Location: Seattle, WA
Member
Thanks for your replies.

BlackSwan, thanks for the links. I haven't tried any of those methods, but I'm reading up on them right now.

I omitted an important detail that ended up being the root of my problem. The query actually looked like this:

SELECT * FROM (
    SELECT field1, field2
    FROM   table
    WHERE  field1 < nvl(:bindvalue,0)
    ORDER BY field1)
WHERE rownum=1


Getting rid of the nvl() function dropped the time to execute from .18 seconds to .005 seconds.

I was under the impression that since the nvl was being performed on the bind variable, it wouldn't have a large effect on the performance on the query. Apparently, this isn't the case.


Re: Slow query with rownum [message #391094 is a reply to message #391058] Wed, 11 March 2009 00:17 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For performances question you have to provide execution plan, statistics and all related information otherwise you get inaccurate answers.

Regards
Michel
Previous Topic: Problems in DDE, TEXT_IO, OLE2
Next Topic: traingle qery
Goto Forum:
  


Current Time: Fri Dec 09 13:17:26 CST 2016

Total time taken to generate the page: 0.09697 seconds