MERGE statement unique index/constraint validation per row or per statement?

Question

Suppose I have the following table with the following constraints:

create table test as (
    select 1 as id, 'a' as name from dual 
    union all 
    select 2, 'b' from dual 
    union all 
    select 3, 'c' from dual
);

create unique index ind on test(name);

alter table test add constraint constr unique (name);

select * from test;

        ID NAME
---------- ----
         1 a   
         2 b   
         3 c   

Suppose now that I do the following MERGE:

merge into test t using (
    select 4 as id, 'b' as name from dual 
    union all 
    select 2 as id, null as name from dual 
) s on (s.id = t.id) 
    when matched then update set t.name = s.name
    when not matched then insert(t.id, t.name) values(s.id, s.name)

select * from test;

        ID NAME
---------- ----
         1 a   
         2     
         3 c   
         4 b   

Will the above MERGE ever fail? If it UPDATEs first, and then INSERTs, the index/constraint will not be invalidated during execution. But if it first INSERTs, and then UPDATEs, the index will be temporary invalidated and the statement might fail?.

Can someone explain in detail (or point in the right direction) how Oracle RDBMS handles such issues? Furthermore, is the handling the same when using the LOG ERRORS INTO clause?

Main reason why I ask this question and why I need a solution: I have MERGE statements running for several hours with LOG ERRORS INTO clause. The error logging seems to work as an autonomous transaction. Some unique constraint errors (based on unique indexes) are logged far before the statement finishes upserting (among others, I see the sequence going up), and I do not know why (although in the end, after upserting, no unique constraint should be invalidated). When I look into the ERROR table, I see ORA-00001: unique constraint (XXX.YYY) violated on an INSERT operation. I can insert this record from the ERROR table into main table without causing unique constraint failure. So I wonder why the error is logged in the first place.

EDIT: The answers below assert that when a statement is executed, the constraints are enforced at the end of the statement. I understand and agree (while I would like to know more details about index maintenance in such scenarios). What I do not understand and why this question is still not answered is why I am having these ORA-00001: unique constraint (XXX.YYY) violated errors logged while they should not be. Seems like the error logging mechanism doesn't behave in an atomic way.

EDIT2:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0  Production
TNS for Solaris: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

EDIT3: I played a bit and was able to reproduce this error:

drop table test;

drop table err_test;

create table test as (
    select 1 as id, 'a' as name from dual 
    union all 
    select 2, 'b' from dual 
    union all 
    select 3, 'c' from dual
);

create unique index ind on test(name);

alter table test add constraint constr unique (name);

--select test.rowid, test.* from test;

BEGIN
DBMS_ERRLOG.CREATE_ERROR_LOG (
   dml_table_name            => 'TEST',
   err_log_table_name        => 'ERR_TEST');
END;
/

--truncate table err_test;

select * from err_test;

merge /*+ PARALLEL(t 2) */ into test t using (
    select 4 as id, 'b' as name from dual 
    union all 
    select 2 as id, null as name from dual 
) s on (s.id = t.id) 
    when matched then update set t.name = s.name
    when not matched then insert(t.id, t.name) values(s.id, s.name)
LOG ERRORS INTO ERR_TEST('TEST,ID:'||s.id) REJECT LIMIT UNLIMITED;

select * from err_test;

In the last select * from err_test; I always get: ORA-00001: unique constraint (XXX.CONSTR) violated. Now the strange thing is that the real MERGE statement (in production) doesn't work in PARALLEL any more, and I still get this error sometimes...

EDIT4: The best answer I have marked as accepted, although the question itself is not answered completely. It seems it is just a bug in Oracle.

Answer 1

This merge never fails.

This is explained with examples here: Database Concepts - 5. Data Integrity

For a not defferrable constrains (default):

In a nondeferrable constraint, Oracle Database never defers the validity check of the constraint to the end of the transaction. Instead, the database checks the constraint at the end of each statement. If the constraint is violated, then the statement rolls back.

The above means, that constraints are checked at the end of the entire single SQL statement, but not during their execution.

Below, in this documentation, you can find two examples of transactions, that "internally", during their execution, violate some constraint rules, but at the end they fulfill all constraint, and there are legal, because:

... because the database effectively checks constraints after the statement completes. Figure 5-4 shows that the database performs the actions of the entire SQL statement before checking constraints.

In the end they also wrote that:

The examples in this section illustrate the constraint checking mechanism during INSERT and UPDATE statements, but the database uses the same mechanism for all types of DML statements. The same mechanism is used for all types of constraints, not just self-referential constraints.

Answer 2

The "LOG ERRORS INTO" part of the job, as the other users pointed, happens after the statement was executed(update and insert part), while checking constraints. So you can have errors inserted before the constraint checking is finished. This is why you see the error inserted before the statement is totally finished.

And as an answer for this observation:

I can insert this record from the ERROR table into main table without causing unique constraint failure. So I wonder why the error is logged in the first place.

Be sure you have the entire information in one Merge statement. if you don't update the value in the same statement but in another which occurs between your failed insert and your retry, things are explainable.

(What I mean is the records in the USING part are not in the same statement.

• session 1: merge using select 4 as id, 'b' as name from dual (the error is inserted in log)
• session 2: merge using select 2 as id, null as name from dual commit ok
• session 3: you retry the insert and it works

)

If you can reproduce the error with one statement, that would be a problem. But you have many sessions in your environment. Please check the source of your Merge statements. You may have late arrivals, or something like this.