Hello All,
I was just curious about what exactly happens when we execute "Update" statement.... I found the answer in one of the Oracle Fundamentals ...
Here we go...
1] For any DML operation ; it is necessary to work on both data blocks and undo blocks and also regenerate redo A , C & I of the ACID test require generation of UNDO ; the UNDO segment is just another segment.
2] First step is same as the execution of select command {only diff is empty block of UNDO segment is required}
3] First , locks must be placed on any rows and associated index keys that will be affected by the operation.
4] Then the redo is regenerated: the server process writes to the log buffer the changes that are going to be applied to the data blocks.
5] This generation of redo Is applied to both table block changes and undo block changes : if a column is to be updated the new value of the column is written to the log buffer { which is the change that will be applied to the table block } and also the old value { which is the change that will be applied to the undo block}.
6] If the column I part of an index key , then the changes to be applied to the index are also written to the log buffer , together with an undo block change to protect the index changes.
7] Having generated the redo, the update is carried out in the DB buffer cache : the block of the table is updated with the new version of the changed column and the old version of the changed column is written to the block of an undo segment.
8] From this point until the update is committed, all queries from other sessions addressing the changed row will be redirected to the undo data.
9] Only the session this is doing the update will see the actual current version of the row in the table block. The same principle applies to any associated index changes.
