| How many rows are processed in this Query ? [message #435494] |
Wed, 16 December 2009 08:00  |
sudhir.sbt
Messages: 22
Registered: February 2009
Location: San Jose
|
Junior Member |
|
|
Hello Gurus,
I have one question here. Below is the query to get the nth salary from the employees table.
SELECT DISTINCT (a.salary)
FROM Employees A
WHERE &n = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal);
Can you please tell for any input to the query, does it processes all the records?
For e.g.
When the query is written like below
SELECT DISTINCT (a.salary)
FROM EMPloyees A
WHERE 3 = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal);
Then do all the records in the tables get processed? OR
The query start processing and when the correlated subquery returns 3 that at that point it will finish processin and not proces other records. Or will it process all the rows and then return 3.
|
|
|
|
|
|
|
|
| Re: How many rows are processed in this Query ? [message #435500 is a reply to message #435494] |
Wed, 16 December 2009 08:07  |
cookiemonster
Messages: 13960
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
First off, when posting code can you please use code tags - see the orafaq forum guide if you're not sure how.
An explain plan would tell you if it's processing all rows or not. However that's not the standard way of doing a top-n query in oracle, this is:
SELECT salary
FROM (SELECT salary
FROM emp
ORDER BY salary DESC)
WHERE rownum = 3;
EDIT: typo
[Updated on: Wed, 16 December 2009 08:08] Report message to a moderator |
|
|
|
| Re: How many rows are processed in this Query ? [message #435507 is a reply to message #435500] |
Wed, 16 December 2009 08:25 |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
SQL> SELECT sal
2 FROM (SELECT sal
3 FROM emp
4 ORDER BY sal DESC)
5 WHERE rownum = 3;
no rows selected
Something is missing in your code. There is no rownum 3 if you do not select rownum 1 and 2.
Maybe you meant:
SQL> select sal
2 from (
3 SELECT sal, rownum rn
4 FROM (SELECT sal
5 FROM emp
6 ORDER BY sal DESC)
7 ) WHERE rn = 3;
SAL
----------
3000
1 row selected.
But this is not the correct answer when you have several rows with same salary (in standard EMP table the third salary is 2950).
regards
Michel
[Updated on: Wed, 16 December 2009 08:26] Report message to a moderator |
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
