Menu
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 [, ...])
WHEN MATCHED part: what happens when a row from t matches multiple rows from s? WHEN NOT MATCHED part I believe it means “when a row in s has no correspondence in t”. Am I right?Thank you.
269
Use the MERGE statement to select rows from one or more sources for update or insertion into a table or view. You can specify conditions to determine whether to update or insert into the target table or view. This statement is a convenient way to combine multiple operations.
The MERGE statement tries to compare the source table with the target table based on a key field and then do some of the processing. The MERGE statement actually combines the INSERT, UPDATE, and the DELETE operations altogether.
Our first task was to change the MERGE statement to meet all of the required conditions for optimization: Target table's join column has a unique or primary key constraint. UPDATE and INSERT clauses include every column in the target table. UPDATE and INSERT clause column attributes are identical.
The MERGE statement basically works as separate INSERT, UPDATE, and DELETE statements all within the same statement. You specify a "Source" record set and a "Target" table and the JOIN condition between the two.
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.
101
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
