Re: MERGE command and order of inserts updates

Hi,

I think the way you are using the MERGE command is not correct, based on a set perspective and that's where you have a problem. Avoid thinking in terms or "orders of the rows are processed" and consider the result you are trying to achieve in term of "sets".

First of all, what is the MERGE command doing?

It takes a result set from a query as an input that contains all the rows you would like to see at the output, based on KEY fields. It then merges (hence the name) the source set with the destination set, based on a joining condition.

If the row already exists (based on the KEY usually, if not you can get into troubles), you want to update one or more NON KEY fields. If the row does not exist in the destination table, you want to insert the full row.

So, as a summary (simplified):

MERGE into DESTINATION
USING (THE RESULT SET TO USE AS A SOURCE) ON (JOIN CONDITIONS BETWEEN THE DESTINATION AND SOURCE) WHEN MATCHED
 UPDATE NON-KEY FIELDS
WHEN NOT MATCHED
 INSERT A COMPLETE ROW There are constraints obviously:

1/ The result set used as a source MUST be built so that the uniqueness of the fields used in the joining clause is guaranteed. If not, the MERGE will bomb, because there is no way for Oracle to know which row should be commited at the output. Do not think in terms of "what row is processed first", this doesn't make sense from a set perspective.

2/ the UPDATE cannot modify any KEY field of the destination table, for obvious reasons. If you were allowed to modify the key fields, which value should the MERGE command use in its comparison to join? The values before or after? This doesn't make sense either from a set perspective. Also, you could easily generate duplicate rows (based on the KEY of the destination table)

3/ The INSERT command MUST be guaranteed to succeed, based on the JOIN condition used in the ON clause.

Once you shift from a "row by row" approach to a "set" approach, all these constraints make sense, the MERGE command becomes obvious, and your remark saying that the "order by" clause doesn't change anything makes sense too. The whole point of relational databases is to work on "sets" and that's why the "order by" is usually irrelevant for bulk processing.

Patrice Borne Received on Sun Jul 31 2005 - 15:42:00 CDT