I wonder what is the behaviour of Oracle regarding the subquery and the execution of the update and delete clause.
I wonder if Oracle:
- executes the subquery and for each row it executes the update and the delete clauses
- executes the subquery for update and then executes the subquery for delete
- 1) and 2), the optimizer chooses the best strategy
- Other?
EDIT:
DB used : Oracle 11.2.0.3.0 I have this pretty query
DROP TABLE T1; DROP TABLE IT1; DROP TABLE OT1; CREATE TABLE T1 ( ID INTEGER, V INTEGER, PIVOT INTEGER ); CREATE TABLE IT1 ( ID INTEGER ); CREATE TABLE OT1 ( ID INTEGER, FOO INTEGER, NV INTEGER ); INSERT INTO T1 (ID,V,PIVOT) VALUES (1,1,1); INSERT INTO T1 (ID,V,PIVOT) VALUES (2,1,1); INSERT INTO IT1 (ID) VALUES (1); INSERT INTO IT1 (ID) VALUES (2); INSERT INTO OT1 (ID,NV,FOO) VALUES (1,2,0); INSERT INTO OT1 (ID,NV,FOO) VALUES (2,2,0); commit; MERGE INTO T1 TARGET USING ( SELECT DISTINCT T1.ID,T1.V, OT1.NV FROM T1 INNER JOIN IT1 ON T1.ID = IT1.ID LEFT OUTER JOIN OT1 ON OT1.ID = IT1.ID WHERE T1.PIVOT = 1 or OT1.FOO=40) SRC ON (SRC.ID = TARGET.ID) WHEN MATCHED THEN UPDATE SET TARGET.V=SRC.NV DELETE WHERE TARGET.V=SRC.V; commit;
If an item has a new version, the item will be updated with the new version (UPDATE clause). If not, the item is destroyed (DELETE clause). Delete should not happens
This statement does not work as I expect. It deletes all the links. It was like the delete clauses execute the subquery with the modified data.
Note the OT1.FOO=40 which is here useless but seems to create the issue. If I add an order by
to the subquery (whatever the order criterion), the statement works correctly.
Thanks,
Nicolas