Início / dba / Perguntas / 30633
Accepted
KutuluMike
Asked: 2012-12-18 13:17:56 +0800 CST

MERGE um subconjunto da tabela de destino

  • 772

Estou tentando usar uma MERGEinstrução para inserir ou excluir linhas de uma tabela, mas só quero agir em um subconjunto dessas linhas. A documentação para MERGEtem um aviso muito forte:

É importante especificar apenas as colunas da tabela de destino que são usadas para fins de correspondência. Ou seja, especifique as colunas da tabela de destino que são comparadas com a coluna correspondente da tabela de origem. Não tente melhorar o desempenho da consulta filtrando linhas na tabela de destino na cláusula ON, como especificando AND NOT target_table.column_x = value. Fazer isso pode retornar resultados inesperados e incorretos.

mas é exatamente isso que parece que tenho que fazer para fazer meu MERGEtrabalho.

Os dados que tenho são uma tabela padrão de junção de muitos para muitos de itens para categorias (por exemplo, quais itens estão incluídos em quais categorias) assim:

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

O que preciso fazer é substituir efetivamente todas as linhas de uma categoria específica por uma nova lista de itens. Minha tentativa inicial de fazer isso se parece com isso:

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 ;

Isso parece estar funcionando em meus testes, mas estou fazendo exatamente o que o MSDN me avisa explicitamente para não fazer. Isso me deixa preocupado com a possibilidade de ter problemas inesperados mais tarde, mas não consigo ver nenhuma outra maneira de fazer minhas MERGElinhas afetar apenas com o valor de campo específico ( CategoryId = 2) e ignorar linhas de outras categorias.

Existe uma maneira "mais correta" de alcançar esse mesmo resultado? E quais são os "resultados inesperados ou incorretos" sobre os quais o MSDN está me alertando?

sql-server t-sql

1 respostas

  1. Best Answer

    A MERGEinstrução tem uma sintaxe complexa e uma implementação ainda mais complexa, mas essencialmente a ideia é unir duas tabelas, filtrar as linhas que precisam ser alteradas (inseridas, atualizadas ou excluídas) e, em seguida, realizar as alterações solicitadas. Dados os seguintes dados de exemplo:

    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);

    Alvo

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

    Fonte

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

    O resultado desejado é substituir os dados no destino pelos dados da origem, mas apenas para CategoryId = 2. Seguindo a descrição MERGEdada acima, devemos escrever uma consulta que una a origem e o destino apenas nas chaves e filtre as linhas apenas nas WHENcláusulas:

    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
;

    Isso dá os seguintes resultados:

    ╔═════════╦════════════╦════════╗
║ $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 ║
╚════════════╩════════╝

    O plano de execução é: Mesclar plano

    Observe que ambas as tabelas são digitalizadas completamente. Podemos pensar que isso é ineficiente, porque apenas as linhas CategoryId = 2serão afetadas na tabela de destino. É aqui que entram os avisos nos Manuais Online. Uma tentativa equivocada de otimizar para tocar apenas as linhas necessárias no destino é:

    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
;

    A lógica na ONcláusula é aplicada como parte da junção. Nesse caso, a associação é uma associação externa completa (consulte esta entrada dos Manuais Online para saber o motivo). A aplicação da verificação da categoria 2 nas linhas de destino como parte de uma junção externa resulta na exclusão de linhas com um valor diferente (porque elas não correspondem à origem):

    ╔═════════╦════════════╦════════╗
║ $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 ║
╚════════════╩════════╝

    A causa raiz é a mesma razão pela qual os predicados se comportam de maneira diferente em uma ONcláusula de junção externa do que se especificado na WHEREcláusula. A MERGEsintaxe (e a implementação de junção dependendo das cláusulas especificadas) apenas torna mais difícil ver que é assim.

    A orientação nos Manuais Online (expandida na entrada Otimizando Desempenho ) oferece orientação que garantirá que a semântica correta seja expressa usando MERGEsintaxe, sem que o usuário necessariamente tenha que entender todos os detalhes de implementação ou considerar as maneiras pelas quais o otimizador pode reorganizar legitimamente coisas por razões de eficiência de execução.

    A documentação oferece três maneiras possíveis de implementar a filtragem antecipada:

    Especificar uma condição de filtragem na WHENcláusula garante resultados corretos, mas pode significar que mais linhas são lidas e processadas das tabelas de origem e destino do que o estritamente necessário (como visto no primeiro exemplo).

    A atualização por meio de uma exibição que contém a condição de filtragem também garante resultados corretos (já que as linhas alteradas devem estar acessíveis para atualização por meio da exibição), mas isso requer uma exibição dedicada e uma que siga as condições ímpares para atualizar exibições.

    O uso de uma expressão de tabela comum traz riscos semelhantes aos da adição de predicados à ONcláusula, mas por motivos ligeiramente diferentes. Em muitos casos, será seguro, mas requer uma análise especializada do plano de execução para confirmar isso (e testes práticos extensivos). Por exemplo:

    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
;

    Isso produz resultados corretos (não repetidos) com um plano mais otimizado:

    Mesclar plano 2

    O plano lê apenas as linhas da categoria 2 da tabela de destino. Isso pode ser uma consideração importante de desempenho se a tabela de destino for grande, mas é muito fácil errar usando a MERGEsintaxe.

    Às vezes, é mais fácil escrever as MERGEoperações DML separadas. Essa abordagem pode até ter um desempenho melhor do que um single MERGE, fato que muitas vezes surpreende as pessoas.

    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;
    • 111

relate perguntas