implementation - Could somebody explain what the MERGE statement really does in Oracle?

Question

I am looking for a clear explanation of what the MERGE statement in Oracle really does.

Here is what I am after:

MERGE INTO (target_table) t
USING (source_view) s
   ON (join condition)
 WHEN MATCHED THEN UPDATE SET col1 = val1 [, ...]
 WHEN NOT MATCHED THEN INSERT (col1 [, ...]) VALUES ( val1 [, ...])

• what kind of join is performed? I think it is full outer join, am I right?
• regarding the WHEN MATCHED part: what happens when a row from t matches multiple rows from s?
• regarding the WHEN NOT MATCHED part I believe it means “when a row in s has no correspondence in t”. Am I right?

Thank you.

Answer 1

what kind of join is performed? I think it is full outer join, am I right?

No, it's a regular outer join. The query needs to know when there are rows in the target table that are also in the source table and when there are records in the source table that are not in the target table. Since the query doesn't need to respond to rows that are in the target table but are not in the source table, it doesn't need the outer join to go both ways.

However, the outer join will not be performed if there is no not matched clause (which is perfectly valid). The optimizer is smart enough to know that in that case, an inner join is sufficient.

regarding the WHEN MATCHED part: what happens when a row from t matches multiple rows from s?

When there are multiple matches, the update is performed for each match. This means that whichever update comes last will be the one written in the commit. There's no way to dictate an order, so in this case the source of the update is effectively random (from the set of matches).

As @ Vincent Malgrat pointed out, this was incorrect. It seems that Oracle will produce an "ORA-40926: unable to get a stable set of rows in the source table" error if there are multiple matches.

regarding the WHEN NOT MATCHED part I believe it means “when a row in s has no correspondence in t”. Am I right?

That is correct.