Home » SQL & PL/SQL » SQL & PL/SQL » doubt in rownum
doubt in rownum [message #265003] Wed, 05 September 2007 04:11 Go to next message
meeta
Messages: 28
Registered: January 2000
Junior Member
Hi,
I am executing the below query and getting the O/P as below

SQL> SELECT ROWNUM rn,salary FROM emp ORDER BY salary ;

        RN     SALARY
---------- ----------
         3        100
         2        200
         1        300
         4        500
         5        600


Can anyone tell me why ROWNUM RN is coming like above,ROWNUM should come as 1,2,3,4,5
Re: doubt in rownum [message #265007 is a reply to message #265003] Wed, 05 September 2007 04:21 Go to previous messageGo to next message
ShivrajGutte
Messages: 21
Registered: April 2007
Location: PUNE
Junior Member
Hi,
While executing the above query Count operation (For rownum) is executing before the order by operation.
If u do the explain plan for this query ,U will get the idea.

regards
Shirvaj
Re: doubt in rownum [message #265011 is a reply to message #265007] Wed, 05 September 2007 04:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Who are "u" and "U"?

Regards
Michel


Re: doubt in rownum [message #265019 is a reply to message #265011] Wed, 05 September 2007 04:40 Go to previous messageGo to next message
Soumen Kamilya
Messages: 128
Registered: August 2007
Location: Kolkata
Senior Member

See the execution plan below:
Quote:
ID PID Operation
0 SELECT STATEMENT
1 0 SORT ORDER BY
2 1 COUNT
3 2 TABLE ACCESS FULL



If you run the query as:
select rownum rn,salary
from (SELECT salary FROM emp ORDER BY salary);

then the execution plan will be different:
Quote:
ID PID Operation
0 SELECT STATEMENT
1 0 COUNT
2 1 VIEW
3 2 SORT ORDER BY
4 3 TABLE ACCESS FULL

First Order by clause runs then select statement runs.

Cheers
Soumen
Re: doubt in rownum [message #265021 is a reply to message #265007] Wed, 05 September 2007 04:50 Go to previous messageGo to next message
meeta
Messages: 28
Registered: January 2000
Junior Member
Thanks shivraj for your reply, I have attached my explain plain in the file
In my explain pla, I am getting rows as 4 whereas my query shows 5 rows as well as can tell me what does cost mean????

[mod-edit] removed illiterate IM speak. If you won't, I will.

[Updated on: Wed, 05 September 2007 07:51] by Moderator

Report message to a moderator

Re: doubt in rownum [message #265037 is a reply to message #265003] Wed, 05 September 2007 05:24 Go to previous message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
Your desire output may be come from the following query
SELECT rank() over (order by sal) rn, sal FROM emp ORDER BY sal
Previous Topic: Tran
Next Topic: Doubts in Procedure
Goto Forum:
  


Current Time: Tue Dec 06 02:38:26 CST 2016

Total time taken to generate the page: 0.16085 seconds