When doing updates with statements that use correlated subqueries, it’s important to make sure that your brackets (parentheses) are in the right place. Here are two statements that look fairly similar but have remarkably different results – and sometimes people don’t realise how different the statements are:
update t1
set
padding = (
select
t2.padding
from t2
where t2.id = t1.id
)
where t1.small_vc <= lpad(20,10,'0')
;
update t1
set
padding = (
select
t2.padding
from t2
where t2.id = t1.id
and t1.small_vc <= lpad(20,10,'0')
)
;
The first statement will update rows in t1 where t1.small_vc <= lpad(20,10,’0′), copying the value of padding from t2 where (if) the id columns match, but setting t1.padding to null if there is no match.
The second statement will update every single row in t1 – some of the rows will probably be updated as expected (i.e. to get the same result as the first statement) but every row where t1.small_vc is greater than lpad(20,10,’0′) will have the padding column set to null.
Originally I created this little demo to respond to a question on OTN – but when I ran it with rowsource execution statistics enabled, I discovered that it also happened to demonstrate an odd bug in that feature – even in 11.2.0.3. Here are the two plans corresponding to the above two statements. The update is supposed to update 20 rows in t1, the incorrect form of the code will update all 10,000 rows in the table:
20 rows updated. PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ SQL_ID 73tz2pqv4z8rc, child number 0 ------------------------------------- update t1 set padding = ( select t2.padding from t2 where t2.id = t1.id ) where t1.small_vc <= lpad(20,10,'0') Plan hash value: 417405447 ------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------ | 0 | UPDATE STATEMENT | | 1 | | 0 |00:00:00.01 | 219 | | 1 | UPDATE | T1 | 1 | | 0 |00:00:00.01 | 219 | |* 2 | TABLE ACCESS FULL | T1 | 1 | 20 | 20 |00:00:00.01 | 173 | | 3 | TABLE ACCESS BY INDEX ROWID| T2 | 20 | 1 | 20 |00:00:00.01 | 45 | |* 4 | INDEX UNIQUE SCAN | T2_PK | 20 | 1 | 20 |00:00:00.01 | 25 | ------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("T1"."SMALL_VC"<='0000000020') 4 - access("T2"."ID"=:B1) 10000 rows updated. PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ SQL_ID ax22zpj8xdwpn, child number 0 ------------------------------------- update t1 set padding = ( select t2.padding from t2 where t2.id = t1.id and t1.small_vc <= lpad(20,10,'0') ) Plan hash value: 2442374960 ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------- | 0 | UPDATE STATEMENT | | 3 | | 0 |00:00:00.27 | 21576 | | 1 | UPDATE | T1 | 3 | | 0 |00:00:00.27 | 21576 | | 2 | TABLE ACCESS FULL | T1 | 3 | 10000 | 29692 |00:00:00.03 | 514 | |* 3 | FILTER | | 19692 | | 40 |00:00:00.01 | 85 | | 4 | TABLE ACCESS BY INDEX ROWID| T2 | 40 | 1 | 40 |00:00:00.01 | 85 | |* 5 | INDEX UNIQUE SCAN | T2_PK | 40 | 1 | 40 |00:00:00.01 | 45 | ------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter(:B1<='0000000020') 5 - access("T2"."ID"=:B1)
Note, in both cases, how the number of rows reported in the update operation (line 1) is zero although we might expect it to match the A-rows value reported in the table access full operation (line 2) which tells us how many rows were identified for update. But something strange has happened in the line reporting the table access full in the second case – clearly there’s a bug somewhere because there were only 10,000 rows in the table, yet the tablescan has (apparently) identified 29,692 rows for update.
Not only is the tablescan line reporting silly numbers, the filter line is as well (because that should only start 10,000 times – once per row in the table) moreover the subquery lines (4 and 5) should only start 20 times each because the filter should eliminate the need to run the subquery for all rows except the 20 where “T1″.”SMALL_VC”<=’0000000020. Even stranger is the fact that when I ran this test several times in a row (recreating the tables each time), the starts and A-rows values for the filter and table access full lines changed (decreasing) every time!
It took me a little while to figure out what was going on – but the doubling of the starts for the subquery gave me a hint, confirmed when I took a snapshot of how the session stats changed when I ran the second update. It’s just another variant of the odd “write consistency” behaviour that can appear when running tablescan through a large volume of data and updating it (although I have to say that I hadn’t seen it happening for a mere 10,000 rows before). Oracle starts the tablescan and update, and after a while decides that it has to rollback and restart the update – the 20 rows that were subject to the subquery were the first 20 rows of the table, which is why they were almost sure to trigger the subquery twice each, had they appeared half way through the table the effect might not have been so consistent).
Footnote:
If you want to repeat my test for yourselves, here’s the code to generate the data sets:
create table t1
as
with generator as (
select --+ materialize
rownum id
from dual
connect by
level <= 1e4
)
select
rownum id,
lpad(rownum,10,'0') small_vc,
rpad('x',100) padding
from
generator v1,
generator v2
where
rownum <= 1e4
;
create table t2
as
select * from t1
;
alter table t1 add constraint t1_pk primary key(id);
alter table t2 add constraint t2_pk primary key(id);
