2
votes

I wonder what is the behaviour of Oracle regarding the subquery and the execution of the update and delete clause.

I wonder if Oracle:

  1. executes the subquery and for each row it executes the update and the delete clauses
  2. executes the subquery for update and then executes the subquery for delete
  3. 1) and 2), the optimizer chooses the best strategy
  4. 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

3
I already read the doc and it is not helpfull about the order - Nicolas Labrot

3 Answers

1
votes

The SQL standard guarantees three phases:

  1. Computing the new values for all rows being updated (read-only phase)
  2. Changes are applied all at once
  3. Constraints are verified

This means that all "subqueries" logically execute before the first write happens. This might be implemented using different physical plan shapes but that does not concern your application logic.

0
votes

Forgot to answer this issue.

The issue is known and should be fixed with patch 11.2.0.4 (released Q4 2013).

Oracle suggests the following temporary workaround :

alter session set "_complex_view_merging"=false;"  

Or the hint NO_MERGE

-1
votes

I do not believe Oracle guarantees the order of operation for a compound statement like this. I would expect that an explain plan generated will show what the exact order of operations are. I have used TOAD to dissect/tune queries like this in the past.