Oracle - UPSERT с обновлением, не выполненным для неизмененных значений

На данный момент я использую следующее обновление или вставку инструкции Oracle:

BEGIN
  UPDATE DSMS
     SET SURNAME = :SURNAME
   WHERE DSM = :DSM;
  IF (SQL%ROWCOUNT = 0) THEN
    INSERT INTO DSMS
      (DSM, SURNAME)
    VALUES
      (:DSM, :SURNAME);
  END IF;
END;

Это работает нормально, за исключением того, что оператор обновления выполняет фиктивное обновление, если данные совпадают с предоставленными значениями параметров. Я бы не возражал против фиктивного обновления в обычной ситуации, но над этой таблицей построена система репликации/синхронизации, использующая триггеры в таблицах для захвата обновленных записей, и частое выполнение этого оператора для многих записей просто означает, что я вызову огромный трафик в триггерах и система синхронизации.

Есть ли какой-нибудь простой способ переформулировать этот код, чтобы оператор обновления не обновлял запись, если в этом нет необходимости, без использования следующего кода проверки IF-EXISTS, который я считаю недостаточно гладким и, возможно, также не самым эффективным для этой задачи?

DECLARE
  CNT NUMBER;
BEGIN
  SELECT COUNT(1) INTO CNT FROM DSMS WHERE DSM = :DSM;
  IF SQL%FOUND THEN
    UPDATE DSMS
       SET SURNAME = :SURNAME
     WHERE DSM = :DSM
       AND SURNAME != :SURNAME;
  ELSE
    INSERT INTO DSMS
      (DSM, SURNAME)
    VALUES
      (:DSM, :SURNAME);
  END IF;
END;

Я также пытался использовать оператор MERGE INTO, но он не работает для обновлений, когда значение не изменяется (обновление ничего не изменяет, и вставка выполняется, но происходит нарушение PK).

Полный образец MERGE INTO:

CREATE TABLE DSMS(
  dsm VARCHAR2(10) NOT NULL PRIMARY KEY,
  surname VARCHAR2(10) NOT NULL
);
> Table created

-- :DSM = 'xx', :SURNAME = 'xx'
MERGE INTO DSMS D
USING (SELECT :DSM       AS DSM,
              :SURNAME   AS SURNAME
         FROM DUAL) V
ON (D.DSM = V.DSM)
WHEN MATCHED THEN
  UPDATE
     SET SURNAME = V.SURNAME
   WHERE D.SURNAME <> V.SURNAME
WHEN NOT MATCHED THEN
  INSERT (DSM, SURNAME)
  VALUES (V.DSM, V.SURNAME);

> Ok - record inserted

-- :DSM = 'xx', :SURNAME = 'xx'
MERGE INTO DSMS D
USING (SELECT :DSM       AS DSM,
              :SURNAME   AS SURNAME
         FROM DUAL) V
ON (D.DSM = V.DSM)
WHEN MATCHED THEN
  UPDATE
     SET SURNAME = V.SURNAME
   WHERE D.SURNAME <> V.SURNAME
WHEN NOT MATCHED THEN
  INSERT (DSM, SURNAME)
  VALUES (V.DSM, V.SURNAME);

> ORA-00001 - Unique constraint violated (PK violation)

Похоже, что Oracle использует UPDATE...IF SQL%ROWCOUNT=0 THEN INSERT... внутри предложения MERGE INTO? Второй оператор MERGE INTO терпит неудачу, потому что обновление ничего не обновляет, поэтому выполняется INSERT, что приводит к нарушению PK, поскольку строка уже существует, просто значения не изменились.


person Buthrakaur    schedule 26.05.2010    source источник
comment
после замены DSMS на TestMerge в вашем примере оба утверждения сработали для меня нормально.   -  person Quassnoi    schedule 26.05.2010
comment
@Quassnoi: я нашел некоторую информацию об ошибках в реализациях Oracle MERGE INTO в некоторых версиях Oracle DB, поэтому я думаю, что могу столкнуться с некоторой ошибкой, потому что я действительно получаю нарушение PK при выполнении команды слияния во второй раз. В какой версии OraDB вы успешно запустили образец? Я на 10.2.0.1.0...   -  person Buthrakaur    schedule 27.05.2010
comment
У меня тоже версия 10.2.0.1.0. Образец таблицы, который вы создали, называется TestMerge, но в ваших MERGE положениях вы используете таблицу DSMS: MERGE INTO DSMS D ….   -  person Quassnoi    schedule 27.05.2010
comment
@Quassnoi: TestMerge была опечаткой - вчера я уже редактировал сообщение. Я попробую еще раз - я не понимаю разницы, когда мы используем одну и ту же версию Ora. Если только у вас не установлены какие-то патчи, которых у меня нет.   -  person Buthrakaur    schedule 27.05.2010
comment
@Quassnoi: хм... теперь он действительно работает без проблем - должно быть, вчера я сделал что-то не так.   -  person Buthrakaur    schedule 27.05.2010


Ответы (2)


MERGE
INTO    dsms d
USING   (
        SELECT  :DSM AS dsm, :SURNAME AS surname, :FIRSTNAME AS firstname, :VALID AS valud
        FROM    dual
        ) v
ON      (d.dsm = q.dsm)
WHEN MATCHED THEN
UPDATE
SET     SURNAME = v.SURNAME, FIRSTNAME = v.FIRSTNAME, VALID = v.VALID
WHERE   d.surname <> v.surname
        OR d.firstname <> v.firstname
        OR d.valid <> v.valid
WHEN NOT MATCHED THEN
INSERT
INTO    (SURNAME, FIRSTNAME, VALID)
VALUES  (SURNAME, FIRSTNAME, VALID)

Возможно, вам придется добавить дополнительные NULL проверки, если ваши поля принимают NULL значений.

person Quassnoi    schedule 26.05.2010
comment
Спасибо. К сожалению, это не работает, когда строка уже существует, но значения одинаковы - вместо этого срабатывает вставка и возникает исключение нарушения PK. Я попытался добавить WHERE D.SURNAME ‹› V.SURNAME... в предложение INTO, но получил ORA-38102 Неверный столбец в INSERT WHERE. - person Buthrakaur; 26.05.2010
comment
@But: конечно, моя вина. Пожалуйста, попробуйте сейчас. - person Quassnoi; 26.05.2010
comment
Поле DSM является первичным ключом, поэтому вставка невозможна, поскольку DSM не может быть нулевым. Вы пропустили поле DSM в измененном коде... - person Buthrakaur; 26.05.2010
comment
@Но: если запись уже существует, то UPDATE следует запускать, а не вставлять. Я думал, что DSM заполняется последовательностью в триггере. Не могли бы вы опубликовать образец записи и значения, которые ведут себя не так, как вы ожидаете при слиянии? - person Quassnoi; 26.05.2010
comment
Я только что добавил пример кода MERGE INTO, который имитирует сбой. - person Buthrakaur; 26.05.2010

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

BEGIN
  INSERT INTO DSMS
      (DSM, SURNAME)
  VALUES
      (:DSM, :SURNAME);
EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
    UPDATE DSMS
       SET SURNAME = :SURNAME
     WHERE DSM = :DSM
       AND SURNAME != :SURNAME;
END;
person Gary Myers    schedule 26.05.2010
comment
вы правы - хорошая идея ... хотя большинство операторов заканчиваются выполнением UPDATE без изменения какой-либо строки (значения не изменятся) в моем сценарии, поэтому я думаю, что это может быть неоптимальным решением. Я попробую и посмотрю. Спасибо. - person Buthrakaur; 27.05.2010