Início / dba / Perguntas / 120233
Accepted
Juan Velez
Asked: 2015-11-06 10:34:57 +0800 CST

SQL Server - DELETE da subconsulta/tabela derivada

  • 772

Existe uma maneira de transformar a SELECTdeclaração abaixo em um DELETE?

Gostaria de excluir os registros correspondentes retornados da [ETL].[Stage_Claims]tabela.

Como usei tabelas derivadas, não posso fazer referência à Stage_Claimstabela.

Para resumir, as 2 tabelas físicas usadas na consulta abaixo possuem estruturas idênticas. A única diferença é DUPS_Claimsum subconjunto de Stage_Claims.

DUPS_Claimscontém registros duplicados encontrados em arquivos Stage_Claims. Se um registro existir 3 vezes em Stage_Claims, teremos esse registro 3 vezes DUPS_Claimstambém.

Stage_Claimscontém todos os registros, incluindo os registros duplicados em DUPS_Claims.

Gostaria de remover os registros duplicados Stage_Claimsdeixando apenas 1 registro exclusivo para cada registro duplicado.

Stage_Claimstem pouco menos de 1 milhão de linhas, então não quero usar Row_Number / Partition em toda a tabela, pois leva mais de 2 minutos para ser executado.

A consulta abaixo é executada em cerca de 15 segundos e identifica com êxito apenas os registros duplicados (sem incluir o registro exclusivo original que queremos manter), mas não consegui descobrir como excluir os registros retornados do SC.

É possível ou devo apenas adotar uma abordagem diferente?

SELECT *
FROM (
    SELECT RN = ROW_NUMBER() OVER (
            PARTITION BY SC.ID ORDER BY SC.id
            )
        ,SC.*
    FROM [ETL].[Stage_Claims] SC
    WHERE ID IN (
            SELECT ID
            FROM (
                SELECT RN = ROW_NUMBER() OVER (
                        PARTITION BY ID ORDER BY id
                        )
                    ,ID
                FROM [ETL].[DUPS_Claims]
                ) AS t1
            WHERE RN > 1
            )
    ) AS t2
WHERE RN > 1
sql-server

1 respostas

  1. Best Answer

    Converta sua instrução select em um CTE e DELETE FROMo CTE, como em:

    ;WITH del AS
(
SELECT *
FROM (
    SELECT RN = ROW_NUMBER() OVER (
            PARTITION BY SC.ID ORDER BY SC.id
            )
        ,SC.*
    FROM [ETL].[Stage_Claims] SC
    WHERE ID IN (
            SELECT ID
            FROM (
                SELECT RN = ROW_NUMBER() OVER (
                        PARTITION BY ID ORDER BY id
                        )
                    ,ID
                FROM [ETL].[DUPS_Claims]
                ) AS t1
            WHERE RN > 1
            )
    ) AS t2
WHERE RN > 1
)
DELETE FROM del;

    Aviso padrão: você deve testar isso em um ambiente de não produção.

    Você pode simplificar um pouco sua consulta e provavelmente obter melhor desempenho usando a consulta abaixo, que não faz uso da tabela intermediária DUPS_Claims, pois é absolutamente desnecessária:

    ;WITH cte AS
(
    SELECT sc.ID
        , rn = ROW_NUMBER() OVER (PARTITION BY sc.ID ORDER BY sc.ID)
    FROM ETL.Stage_Claims sc
)
DELETE
FROM cte 
WHERE rn > 1;

    Criei um índice não clusterizado e não exclusivo em ambas as tabelas e, em seguida, examinei os planos de execução para ambas as variações.

    A primeira variante:

    insira a descrição da imagem aqui

    A segunda variante:

    insira a descrição da imagem aqui

    A primeira variante verifica o índice duas vezes, enquanto a segunda variante claramente só precisa verificar o índice uma única vez e não requer uma junção de mesclagem relativamente cara em meu exemplo um tanto artificial. ETL.Stage_ClaimsMinha tabela de amostra contém 89 IDvalores exclusivos, cada um duplicado 89 vezes, para um total de 7.921 linhas.

    Caso os CTEs não sejam o seu forte, você pode usar esta abordagem para excluir de uma tabela derivada:

    DELETE c
FROM (
    SELECT sc.ID
        , rn = ROW_NUMBER() OVER (PARTITION BY sc.ID ORDER BY sc.ID)
    FROM ETL.Stage_Claims sc
) c
WHERE rn > 1;

    O plano de consulta acima DELETEda tabela derivada:

    insira a descrição da imagem aqui

    • 13

relate perguntas