DML Implies a Query

David Clement

September 2004

Oracle versions 7-8


When the DML statements update and delete include a where clause, Oracle processes the where clause internally as the logical equivalent of a select, that is, the execution plan will include the same row-source operations as if an explicit select had been coded. This is because the rows to be updated or deleted have to be identified before any further processing is done. This is necessary for rollbacks, read consistency, and recovery. The SCN for the data that is about to be changed needs to be saved, and the snapshot needs to be copied into the rollback segments.

This implies, logically, that a select inside a DML statement is suspect, as in the example below, which I've adapted from code that I saw at a client site.

          update t1 set (x = :myvar)
          where t1.y in 
              (select t2.y
              from t2, t1
              where t2.s = t1.s);
								
The nested join of t1 to t2 is redundant, because a query on t1 is going to be formed anyway as a normal part of the execution plan. It is more efficient here to use a correlated subquery, as follows.
          update t1 set (x = :myvar)
          where t1.y in
              (select t2.y
              from t2
              where t2.s = t1.s);
        
This code could still be better if it checked to see if x needs to be updated and if it used the exists operator, as follows.
          update t2 set (x = :myvar)
          where x <> myvar
          and exists
              (select 1
              from t2
              where t2.s = t1.s);
        
Checking to see if the column needs to be updated is helpful because it prevents Oracle unnecessarily updating data blocks and unnecessarily allocating redo space, including rollback segment space.