Yesterday, there was a discussion which was going on OTN (Oracle Forum). I was also part of that discussion. Discussion was about the “Does UPDATE statement change rowid in Oracle?”

As per my comment of “UPDATE does not change rowid”, one of the user has pointed me that UPDATE statement sometime changes the rowid in Oracle and given me following example.

SQL> ed  
Wrote file afiedt.buf  
   
  1  create table moving_rowid (  
  2    col1 number primary key,  
  3    col2 number,  
  4    col3 number,  
  5    col4 varchar2(10)  
  6  )  
  7* organization index  
SQL> /   
 Table created.  
SQL> insert into moving_rowid values( 1, 2, 3, 'foo' );  
1 row created.  
  
SQL> insert into moving_rowid values( 2, 3, 4, 'bar' );  
1 row created.  
    
SQL> select rowid, col1, col2, col3, col4  
  2    from moving_rowid;  
ROWID                  COL1       COL2       COL3 COL4  
---------------- ---------- ---------- ---------- ----------  
*BAEADxsCwQL+             1          2          3 foo  
*BAEADxsCwQP+             2          3          4 bar  
   
SQL> update moving_rowid set col1=col1+1;  
2 rows updated.  
   
SQL> commit;  
Commit complete.  
   
SQL> select rowid, col1, col2, col3, col4  
  2    from moving_rowid;  
ROWID                  COL1       COL2       COL3 COL4  
---------------- ---------- ---------- ---------- ----------  
*BAEADxsCwQP+             2          2          3 foo  
*BAEADxsCwQT+             3          3          4 bar  

Above example has made me to think and I have given the following answer on forum with example to show that UPDATE does not change the rowid:

SQL> select rowid, col1,col2,col3,col4 from moving_rowid;  
ROWID    COL1  COL2 COL3 COL4  
AAAFiAAAEAAAAhgAAA  1  2  3 foo  
AAAFiAAAEAAAAhgAAB  2  3  4 bar  
  
SQL> update moving_rowid set col1=col1+1;  
2 rows updated.  
  
SQL> commit;  
Commit complete.  
  
SQL> select rowid, col1,col2,col3,col4 from moving_rowid;  
ROWID    COL1  COL2 COL3 COL4  
AAAFiAAAEAAAAhgAAA  2  2  3 foo  
AAAFiAAAEAAAAhgAAB  3  3  4 bar  

No changes in rowid, reason there is no organization has been done as per the index.

Please note: if you change any indexed column and table has an index for organizing data then index will change. This is what happens with partition too.

Now, see the example given below with organization index:

SQL> update moving_rowid set col1=col1+1;  
2 rows updated.  
  
SQL> commit;  
Commit complete.  
  
SQL> select rowid,col1,col2,col3,col4 from moving_rowid;  
ROWID   COL1  COL2 COL3  COL4  
*BAEACGwCwQP+  2  2   3   foo  
*BAEACGwCwQT+  3  3  4  bar  
  
Now lets update non-index column:  
SQL> select rowid,col1,col2,col3,col4 from moving_rowid;  
ROWID   COL1  COL2  COL3 COL4  
*BAEACGwCwQP+  2  3  3 foo  
*BAEACGwCwQT+  3  4  4 bar  

I hope above would have clear the behavior. UPDATE does not change the rowid. Users who see change in rowid is actually movement of data pointer. data pointer changes if the organization done on the basis of index (and each update changing the index too) or if row-movement has happened.

Therefore it’s not best practice to do update on the basis of rowid.

Forum link is given below:
http://forums.oracle.com/forums/message.jspa?messageID=9115665#9115665

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s