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)
How Rowids are assigned to rows by Oracle? [message #596335] Fri, 20 September 2013 17:52 Go to next message
gupta27
Messages: 28
Registered: August 2013
Location: Delhi
Junior 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)
Re: How Rowids are assigned to rows by Oracle? [message #596336 is a reply to message #596335] Fri, 20 September 2013 17:55 Go to previous messageGo to next message
BlackSwan
Messages: 22525
Registered: January 2009
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 Go to previous messageGo to next message
gupta27
Messages: 28
Registered: August 2013
Location: Delhi
Junior 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.

Re: How Rowids are assigned to rows by Oracle? [message #596338 is a reply to message #596337] Fri, 20 September 2013 18:23 Go to previous messageGo to next message
BlackSwan
Messages: 22525
Registered: January 2009
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
Re: How Rowids are assigned to rows by Oracle? [message #596339 is a reply to message #596338] Fri, 20 September 2013 18:38 Go to previous messageGo to next message
gupta27
Messages: 28
Registered: August 2013
Location: Delhi
Junior 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?
Re: How Rowids are assigned to rows by Oracle? [message #596340 is a reply to message #596339] Fri, 20 September 2013 18:40 Go to previous messageGo to next message
BlackSwan
Messages: 22525
Registered: January 2009
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 Go to previous message
John Watson
Messages: 4403
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?
Previous Topic: Please help me to close the IF blocks.
Next Topic: Model clause
Goto Forum:
  


Current Time: Tue Jul 29 15:08:45 CDT 2014

Total time taken to generate the page: 0.18743 seconds