Oracle SQL MERGE

Команда MERGE появилась в версии 9 СУБД Oracle и была усовершенствована в версии 10. Команда не делает ничего такого, чего нельзя сделать с помощью традиционных UPDATE, INSERT (и DELETE), но в ряде случаев делает это эффективнее.

Синтаксис команды MERGE:

MERGE INTO target_table
    USING source_table | source_view | source_subquery
    ON condition
[WHEN MATCHED THEN
    UPDATE SET ... [where_clause]
    [DELETE where_clause]]
[WHEN NOT MATCHED THEN
    INSERT ... [where_clause]]

Команда изменяет таблицу target_table, используя в качестве источника данных таблицу, вью или подзапрос из части USING. Часть ON condition сопоставляет строки источника со строками целевой таблицы. При наличии в target_table строк, соответствующих источнику, выполняется часть WHEN MATCHED THEN UPDATE, иначе - часть WHEN NOT MATCHED THEN INSERT. Иными словами, строки, соответствующие источнику, модифицируются, а отсутствующие - добавляются.

Опциональные where_clause в частях UPDATE и INSERT ограничивают модифицируемые и добавляемые строки. Опция DELETE позволяет удалять строки, модифицированные в части UPDATE.

Что делает и чего не делает MERGE

Чтобы понять, как работает MERGE, поэкспериментируем. Вначале я создам таблицу - источник данных:

SQL> CREATE TABLE books (
         book_id NUMBER(9) PRIMARY KEY,
         name VARCHAR2(50) NOT NULL
     );
Table created

SQL> INSERT INTO books VALUES (1, 'Книга рекордов Гиннеса');
1 row inserted

SQL> INSERT INTO books VALUES (2, 'Слово о полку Игореве');
1 row inserted

Теперь создам таблицу books_copy, назначение которой - содержать точную копию данных из таблицы books:

SQL> CREATE TABLE books_copy AS SELECT * FROM books;
Table created

SQL> ALTER TABLE books_copy ADD PRIMARY KEY (book_id);
Table altered

SQL> SELECT * FROM books_copy;

   BOOK_ID NAME
---------- --------------------------------------------------
         1 Книга рекордов Гиннеса
         2 Слово о полку Игореве

Сделаю изменения в таблице-источнике, удалив, изменив и добавив по одной строке. После этого таблица books_copy уже не содержит актуальной копии и нуждается в обновлении:

SQL> DELETE FROM books WHERE book_id = 1; -- Книга рекордов Гиннеса
1 row deleted

SQL> UPDATE books SET name = 'Повесть временных лет' WHERE book_id = 2;
1 row updated

SQL> INSERT INTO books VALUES (3, 'Русские народные сказки');
1 row inserted

SQL> COMMIT;
Commit complete

SQL> SELECT * FROM books;

   BOOK_ID NAME
---------- --------------------------------------------------
         2 Повесть временных лет
         3 Русские народные сказки

SQL> SELECT * FROM books_copy;

   BOOK_ID NAME
---------- --------------------------------------------------
         1 Книга рекордов Гиннеса
         2 Слово о полку Игореве

Теперь для того, чтобы привести books_copy в соответствие с books необходимо:

1. модифицировать строки books_copy, такие, что books_copy.book_id = books.book_id
2. добавить в books_copy строки из books c books_id, отсутствующими в books_copy
3. удалить из books_copy строки c books_id, отсутствующими в books

Команда MERGE модифицирует и добавит строки в books_copy (пп. 1 и 2):

SQL> MERGE INTO books_copy tgt
         USING books src
         ON (tgt.book_id = src.book_id)
     WHEN MATCHED THEN
         UPDATE SET tgt.name = src.name
     WHEN NOT MATCHED THEN
         INSERT VALUES (src.book_id, src.name)
     ;
2 rows merged

Oracle сообщает о двух обработанных строках: одна из них модифицирована, другая - добавлена. Результат:

SQL> SELECT * FROM books_copy;

   BOOK_ID NAME
---------- --------------------------------------------------
         1 Книга рекордов Гиннеса
         2 Повесть временных лет
         3 Русские народные сказки

Однако, MERGE не может удалить из целевой таблицы строки, которые отсутствуют в источнике (п.3)!

Сделаю это отдельной командой DELETE и проверю, что теперь содержимое таблиц одинаково:

SQL> DELETE FROM books_copy WHERE book_id NOT IN (SELECT book_id FROM books);
1 row deleted

SQL> SELECT * FROM books;

   BOOK_ID NAME
---------- --------------------------------------------------
         2 Повесть временных лет
         3 Русские народные сказки

SQL> SELECT * FROM books_copy;

   BOOK_ID NAME
---------- --------------------------------------------------
         2 Повесть временных лет
         3 Русские народные сказки

Что происходит "за кулисами"

Оказывается, для того, чтобы получить исходные данные для операций UPDATE и INSERT, команда MERGE выполняет внешнее соединение источника и целевой таблицы: source LEFT OUTER JOIN target. Результатом являются

1. строки, которые есть и в источнике и в приемнике, и
2. строки, которые есть только в источнике, но отсутствуют в приемнике.

Проиллюстрирую это:

SQL> ROLLBACK;
Rollback complete

SQL> SELECT src.*, tgt.*
     FROM books src LEFT OUTER JOIN books_copy tgt
         ON (src.book_id = tgt.book_id);

   BOOK_ID NAME                              BOOK_ID NAME
---------- ------------------------------ ---------- ------------------------------
         2 Повесть временных лет                   2 Слово о полку Игореве
         3 Русские народные сказки                                       

А для того, чтобы в одном запросе выбрать вставляемые, модифицируемые, а также удаляемые строки, нужно воспользоваться полным внешним соединением, source FULL OUTER JOIN target:

SQL> SELECT src.*, tgt.*
     FROM books src FULL OUTER JOIN books_copy tgt
         ON (src.book_id = tgt.book_id);

   BOOK_ID NAME                              BOOK_ID NAME
---------- ------------------------------ ---------- ------------------------------
                                                   1 Книга рекордов Гиннеса
         2 Повесть временных лет                   2 Слово о полку Игореве
         3 Русские народные сказки                                       

К сожалению, MERGE этого не делает (и потому называется MERGE, а не SYNC).

Не нужно модифицировать все строки

Модификация всех строк целевой таблицы, сопоставленных с источником, в общем случае не нужна и затратна. Однако, в рассмотренном примере происходит именно это. Если работать таблицей, содержащей миллионы строк, то СУБД будет делать много ненужной работы.

На самом деле, нужно модифицировать лишь те строки целевой таблицы, для которых соответствующие строки источника изменились со времени предыдущего выполнения команды MERGE. Пусть таблица books имеет столбец с датой последнего изменения строки:

SQL> ALTER TABLE books ADD (change_date DATE DEFAULT SYSDATE);
Table altered

SQL> UPDATE books SET change_date = change_date-2;
2 rows updated

SQL> SELECT * FROM books;

   BOOK_ID NAME                                     CHANGE_DATE
---------- ---------------------------------------- -----------
         2 Повесть временных лет                    06.12.2013
         3 Русские народные сказки                  06.12.2013

Итак, последние изменения в books сделаны 2 суток назад. Приведем в соответствие books_copy и books, после чего сделаем новые изменения в источнике:

SQL> DELETE FROM books_copy;
2 rows deleted

SQL> INSERT INTO books_copy SELECT book_id, name FROM books;
2 rows inserted

SQL> INSERT INTO books VALUES (4, 'Сон в красном тереме', SYSDATE);
1 row inserted

SQL> UPDATE books SET name = 'Сказки русских писателей', change_date = SYSDATE WHERE book_id = 3;
1 row updated

SQL> COMMIT;
Commit complete

SQL> SELECT * FROM books;

   BOOK_ID NAME                                     CHANGE_DATE
---------- ---------------------------------------- -----------
         4 Сон в красном тереме                     08.12.2013
         2 Повесть временных лет                    06.12.2013
         3 Сказки русских писателей                 08.12.2013

Обновим books_copy, модифицируя только строки, источник которых был изменен в течение последних суток:

SQL> MERGE INTO books_copy tgt
  2      USING books src
  3      ON (tgt.book_id = src.book_id)
  4  WHEN MATCHED THEN
  5      UPDATE SET tgt.name = src.name
  6      WHERE src.change_date > SYSDATE-1
  7  WHEN NOT MATCHED THEN
  8      INSERT VALUES (src.book_id, src.name)
  9  ;
2 rows merged

SQL> SELECT * FROM books_copy;

   BOOK_ID NAME
---------- --------------------------------------------------
         2 Повесть временных лет
         3 Сказки русских писателей
         4 Сон в красном тереме

А можно ли добиться аналогичного результата иначе, изменив условие ON? Оказывается, нельзя:

SQL> ROLLBACK;
Rollback complete

SQL> MERGE INTO books_copy tgt
         USING books src
         ON (tgt.book_id = src.book_id AND src.change_date > SYSDATE-1)
     WHEN MATCHED THEN
         UPDATE SET tgt.name = src.name
     WHEN NOT MATCHED THEN
         INSERT VALUES (src.book_id, src.name)
     ;
...
ORA-00001: unique constraint (AY.SYS_C0062249) violated

Проверка условия ON определяет, какая из операций, UPDATE или INSERT, будет выполнена. И в данном случае часть WHEN NOT MATCHED THEN INSERT пытается добавить в books_copy все записи из books, которые не соответствуют условию ON! Что приводит к нарушению первичного ключа.

Вывод: не нужно включать в условие ON иную логику, кроме логики сопоставления строк целевой таблицы и источника.

Иногда полезно модифицировать все строки?

Опция DELETE в MERGE работает только для строк, модифицированных в части UPDATE. Я не нашел готового ответа на вопрос, в каких случаях полезна опция DELETE, но представил себе следующиую ситуацию.

Иногда из целевой таблицы нужно удалять строки по прошествии времени или по наступлении события. Чтобы команда MERGE смогла удалять строки, ставшие ненужными, все сопоставленные с источником строки должны модифицироваться частью UPDATE.

Потребуем, чтобы таблица books_copy отражала изменения в источнике только за вчерашний и сегодняшний день. Тогда строки, которые были изменены в источнике ранее, чем вчерашняя полночь, должны удаляться из books_copy.

Что мы имеем на данный момент?

SQL> SELECT * FROM books;

   BOOK_ID NAME                                     CHANGE_DATE
---------- ---------------------------------------- -----------
         4 Сон в красном тереме                     08.12.2013
         2 Повесть временных лет                    06.12.2013
         3 Сказки русских писателей                 08.12.2013

SQL> SELECT * FROM books_copy;

   BOOK_ID NAME
---------- ----------------------------------------
         2 Повесть временных лет
         3 Русские народные сказки

Согласно предложенной выше логике, после выполнения команды MERGE с DELETE в books_copy должны произойти следующие изменения:

1. будет модифицирована строка с book_id = 3,
2. будет (модифицирована и) удалена строка с book_id = 2, поскольку соответствующая строка источника была обновлена ранее вчерашней полночи,
3. будет добавлена строка с book_id = 4.

SQL> MERGE INTO books_copy tgt
         USING books src
         ON (tgt.book_id = src.book_id)
     WHEN MATCHED THEN
         UPDATE SET tgt.name = src.name
         DELETE WHERE src.change_date < TRUNC(SYSDATE-1)
     WHEN NOT MATCHED THEN
         INSERT VALUES (src.book_id, src.name)
         WHERE src.change_date >= TRUNC(SYSDATE-1)
     ;
 
3 rows merged

SQL> SELECT * FROM books_copy;
   BOOK_ID NAME
---------- --------------------------------------------------
         3 Сказки русских писателей
         4 Сон в красном тереме

Работает!

В заключение, удаляю использованные в экспериментах таблицы:

SQL> DROP TABLE books;
Table dropped

SQL> DROP TABLE books_copy;
Table dropped

Пара замечаний

Для полноценной работы с командой MERGE пользователю нужны привилегии на INSERT, UPDATE и DELETE для target_table. Выполнение команды приводит к срабатыванию триггеров для INSERT, UPDATE и DELETE, если соответствующие части присутствуют в команде. В этом свете команда MERGE выглядит не как самостоятельная команда, а как синтаксический сахар, или shortcut, для компактной записи нескольких команд SQL.

Однако, потенциал команды MERGE - в ее гибкости и эффективности:

• она может работать как INSERT, как UPDATE, или комбинировать операции INSERT и UPDATE (и DELETE), в зависимости от используемых и опущенных опций.
• она извлекает данные из источника один раз, в отличие от синаксически эквивалентных ей двух команд UPDATE и INSERT с подзапросами.