Update by Row ID

David Clement

September 2004

Oracle versions 7-8

In the rare case when revisiting a row is unavoidable, the way to do it is to use the row ID. The row ID represents the address of the row as a value combining, before Oracle 8, the block number, row number, and data file number, and for Oracle 8 and later, the data object number, data file number, data block number (in the data file, not in the file system) and row number. When table access is by row ID, there is little optimization to be done, and much of the overhead of generating execution plans and so forth is eliminated. The returning clause can easily be used to get the row ID for the second statement, as follows.

          exec update acctstable
          set defaultcreditline = :clDefault
          where acctno = :newacctno
          returning rowid into :chRowId;
          // some unavoidable processing delay goes here...
          exec sql update acctstable
          set creditextension = :crExtend
          where rowid = :chRowId;