Home » SQL & PL/SQL » SQL & PL/SQL » diff rowid & rownum
diff rowid & rownum [message #191130] Mon, 04 September 2006 23:43 Go to next message
charankarthikeyan
Messages: 16
Registered: August 2006
Junior Member
Hi,

can anyone tell what's the diff between rowid & rownum
assistance pls.

karthik
Re: diff rowid & rownum [message #191131 is a reply to message #191130] Tue, 05 September 2006 00:00 Go to previous messageGo to next message
rameshuddaraju
Messages: 69
Registered: June 2005
Location: India
Member

Rowid is physical address of a row in the database where as rownum is a pseudo column which returns row number such as 1,2,3... for the rows returned by a select statement.
Re: diff rowid & rownum [message #191168 is a reply to message #191131] Tue, 05 September 2006 01:48 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
ROWNUM is assigned before an order by is applied. Did you search the board?

MHE
Re: diff rowid & rownum [message #191177 is a reply to message #191130] Tue, 05 September 2006 02:06 Go to previous messageGo to next message
rana.irfan@arzootex.com
Messages: 15
Registered: June 2006
Location: Faisalabad.
Junior Member

Hi,

ROWID is always unique in Select Statement.

SQLWKS> Select Rowid,rownum,Lot_No FROM FABRIC_ISSUE
2> WHERE LOT_NO='44GI';

ROWID ROWNUM LOT_NO
------------------ ---------- ---------------
AAAGFnAAIAAAGXnAAI 1 44GI
AAAGFnAAIAAAGXnAAJ 2 44GI
AAAGFnAAIAAAGXnAAK 3 44GI

ROWNUM is always diffrent in a Select Statement.

SQLWKS> Select Rowid,rownum,Lot_No FROM FABRIC_ISSUE
2> WHERE LOT_NO='44GI'
3> and rowid='AAAGFnAAIAAAGXnAAK' ;

ROWID ROWNUM LOT_NO
------------------ ---------- ---------------
AAAGFnAAIAAAGXnAAK 1 44GI


Regards,


Rana Irfan

Re: diff rowid & rownum [message #191210 is a reply to message #191177] Tue, 05 September 2006 03:45 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
ROWID is, like stated before, a reference to the physical location of the data. As it is unique in a database, it is unique in a select as well.

ROWNUM on the other hand is unique in a single select statement. It refers to the order in which records are retrieve before any order by is applied. Will it be different for every select? No. Will it be the same for every select? No.

MHE
Re: diff rowid & rownum [message #191298 is a reply to message #191210] Tue, 05 September 2006 10:23 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
Maheer,
are you sure that ROWNUM is applied before order by clause.
But, i believe that it would be applied after order by cluase(please correct me if i'm wrong).

please check the following snipset.

SQL> SELECT ROWNUM,A,B FROM TAB1;

    ROWNUM          A B
---------- ---------- ----------
         1          1 A1
         2          1 B1
         3          1 C1
         4          2 A2
         5          2 B2

SQL> SELECT ROWNUM,A,B FROM TAB1 ORDER BY 2 DESC;

    ROWNUM          A B
---------- ---------- ----------
         1          2 B2
         2          2 A2
         3          1 C1
         4          1 B1
         5          1 A1

Thanks,
Thangam
Re: diff rowid & rownum [message #191315 is a reply to message #191298] Tue, 05 September 2006 11:51 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What takes more time? Posting a question on the forum, or finding the answer in the Oracle Documentation? ROWNUM and ROWID.
Re: diff rowid & rownum [message #191317 is a reply to message #191130] Tue, 05 September 2006 12:07 Go to previous messageGo to next message
Yasir Hashmi
Messages: 304
Registered: April 2006
Senior Member
>>What takes more time?
But then the forum is all about discussing a common topic.
Re: diff rowid & rownum [message #191331 is a reply to message #191317] Tue, 05 September 2006 15:02 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
In my opinion, OP's original question doesn't look like a request for a discussion, but more like I'm to lazy to search for those terms; pour some knowledge directly into my head.
Re: diff rowid & rownum [message #191372 is a reply to message #191298] Wed, 06 September 2006 01:17 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
gold_oracl wrote on Tue, 05 September 2006 17:23

Maheer,
are you sure that ROWNUM is applied before order by clause.
But, i believe that it would be applied after order by cluase(please correct me if i'm wrong).

SQL> SELECT ROWNUM
  2       , region_id
  3       , region_name
  4  FROM   regions
  5  ORDER  BY region_name
  6  /

    ROWNUM  REGION_ID REGION_NAME
---------- ---------- -------------------------
         2          2 Americas
         3          3 Asia
         1          1 Europe
         4          4 Middle East and Africa

SQL> ed
Wrote file afiedt.buf

  1  SELECT ROWNUM
  2       , region_id
  3       , region_name
  4  FROM   regions
  5* ORDER  BY 3
SQL> /

    ROWNUM  REGION_ID REGION_NAME
---------- ---------- -------------------------
         2          2 Americas
         3          3 Asia
         1          1 Europe
         4          4 Middle East and Africa


Seems like I was right Wink

The reason why you witnessed the behaviour that a select with an ORDER BY descending still produces the row numbers in ascending order can be explained if you have an index on the A column. That would cause Oracle to retrieve the rows in the order desired by using that index. No explicit sort is necessary in that case. You can verify this by doing an explain plan for the select. The row numbers will still be retrieved before an explicit sort takes place. Oracle just takes a shortcut.

MHE

[Updated on: Wed, 06 September 2006 01:31]

Report message to a moderator

Re: diff rowid & rownum [message #191415 is a reply to message #191130] Wed, 06 September 2006 04:52 Go to previous messageGo to next message
charankarthikeyan
Messages: 16
Registered: August 2006
Junior Member
hi,

thank u very much for detailed information Razz
& for those in single line comments Mad

karthik
Re: diff rowid & rownum [message #191540 is a reply to message #191415] Thu, 07 September 2006 01:27 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
By the way, Littlefoot has a point: if you read the sticky (tips&tricks) you will understand what he means: search the Oracle documentation, search the forum, ... Most questions (certainly generic ones) are already asked and answered before.

link 1
link 2
link 3
link 4

MHE
Previous Topic: I need Ideas
Next Topic: How to reset the culmalative balance
Goto Forum:
  


Current Time: Sat Dec 10 16:52:36 CST 2016

Total time taken to generate the page: 0.23048 seconds