Home » SQL & PL/SQL » SQL & PL/SQL » Rowid
Rowid [message #202073] Wed, 08 November 2006 01:54 Go to next message
decci_7
Messages: 68
Registered: March 2006
Member
I have read that Oracle generates the rowid in the hexadecimal form and it uniquely identifies a particular row.

lets take an example:

SQL> select rowid,rownum,i from t1;

ROWID ROWNUM I
----------------------- -------------- ----------
AAAM6MAABAAAO8qAAA 1 1
AAAM6MAABAAAO8qAAB 2 2
AAAM6MAABAAAO8qAAC 3 3
AAAM6MAABAAAO8qAAD 4 4
AAAM6MAABAAAO8qAAE 5 5

Now what if i delete the first record with rowid "AAAM6MAABAAAO8qAAA" from the table t1 and add a new record in the table t1 after deleting.

Will the rowid generated for the new record added be "AAAM6MAABAAAO8qAAF" or will it be "AAAM6MAABAAAO8qAAA" as the record pertaining to this rowid has been deleted.

Or will it go like if the record with the rowid "AAAM6MAABAAAO8qAAA" gets deleted then the record with rowid "AAAM6MAABAAAO8qAAB" gets assigned to "AAAM6MAABAAAO8qAAA" and so on and finally the new record will be assigned the rowid as "AAAM6MAABAAAO8qAAE"??

Please let me know.
Re: Rowid [message #202080 is a reply to message #202073] Wed, 08 November 2006 02:46 Go to previous messageGo to next message
Claud
Messages: 17
Registered: July 2006
Junior Member
After you delete first record with rowid "AAAM6MAABAAAO8qAAA", the new rowid for a row that you have inserted, Oracle will generate the random new rowid, not the next rowid after the last one.

Here you have to pay attention is the rownum for the second record is changed to the rownum first record that you have deleted before.
Re: Rowid [message #202096 is a reply to message #202080] Wed, 08 November 2006 03:25 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Claud wrote on Wed, 08 November 2006 09:46
Oracle will generate the random new rowid, not the next rowid after the last one.
I partially agree: it is indeed not the "next rowid" but it isn't random either. The rowid refers to the physical location of the record.
MHE
Re: Rowid [message #202393 is a reply to message #202073] Thu, 09 November 2006 08:36 Go to previous messageGo to next message
rameshuddaraju
Messages: 69
Registered: June 2005
Location: India
Member

ROWID is a pseudo-column. It is not data in the database or table so much as it is a mapping of the location, in a specific datafile of the physical location of a row of data.
Since rows can migrate from location-to-location when they are updated ROWID should never be stored for future purpose.
Re: Rowid [message #202396 is a reply to message #202393] Thu, 09 November 2006 09:04 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Not entirely true.
When a row migrates, a marker is left in the original block pointing to the new location of the row.
Re: Rowid [message #204098 is a reply to message #202073] Fri, 17 November 2006 16:08 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
True, but his point about never storing rowid as a perm pointer is correct. Any move or import will break the application and is generally only asking for problems.
Re: Rowid [message #204314 is a reply to message #204098] Mon, 20 November 2006 02:12 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
True. It generally asking for trouble in the long run.
In the short run, for things like data cleaning scripts, it can work really really well.
Previous Topic: "&" in insert statements
Next Topic: Query Help
Goto Forum:
  


Current Time: Sun Dec 04 06:56:19 CST 2016

Total time taken to generate the page: 0.19673 seconds