MERGE оператор уникальный индекс / проверка ограничений на строку или на оператор?

Question

Предположим, у меня есть следующая таблица со следующими ограничениями:

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   

Предположим теперь, что я делаю следующее 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   

Будет выше MERGE когда-нибудь не получится? Если оно UPDATE сначала, а потом INSERT s, индекс / ограничение не будут недействительными во время выполнения. Но если это первое INSERT с, а затем UPDATE s, индекс будет временно признан недействительным, и оператор может завершиться ошибкой?

Может кто-нибудь подробно объяснить (или указать в правильном направлении), как СУБД Oracle решает такие проблемы? Кроме того, является ли обработка тем же при использовании LOG ERRORS INTO статья?

Основная причина, по которой я задаю этот вопрос и почему мне нужно решение: у меня есть операторы MERGE, работающие в течение нескольких часов с предложением LOG ERRORS INTO. Регистрация ошибок, похоже, работает как автономная транзакция. Некоторые уникальные ошибки ограничения (основанные на уникальных индексах) регистрируются задолго до того, как оператор заканчивает upserting (среди прочего, я вижу, что последовательность идет вверх), и я не знаю почему (хотя в конце, после upserting, не должно быть никакого уникального ограничения аннулированный). Когда я просматриваю таблицу ERROR, я вижу ORA-00001: ограничение уникальности (XXX.YYY) нарушено в операции INSERT. Я могу вставить эту запись из таблицы ОШИБКА в основную таблицу, не вызывая уникальный сбой ограничения. Поэтому мне интересно, почему ошибка вошла в первую очередь.

РЕДАКТИРОВАТЬ: ответы ниже утверждают, что при выполнении инструкции ограничения применяются в конце инструкции. Я понимаю и согласен (хотя я хотел бы узнать больше подробностей о ведении индекса в таких сценариях). Что я не понимаю и почему на этот вопрос до сих пор нет ответа, так это то, почему у меня регистрируются ошибки ORA-00001: уникальные ограничения (XXX.YYY), а их не должно быть. Похоже, механизм регистрации ошибок не ведет себя атомарно.

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: я играл немного и смог воспроизвести эту ошибку:

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;

Напоследок select * from err_test; Я всегда получаю: ORA-00001: unique constraint (XXX.CONSTR) violated, Теперь странно то, что настоящий оператор MERGE (в производстве) больше не работает в PARALLEL, и иногда я все еще получаю эту ошибку...

РЕДАКТИРОВАТЬ 4: Лучший ответ, который я отметил как принятый, хотя сам вопрос не ответил полностью. Кажется, это просто ошибка в Oracle.

Answer 1

Это слияние никогда не подводит.

Это объясняется с примерами здесь: Концепции базы данных - 5. Целостность данных

Для недефференцируемых ограничений (по умолчанию):

В недопустимом ограничении Oracle Database никогда не откладывает проверку достоверности ограничения до конца транзакции. Вместо этого база данных проверяет ограничение в конце каждого оператора. Если ограничение нарушено, то оператор откатывается.

Вышеуказанное означает, что ограничения проверяются в конце всего одного оператора SQL, но не во время их выполнения.

Ниже, в этой документации, вы можете найти два примера транзакций, которые "внутренне" во время их выполнения нарушают некоторые правила ограничения, но в конце они выполняют все ограничения, и есть законные, потому что:

... потому что база данных эффективно проверяет ограничения после завершения оператора. Рисунок 5-4 показывает, что база данных выполняет действия всего оператора SQL перед проверкой ограничений.

В конце они также написали, что:

Примеры в этом разделе иллюстрируют механизм проверки ограничений во время операторов INSERT и UPDATE, но база данных использует один и тот же механизм для всех типов операторов DML. Один и тот же механизм используется для всех типов ограничений, а не только для ссылок на себя.

Answer 2

Часть задания "LOG ERRORS INTO", как указывали другие пользователи, происходит после выполнения оператора (обновление и вставка части) при проверке ограничений. Таким образом, вы можете вставить ошибки до завершения проверки ограничений. Вот почему вы видите ошибку, вставленную до полного завершения оператора.

И как ответ на это наблюдение:

Я могу вставить эту запись из таблицы ОШИБКА в основную таблицу, не вызывая уникальный сбой ограничения. Поэтому мне интересно, почему ошибка вошла в первую очередь.

Убедитесь, что у вас есть вся информация в одном операторе слияния. если вы не обновите значение в том же операторе, но в другом, который происходит между неудачной вставкой и повторной попыткой, все объяснимо.

(Я имею в виду, что записи в разделе USING не совпадают.

• Сессия 1: объединение с использованием select 4 as id, 'b' as name from dual(ошибка заносится в журнал)
• Сессия 2: объединение с использованием select 2 as id, null as name from dual совершить хорошо
• Сессия 3: Вы повторяете вставку, и она работает

)

Если вы можете воспроизвести ошибку одним утверждением, это будет проблемой. Но у вас есть много сеансов в вашей среде. Пожалуйста, проверьте источник ваших заявлений о слиянии. У вас могут быть опоздания или что-то в этом роде.