OraFAQ Forum: SQL & PL/SQL » How Rowids are assigned to rows by Oracle?

Home » SQL & PL/SQL » SQL & PL/SQL » How Rowids are assigned to rows by Oracle? (Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production)

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

How Rowids are assigned to rows by Oracle? [message #596335]

Fri, 20 September 2013 17:52

gupta27
Messages: 31
Registered: August 2013
Location: Delhi

Member

Hi,

Can you please tell me how Rowids are assigned to rows by Oracle?

Is it that Rowids are assigned in an incremental manner - as in the latest record would get Maximum Rowid.(Assuming that no record has ever been deleted from that table)

Report message to a moderator

Re: How Rowids are assigned to rows by Oracle? [message #596336 is a reply to message #596335]

Fri, 20 September 2013 17:55

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

gupta27 wrote on Fri, 20 September 2013 15:52

Hi,

Can you please tell me how Rowids are assigned to rows by Oracle?

Is it that Rowids are assigned in an incremental manner - as in the latest record would get Maximum Rowid.(Assuming that no record has ever been deleted from that table)

post SQL & results that show or return what you call ROWID.
Or post URL to Oracle documentation where ROWID is defined.

Do you mistaken ROWID for ROWNUM (row number)?

 1* select rowid, rownum from dual
SQL> /

ROWID                  ROWNUM
------------------ ----------
AAAAB0AABAAAAOhAAA          1

[Updated on: Fri, 20 September 2013 17:58]

Report message to a moderator

Re: How Rowids are assigned to rows by Oracle? [message #596337 is a reply to message #596336]

Fri, 20 September 2013 18:16

gupta27
Messages: 31
Registered: August 2013
Location: Delhi

Member

SQL> select rowid,ID,Comments from COMMENTS_T a
where ID like '%0813654254%'
order by rowid desc  ;

ROWID ID COMMENTS
------------------ ---------- -------------------------------------------------------------------------------------------
AALvGTAAsAAAUKnAAG 0813654254 JR/0813653606 single
AALvGTAAsAAAUKnAAF 0813654254 JR/0813653606 single
AALvGTAAsAAAUKnAAE 0813654254 JR/0813653606 SINGLE
AALvGTAAsAAAUKnAAD 0813654254 JR
AALvGTAAsAAAUKnAAC 0813654254
AALvGTAAsAAAUKnAAB 0813654254

6 rows selected.

If I select the record which has Max(rowid), would it be the latest record loaded into the database. Does Oracle assign rowid to rows in increasing order - means whenever a new record is inserted in the table it would get the maximum ROWID in that table.(Assuming that no row has ever been deleted from that table)

Please let me know in case you need more information.

Report message to a moderator

Re: How Rowids are assigned to rows by Oracle? [message #596338 is a reply to message #596337]

Fri, 20 September 2013 18:23

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

can result change when table spans more than 1 datafile?

the ONLY sure way to order result set rows is to use ORDER BY clause

Report message to a moderator

Re: How Rowids are assigned to rows by Oracle? [message #596339 is a reply to message #596338]

Fri, 20 September 2013 18:38

gupta27
Messages: 31
Registered: August 2013
Location: Delhi

Member

BlackSwan wrote on Fri, 20 September 2013 23:23

can result change when table spans more than 1 datafile?

I am not sure about this. But it is a very small table and would span only 1 datafile.

But can you also tell me about my first question about how oracle assigns rowid's to rows-ascending order or randomly?

Report message to a moderator

Re: How Rowids are assigned to rows by Oracle? [message #596340 is a reply to message #596339]

Fri, 20 September 2013 18:40

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

> means whenever a new record is inserted in the table it would get the maximum ROWID in that table.
Above will be true (which will be the case most of the time), until it is not true.

similar in concept to below
http://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns007.htm

when all else fails Read The Fine Manual YOURSELF!

http://www.oracle.com/pls/db112/search?remark=quick_search&word=rowid

[Updated on: Fri, 20 September 2013 19:02]

Report message to a moderator

Re: How Rowids are assigned to rows by Oracle? [message #596348 is a reply to message #596337]

Sat, 21 September 2013 03:21

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

The rowid is the physical location of the row. All described in the docs,
http://docs.oracle.com/cd/E16655_01/server.121/e17209/pseudocolumns008.htm#SQLRF00254
So the concept of "maximum" doesn't apply. You cannot have maximum value for a point on a disc. What wouold it be? The track nearest the centre?

Report message to a moderator

Previous Topic:	Please help me to close the IF blocks.
Next Topic:	Model clause

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 05:16:44 CDT 2024