Using ROWID in the application
Posted by decipherinfosys on December 7, 2008
In one of the client engagements, we saw that the code was using ROWID for doing the updates for the tables i.e. it used to retrieve several columns including ROWID and then later on use ROWID to identify the rows it needs to update. As you already know, ROWID is assigned to a row when a record gets created (ROWID is unique within a table) and remains the same for that record unless that row is deleted and re-inserted in which case it is a new row and hence a new ROWID. Here are some of the common examples when this deletion + insertion can happen:
1) Suppose that you have implemented partitioning and the code updates the partitioned key – the update in this case will cause the row to move from one partition to another in which case the ROWID value will change.
2) If the DBA uses the “ALTER TABLE <Table_Name> MOVE” command, then also the ROWID could change.
3) If you have to use the Flashback feature and have to flashback the table to a point in time, then also the ROWID can change.
4) If the DBA uses the “ALTER TABLE <Table_Name> SHRINK SPACE COMPACT”, then also the ROWID can change.
5) If you have the “Enable Row Movement” set for a table, then also the ROWID can change for the record(s).
The reason why the client was using ROWID for the updates was because it is very fast however just using the ROWID does not guarantee that you will always be updating the same record. Here is an example – consider these 3 sessions:
First Session: Fetches a row from table A with rowid A1
Second Session: Deletes the row from table A with rowid A1 and commits the transaction
NOTE ==> Please note that the rowid A1 is now free for re-use by sessions
Third Session: Creates a new row with rowid A1 and commits the transaction
First Session: Now tries to fire the update statement: “update A set …. where rowid = A1″
And this command will not be updating the same record as it intended to. So, be aware of these facts in case you are also using only ROWID in your applications to do the updates.