7 Replies Latest reply on Jan 13, 2012 11:09 PM by rp0428

    execution order of update and insert clause with merge

    db-geek
      Hi,

      we are currently upgrading a Datawarehouse database from Oracle 9iR2 to Oracle 11gR2 and I tested a MERGE statement, which ends in 11gR2 with "ORA-00001: unique constraint (UK_TDMH_01) violated".

      In 9i the statement is successfully executed.

      I checked the failure data in 11gR2 with dml error logging, but there are no duplicated records.

      There is a unique constraint on the table, which belongs to a "valid to date" column.

      Always the actual valid record has as a "valid to date" with the value "31.12.2099".

      The MERGE update clause sets the record with the "valid to date" = "31.12.2099" to SYSDATE and the insert clause inserts a new record with "valid to date" with the value "31.12.2099".
      With Oracle 9iR2 MERGE has no problems with the unique constraint on the "valid to date" column.

      On 11gR2 the MERGE with a unique constraint on the "valid to date" fails.

      Without the unique constraint the MERGE can be executed successfully in 11gR2.
      Also, afterwards the unique constraint can be created without any problems or duplicate records.

      Does anyone knows, if the MERGE processing order of the insert and update clause have been changed?
      Or it is possible, to use the old behaviour from 9iR2?
      Or is there any way, to change the processing order of the update and insert clause?

      I tried already to change the data sorting in the USING clause and changed the position of the insert and update clause in the MERGE statement, but nothing succeeded.
        • 1. Re: execution order of update and insert clause with merge
          Toon Koppelaars
          That to me sounds like a bug. Try creating a reproducable case and log a SR with Oracle.
          I'm unable to reproduce this in 11Gr1:
          SQL> create table merge_Test( i number primary key, d date unique);
          
          Tabel is aangemaakt.
          
          SQL> insert into merge_test values(1,to_date('01-01-2012','DD-MM-YYYY'));
          
          1 rij is aangemaakt.
          
          SQL> insert into merge_test values(2,to_date('02-01-2012','DD-MM-YYYY'));
          
          1 rij is aangemaakt.
          
          SQL> commit
            2  ;
          
          Commit is voltooid.
          
          SQL> select * from merge_test;
          More...
          
                   I D
          ---------- ----------
                   1 01-01-2012
                   2 02-01-2012
          
          2 rijen zijn geselecteerd.
          
          
          SQL> merge into merge_test dest
            2  using (select 3 as x from dual union all select 2 as x from dual) src
            3  on (dest.i = src.x)
            4  when not matched then
            5    insert(i,d) values(src.x,to_date('02-01-2012'))
            6  when matched then
            7    update set dest.d = to_date('03-01-2012')
            8  /
          
          2 rijen zijn samengevoegd.
          
          SQL> rollback;
          
          Rollback is voltooid.
          
          SQL> merge into merge_test dest
            2  using (select 2 as x from dual union all select 3 as x from dual) src
            3  on (dest.i = src.x)
            4  when not matched then
            5    insert(i,d) values(src.x,to_date('02-01-2012'))
            6  when matched then
            7    update set dest.d = to_date('03-01-2012')
            8  /
          
          2 rijen zijn samengevoegd.
          
          SQL>
          I'm trying to temporarily create a UK violation for 02-01-2012. These temporary violations should be allowed. That is you should not have to change the timing of the UK constraint to deferred (which could be a quick and dirty workaround for you right now).
          1 person found this helpful
          • 2. Re: execution order of update and insert clause with merge
            Lakmal Rajapakse
            It might be a bug with merge statement in 11.2. But that is beside the point - this is really bad design. Primary keys are supposed to be immuatble - you should not be updating them. I think instead of having a "valid to date" column you should have a "valid from date" column.
            • 3. Re: execution order of update and insert clause with merge
              db-geek
              There are some points, which are not included in my first description:
              - the table is used as SCD2 dimension table
              - the "valid to date" is with a another column a unique key, not in a primary key
              - there is also a primary key, which is be used in the MERGE ON condition
              - there is also a "valid from date" column in the table, which is not indexed, because it is not necessary
              - there is also an indexed "valid flag" in the table for identifying the actual valid record

              I intended the usage of the "valid to date" in the unique key because of possible performance reasons.
              My intention was, when selecting actual valid data from the table I can use the fixed "valid to date" "31.12.2099" to identify the valid record in combination with the other attribute.

              When I create the unique key using "valid from date", the MERGE is executed without any problems in 11gR2.
              The new "valid from date" is then set to SYSDATE.

              It seems for me, that MERGE in the new database tries to insert the new record with the "valid to date" with value "31.12.2099" before the update of the existing row is executed.
              But the insert can't be successfully completed because of the unique constraint violation.

              In the old database this behaviour seems to be contrary, because there are no such problems.

              If there are no other solutions, I will change the unique key from "valid to date" to "valid from date".

              But as Toon Koppelaars already stated, it sounds for me like a bug.
              Or a wrong database setting, which I haven't found until now.
              • 4. Re: execution order of update and insert clause with merge
                db-geek
                Toon Koppelaars wrote:
                That to me sounds like a bug. Try creating a reproducable case and log a SR with Oracle.
                I'm unable to reproduce this in 11Gr1:

                I'm trying to temporarily create a UK violation for 02-01-2012. These temporary violations should be allowed. That is you should not have to change the timing of the UK constraint to deferred (which could be a quick and dirty workaround for you right now).
                I tried Your example here on 11gR2 and there is no unique constraint violation, too.

                I also changed it a little bit, to be nearer at the failing statement
                d> 
                d> create table ts_merge_Test
                  2  (i number primary key
                  3   ,val_key VARCHAR2(20)
                  4   ,valid_until date
                  5  );
                
                Table created.
                
                d> 
                d> CREATE UNIQUE INDEX ts_uk ON ts_merge_test
                  2  (val_key
                  3   ,valid_until
                  4  );
                
                Index created.
                
                d> 
                d> insert into ts_merge_test
                  2  values(1,'A',to_date('31.12.2099','DD.MM.YYYY'))
                  3  ;
                
                1 row created.
                
                d> commit;
                
                Commit complete.
                
                d> 
                d> select * from ts_merge_test;
                
                         I VAL_KEY              VALID_UNT
                ---------- -------------------- ---------
                         1 A                    31-DEC-99
                
                1 row selected.
                
                d> 
                d> merge into ts_merge_test dest
                  2  using (SELECT
                  3                1 AS i
                  4                ,'A' AS val_key
                  5         FROM   dual
                  6         WHERE  0 = 0
                  7         UNION ALL
                  8         SELECT
                  9                2 AS i
                 10                ,'A' AS val_key
                 11         FROM   dual
                 12         WHERE  0 = 0
                 13        ) src
                 14  on (dest.i = src.i)
                 15  when not matched
                 16  then
                 17      insert(i,val_key,valid_until)
                 18      values(src.i,src.val_key,to_date('31.12.2099','DD.MM.YYYY'))
                 19  when matched
                 20  then
                 21      update set dest.valid_until = TRUNC(SYSDATE)
                 22  /
                
                2 rows merged.
                
                d> 
                d> COMMIT;
                
                Commit complete.
                
                d> 
                d> select * from ts_merge_test;
                
                         I VAL_KEY              VALID_UNT
                ---------- -------------------- ---------
                         1 A                    13-JAN-12
                         2 A                    31-DEC-99
                
                2 rows selected.
                Here is no unique constraint violation, too.
                The failing merge statement is much more complex, so it seems, that the complexity is necessary to reproduce the error.
                • 5. Re: execution order of update and insert clause with merge
                  rp0428
                  Read this post in the general DB forum: Merge no work

                  user503699 explains how MERGE is a SET operation. So the initial query sees records the way they existed at the start of the operation.

                  If the query inserts a record and then a later query record tries to update it in a way that violates a constraint it will fail.

                  The post has a simple example that shows this. I can't speak to any possible difference with MERGE in an earlier version. If there is a difference it may be due to the earlier version having a bug that was fixed.

                  Hope this helps.
                  • 6. Re: execution order of update and insert clause with merge
                    db-geek
                    rp0428 wrote:
                    Read this post in the general DB forum: Merge no work

                    user503699 explains how MERGE is a SET operation. So the initial query sees records the way they existed at the start of the operation.

                    If the query inserts a record and then a later query record tries to update it in a way that violates a constraint it will fail.

                    The post has a simple example that shows this. I can't speak to any possible difference with MERGE in an earlier version. If there is a difference it may be due to the earlier version having a bug that was fixed.
                    Thanks for the link to the post in the DB forum, but that post belongs to 9.2, while my problem is in 11.2. Both identical statements are executed with the same data, but in 11gR2 the MERGE fails with a unique constraint failure.

                    The data for insert and update is so prepared in the USING clause, that the update is only executed on existing records in the target table.
                    New inserted data can't be affected by the update.

                    I hope to find some time next week, to reproduce the error with a testcase and assign it to Oracle support.
                    • 7. Re: execution order of update and insert clause with merge
                      rp0428
                      Well then you original question doesn't make sense because in it you said:

                      >
                      Does anyone knows, if the MERGE processing order of the insert and update clause have been changed?

                      >

                      Since the MERGE is a SET operation its only view is of the data that existed at the start time. So by definition the MERGE processing order makes no difference whatsoever. The INSERT clause will only insert records that don't exist at the start of the merge (based on primary key or unique indexes) and the UPDATE will only update records that already exist at the start of the merge (and will not include records created by the INSERT clause).

                      >
                      The data for insert and update is so prepared in the USING clause, that the update is only executed on existing records in the target table.
                      New inserted data can't be affected by the update.

                      >

                      Divide and conquer - eliminate the INSERT part of the merge to see if the failure is occuring in the UPDATE part. Then eliminate the UPDATE part of the merge to see if the INSERT part fails.

                      You also said that;

                      >
                      The MERGE update clause sets the record with the "valid to date" = "31.12.2099" to SYSDATE and the insert clause inserts a new record with "valid to date" with the value "31.12.2099".

                      >

                      You haven't posted the code so we have no idea how you are locating records to be inserted and updated.
                      We also don't know if the unique index is on the "valid to date" column (post the table ddl please) or a combination of that date column and one or more other columns. So a record with '31.12.2099' for that indexed value already exists the INSERT will fail regardless of the key value used to try to locate the new record.

                      And the UPDATE clause will only get executed on an existing record based on your (to us unknown without the code) key fields and that existing record could have any value for "valid to date". If the unique index is only on the single "valid to date" column then only one record in your table can have a column with that value. If the unique index is multi-column then you are violating that multi-column condition.

                      It does us not good to speculate about what your data model is. Please provide the relevant table and index DDL as well as the merge query you are using. There have been no changes to MERGE that are related to your question that I am aware of.