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.
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, t1
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
update t1 set (x = :myvar)
where t1.y in
(select t2.y
from t2
where t2.s = t1.s);
exists operator, as follows.
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.
update t2 set (x = :myvar)
where x <> myvar
and exists
(select 1
from t2
where t2.s = t1.s);
|
|
|