MERGE - подмножество целевой таблицы

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

  

Важно указать только столбцы из целевой таблицы, которые используются для сопоставления. То есть, укажите столбцы из целевой таблицы, которые сравниваются с соответствующим столбцом исходной таблицы. Не пытайтесь улучшить производительность запросов, отфильтровывая строки в целевой таблице в предложении ON, например, указывая AND NOT target_table.column_x = значение. Это может привести к неожиданным и неправильным результатам.

, но это именно то, что мне кажется, что я должен сделать, чтобы сделать мой MERGE.

Данные, которые у меня есть, представляют собой стандартную таблицу со многими элементами ко многим элементам в категории (например, какие элементы включены в какие категории):

CategoryId   ItemId
==========   ======
1            1
1            2
1            3
2            1
2            3
3            5
3            6
4            5

Что мне нужно сделать, так это эффективно заменить все строки в определенной категории новым списком элементов. Моя первоначальная попытка сделать это выглядит так:

MERGE INTO CategoryItem AS TARGET
USING (
  SELECT ItemId FROM SomeExternalDataSource WHERE CategoryId = 2
) AS SOURCE
ON SOURCE.ItemId = TARGET.ItemId AND TARGET.CategoryId = 2
WHEN NOT MATCHED BY TARGET THEN
    INSERT ( CategoryId, ItemId )
    VALUES ( 2, ItemId )
WHEN NOT MATCHED BY SOURCE AND TARGET.CategoryId = 2 THEN
    DELETE ;

Этот появляется для работы в моих тестах, но я делаю именно то, что MSDN явно предупреждает меня не делать. Это заставляет меня беспокоиться о том, что позже я столкнусь с непредвиденными проблемами, но я не вижу другого способа заставить мой MERGE влиять только на строки с определенным значением поля (CategoryId = 2) и игнорировать строки из других категорий.

Есть ли более правильный способ достижения этого же результата? И каковы «неожиданные или неправильные результаты», о которых MSDN предупреждает меня?

59 голосов | спросил KutuluMike 18 TueEurope/Moscow2012-12-18T01:17:56+04:00Europe/Moscow12bEurope/MoscowTue, 18 Dec 2012 01:17:56 +0400 2012, 01:17:56

1 ответ


87

Оператор MERGE имеет сложный синтаксис и еще более сложную реализацию, но по существу идея состоит в объединении двух таблиц, фильтрации вниз до строк, которые необходимо изменить (вставить, обновить или удалить ), а затем выполнить запрошенные изменения. Учитывая следующие данные образца:

DECLARE @CategoryItem AS TABLE
(
    CategoryId  integer NOT NULL,
    ItemId      integer NOT NULL,

    PRIMARY KEY (CategoryId, ItemId),
    UNIQUE (ItemId, CategoryId)
);

DECLARE @DataSource AS TABLE
(
    CategoryId  integer NOT NULL,
    ItemId      integer NOT NULL

    PRIMARY KEY (CategoryId, ItemId)
);

INSERT @CategoryItem
    (CategoryId, ItemId)
VALUES
    (1, 1),
    (1, 2),
    (1, 3),
    (2, 1),
    (2, 3),
    (3, 5),
    (3, 6),
    (4, 5);

INSERT @DataSource
    (CategoryId, ItemId)
VALUES
    (2, 2);

Target

╔════════════╦════════╗
║ CategoryId ║ ItemId ║
╠════════════╬════════╣
║          1 ║      1 ║
║          2 ║      1 ║
║          1 ║      2 ║
║          1 ║      3 ║
║          2 ║      3 ║
║          3 ║      5 ║
║          4 ║      5 ║
║          3 ║      6 ║
╚════════════╩════════╝

Источник

╔════════════╦════════╗
║ CategoryId ║ ItemId ║
╠════════════╬════════╣
║          2 ║      2 ║
╚════════════╩════════╝

Желаемый результат - заменить данные в целевом объекте данными из источника, но только для CategoryId = 2. Следуя приведенному выше описанию MERGE, мы должны написать запрос, который объединяет источник и цель только с ключами и фильтрует строки только в предложениях WHEN:

MERGE INTO @CategoryItem AS TARGET
USING @DataSource AS SOURCE ON 
    SOURCE.ItemId = TARGET.ItemId 
    AND SOURCE.CategoryId = TARGET.CategoryId
WHEN NOT MATCHED BY SOURCE 
    AND TARGET.CategoryId = 2 
    THEN DELETE
WHEN NOT MATCHED BY TARGET 
    AND SOURCE.CategoryId = 2 
    THEN INSERT (CategoryId, ItemId)
        VALUES (CategoryId, ItemId)
OUTPUT 
    $ACTION, 
    ISNULL(INSERTED.CategoryId, DELETED.CategoryId) AS CategoryId,
    ISNULL(INSERTED.ItemId, DELETED.ItemId) AS ItemId
;

Это дает следующие результаты:

╔═════════╦════════════╦════════╗
║ $ACTION ║ CategoryId ║ ItemId ║
╠═════════╬════════════╬════════╣
║ DELETE  ║          2 ║      1 ║
║ INSERT  ║          2 ║      2 ║
║ DELETE  ║          2 ║      3 ║
╚═════════╩════════════╩════════╝
╔════════════╦════════╗
║ CategoryId ║ ItemId ║
╠════════════╬════════╣
║          1 ║      1 ║
║          1 ║      2 ║
║          1 ║      3 ║
║          2 ║      2 ║
║          3 ║      5 ║
║          3 ║      6 ║
║          4 ║      5 ║
╚════════════╩════════╝

План выполнения: План слияния

Обратите внимание, что обе таблицы полностью сканируются. Мы могли бы подумать, что это неэффективно, потому что в целевой таблице будут затронуты только строки, где CategoryId = 2. Здесь появляются предупреждения в Books Online. Одна ошибочная попытка оптимизировать для касания только необходимых строк в цели:

MERGE INTO @CategoryItem AS TARGET
USING 
(
    SELECT CategoryId, ItemId
    FROM @DataSource AS ds 
    WHERE CategoryId = 2
) AS SOURCE ON
    SOURCE.ItemId = TARGET.ItemId
    AND TARGET.CategoryId = 2
WHEN NOT MATCHED BY TARGET THEN
    INSERT (CategoryId, ItemId)
    VALUES (CategoryId, ItemId)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
OUTPUT 
    $ACTION, 
    ISNULL(INSERTED.CategoryId, DELETED.CategoryId) AS CategoryId,
    ISNULL(INSERTED.ItemId, DELETED.ItemId) AS ItemId
;

Логика в ON применяется как часть соединения. В этом случае объединение является полным внешним соединением (см. эта запись в Интернете для ). Применение проверки для категории 2 в целевых строках как части внешнего соединения в конечном итоге приводит к строкам с другим удаляемым значением (поскольку они не соответствуютисточник):

╔═════════╦════════════╦════════╗
║ $ACTION ║ CategoryId ║ ItemId ║
╠═════════╬════════════╬════════╣
║ DELETE  ║          1 ║      1 ║
║ DELETE  ║          1 ║      2 ║
║ DELETE  ║          1 ║      3 ║
║ DELETE  ║          2 ║      1 ║
║ INSERT  ║          2 ║      2 ║
║ DELETE  ║          2 ║      3 ║
║ DELETE  ║          3 ║      5 ║
║ DELETE  ║          3 ║      6 ║
║ DELETE  ║          4 ║      5 ║
╚═════════╩════════════╩════════╝

╔════════════╦════════╗
║ CategoryId ║ ItemId ║
╠════════════╬════════╣
║          2 ║      2 ║
╚════════════╩════════╝

Основная причина - та же самая причина, по которой предикаты ведут себя по-разному во внешнем соединении ON, чем они делают, если указано в предложении WHERE. Синтаксис MERGE (и реализация объединения в зависимости от указанных клауз) просто затрудняет понимание этого.

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

Документация предлагает три возможных способа ранней фильтрации:

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

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

Использование общего выражения таблицы несет схожие риски с добавлением предикатов в предложение ON, но по нескольким причинам. Во многих случаях это будет безопасно, но для этого требуется экспертный анализ плана выполнения (и обширное практическое тестирование). Например:

WITH TARGET AS 
(
    SELECT * 
    FROM @CategoryItem
    WHERE CategoryId = 2
)
MERGE INTO TARGET
USING 
(
    SELECT CategoryId, ItemId
    FROM @DataSource
    WHERE CategoryId = 2
) AS SOURCE ON
    SOURCE.ItemId = TARGET.ItemId
    AND SOURCE.CategoryId = TARGET.CategoryId
WHEN NOT MATCHED BY TARGET THEN
    INSERT (CategoryId, ItemId)
    VALUES (CategoryId, ItemId)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
OUTPUT 
    $ACTION, 
    ISNULL(INSERTED.CategoryId, DELETED.CategoryId) AS CategoryId,
    ISNULL(INSERTED.ItemId, DELETED.ItemId) AS ItemId
;

Это дает правильные результаты (не повторяющиеся) с более оптимальным планом:

Merge plan 2

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

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

DELETE ci
FROM @CategoryItem AS ci
WHERE ci.CategoryId = 2
AND NOT EXISTS 
(
    SELECT 1 
    FROM @DataSource AS ds 
    WHERE 
        ds.ItemId = ci.ItemId
        AND ds.CategoryId = ci.CategoryId
);

INSERT @CategoryItem
SELECT 
    ds.CategoryId, 
    ds.ItemId
FROM @DataSource AS ds
WHERE
    ds.CategoryId = 2;
ответил Paul White 18 TueEurope/Moscow2012-12-18T13:20:44+04:00Europe/Moscow12bEurope/MoscowTue, 18 Dec 2012 13:20:44 +0400 2012, 13:20:44

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132