Oracle Server - MERGE command and order of inserts updates

Author

MERGE command and order of inserts updates

srini

2005-07-29, 7:23 am

Hi,

I get an Unique constraint violation for the following merge command

MERGE INTO destination D
USING ( SELECT * FROM DEP_ACCT
where
UPDT_TS BETWEEN TO_DATE('&1','MM/DD/YYYY HH24:MI:SS') AND
TO_DATE('&2','MM/DD/YYYY HH24:MI:SS')

) S
ON (S.DEP_ACCT_ID = D.DEP_ACCT_ID )
WHEN MATCHED THEN
UPDATE
SET
D.BNK_RTE_NB = S.BNK_RTE_NB,
D.DEP_ACCT_NB = S.DEP_ACCT_NB,
WHEN NOT MATCHED THEN
INSERT (D.DEP_ACCT_ID,D.BNK_RTE_NB)
VALUES(S.DEP_ACCT_ID,S.BNK_RTE_NB,S.DEP_ACCT_NB).

A unique constraint index is defined on columns BNK_RTE_NB and
DEP_ACCT_NB on the source as well as the destination tables.

DEP_ACCT_ID is the primary key on source and destination tables.

Because of this constraint, it is important that updated records are
merged prior to insertion of new records, especially when the
combination of BNK_RTE_NB and DEP_ACCT_NB already exists in
destination.

Questions:

What is the order of records, that the MERGE command performs the
upserts ? Is it consistent can we rely on the order.

Will the order vary, depending on the number of records that the join
returns ?

If I want to use MERGE command, what is the workaround for my
problem(without reming the constraints, on destination) ?

Thanks in advance.
srini

Holger Baer

2005-07-29, 7:23 am

srini wrote:
> Hi,
>
> I get an Unique constraint violation for the following merge command
>
> MERGE INTO destination D
> USING ( SELECT * FROM DEP_ACCT
> where
> UPDT_TS BETWEEN TO_DATE('&1','MM/DD/YYYY HH24:MI:SS') AND
> TO_DATE('&2','MM/DD/YYYY HH24:MI:SS')
>
> ) S
> ON (S.DEP_ACCT_ID = D.DEP_ACCT_ID )
> WHEN MATCHED THEN
> UPDATE
> SET
> D.BNK_RTE_NB = S.BNK_RTE_NB,
> D.DEP_ACCT_NB = S.DEP_ACCT_NB,
> WHEN NOT MATCHED THEN
> INSERT (D.DEP_ACCT_ID,D.BNK_RTE_NB)
> VALUES(S.DEP_ACCT_ID,S.BNK_RTE_NB,S.DEP_ACCT_NB).
>
> A unique constraint index is defined on columns BNK_RTE_NB and
> DEP_ACCT_NB on the source as well as the destination tables.
>
> DEP_ACCT_ID is the primary key on source and destination tables.
>
> Because of this constraint, it is important that updated records are
> merged prior to insertion of new records, especially when the
> combination of BNK_RTE_NB and DEP_ACCT_NB already exists in
> destination.
>
> Questions:
>
> What is the order of records, that the MERGE command performs the
> upserts ? Is it consistent can we rely on the order.
>
> Will the order vary, depending on the number of records that the join
> returns ?
>
> If I want to use MERGE command, what is the workaround for my
> problem(without reming the constraints, on destination) ?
>
> Thanks in advance.
> srini
>

To control the order of records, you can include an order by in the select
statement - at least on 10g where i tested this.

But if that's going to help you in your specific case, I really can't tell.

HTH
Holger

srini

2005-07-29, 7:23 am

Thanks for the reply. I am using 9i, and MERGE command ignores the
order by clause.

Patrice Borne

2005-07-31, 8:23 pm

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

Sponsored Links