Does UPDATE Change ROWID in Oracle?

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