MERGE accounting with a unique index / constraint for each row or each statement?

Question

Suppose I have the following table with the following restrictions:

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 am doing 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 

Is it off above MERGE ? If it is UPDATE first and then INSERT s, the index / constraint will not be canceled at run time. But if this is the first INSERT s and then UPDATE s, the index will be temporarily invalid and the statement may fail ?.

Can someone explain in detail (or point in the right direction) how Oracle RDBMS deals with such problems? Also, is the same processed when using the LOG ERRORS INTO ?

The main reason I ask this question and why I need a solution: I have MERGE instructions that work for several hours with the LOG ERRORS INTO clause. Error logging seems to work like a standalone transaction. Some unique constraint errors (based on unique indexes) are logged long before the statement completes (among other things, I see that the sequence goes up), and I don't know why (although in the end, after completion, no unique constraint should be canceled). When I look at the ERROR table, I see ORA-00001: a unique constraint (XXX.YYY) is violated during an INSERT operation. I can insert this record from the ERROR table into the main table without causing a unique constraint failure. So I wonder why the error is logged in the first place.

EDIT:. The answers below state that when executing an instruction, restrictions are given at the end of the instruction. I understand and agree (although I would like to know more details about index maintenance in such scenarios). The fact that I do not understand and why this question still has not been answered is the reason that I have these ORA-00001: a unique restriction (XXX.YYY) violated the registered errors while they should not exist. It seems that the error logging mechanism does not behave atomically.

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 little 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 it’s strange that the real MERGE statement (in production) no longer works in PARALLEL, and I still sometimes get this error ...

EDIT4: The best answer that I designated as accepted, although the question itself does not fully answer. This seems to be just a mistake in Oracle.

Answer 1

This merger will never work.

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

For non-differentiable constraints (default):

In an undefined constraint, the Oracle database never cancels the validation of the constraint until the end of the transaction. Instead, the database checks the constraint at the end of each statement. If the constraint is violated, then rolls back.

The above means that constraints are checked at the end of just one SQL statement , but not at runtime.

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

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

At the end, they also wrote that:

The examples in this section illustrate the constraint checking mechanism during the 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, and not just for self-referencing constraints.

Answer 2

Part of the "LOG ERRORS INTO" task, as indicated by other users, occurs after the statement is executed (updating and inserting the part), while checking the restrictions. Thus, you may have errors inserted before completing the constraint check. This is why you see that the error is inserted before the statement completes.

And as the answer for this observation:

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

Make sure you have all the information in a single Merge expression. if you do not update the value in the same statement, but in the other, which occurs between the failed insertion and retry, everything is explainable.

(I mean that the entries in the USING part are not in the same expression.

• session 1: merge using select 4 as id, 'b' as name from dual (error is inserted into the log)
• session 2: merge using select 2 as id, null as name from dual commit ok
• Session 3: You try to paste again, and it works.

)

If you can reproduce the error with one statement, this will be a problem. But you have many sessions in your environment. Check the source of your merger statements. You may have a late arrival or something like that.