OraFAQ Forum: SQL & PL/SQL » diff rowid & rownum

Home » SQL & PL/SQL » SQL & PL/SQL » diff rowid & rownum

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

diff rowid & rownum [message #191130]

Mon, 04 September 2006 23:43

charankarthikeyan
Messages: 16
Registered: August 2006

Junior Member

Hi,

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

karthik

Report message to a moderator

Re: diff rowid & rownum [message #191131 is a reply to message #191130]

Tue, 05 September 2006 00:00

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.

Report message to a moderator

Re: diff rowid & rownum [message #191168 is a reply to message #191131]

Tue, 05 September 2006 01:48

Maaher
Messages: 7065
Registered: December 2001

Senior Member

ROWNUM is assigned before an order by is applied. Did you search the board?

MHE

Report message to a moderator

Re: diff rowid & rownum [message #191177 is a reply to message #191130]

Tue, 05 September 2006 02:06

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

Report message to a moderator

Re: diff rowid & rownum [message #191210 is a reply to message #191177]

Tue, 05 September 2006 03:45

Maaher
Messages: 7065
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

Report message to a moderator

Re: diff rowid & rownum [message #191298 is a reply to message #191210]

Tue, 05 September 2006 10:23

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

Report message to a moderator

Re: diff rowid & rownum [message #191315 is a reply to message #191298]

Tue, 05 September 2006 11:51

Littlefoot
Messages: 21823
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.

Report message to a moderator

Re: diff rowid & rownum [message #191317 is a reply to message #191130]

Tue, 05 September 2006 12:07

Yasir Hashmi
Messages: 304
Registered: April 2006

Senior Member

>>What takes more time?
But then the forum is all about discussing a common topic.

Report message to a moderator

Re: diff rowid & rownum [message #191331 is a reply to message #191317]

Tue, 05 September 2006 15:02

Littlefoot
Messages: 21823
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.

Report message to a moderator

Re: diff rowid & rownum [message #191372 is a reply to message #191298]

Wed, 06 September 2006 01:17

Maaher
Messages: 7065
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

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

Report message to a moderator

Re: diff rowid & rownum [message #191540 is a reply to message #191415]

Thu, 07 September 2006 01:27

Maaher
Messages: 7065
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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Dec 06 14:57:10 CST 2024