Slow query with rownum [message #391039] |
Tue, 10 March 2009 14:49  |
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   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
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 #391043 is a reply to message #391041] |
Tue, 10 March 2009 15:01   |
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 #391058 is a reply to message #391056] |
Tue, 10 March 2009 17:18   |
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  |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
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
|
|
|