Do Not Update the Same Row Twice

David Clement

September 2004

Oracle versions 7-8

How does the disk I/O algorithm work if the following two statements are submitted?

          exec sql update t
          set c = 1 
          where tkey = :hostvariable;
          exec sql update t
          set d = 2
          where tkey = :hostvariable;
       

This means that for the first DML statement, a query is constructed internally (as part of the execution path generated by the optimizer) to identify the row that will be updated. The query is processed like any other implicit query, and eventually the SP finds the row that it needs by inspecting block directories in the database buffer cache. When the SP finds the row, it puts the buffer at the MRU end of the LRU list, copies the block into a rollback segment snapshot, updates the BVN, modifies the block data in the database buffer cache, and changes the buffer status to "dirty."

For the second DML statement, the same query is constructed internally (or re-used as the case may be) and again the SP looks for the row to update. It probably will find the block it wants at the MRU end of the LRU list, but again, the SP must copy the block into a rollback segment snapshot, update the BVN, modify the block data in the database buffer cache, and change the buffer status to "dirty." Since much of this processing has just been done for the first statement, it is effectively wasted.

Because all Oracle I/O is by block, updating column by column is an inefficient use of the I/O mechanism. The efficient way to write the code is the obvious way: update both columns, not just one.

         exec sql update t
         set c = 1, d = 2
         where tkey = :hostvariable;