CROSS APPLY produz junção externa

Resumo

O SQL Server usa a junção correta (interna ou externa) e adiciona projeções quando necessário para honrar toda a semântica da consulta original ao realizar traduções internas entre apply e join .

As diferenças nos planos podem ser explicadas pelas diferentes semânticas de agregações com e sem uma cláusula group by no SQL Server.

Detalhes

Junte-se vs Aplicar

Precisamos ser capazes de distinguir entre uma aplicação e uma junção :

• Aplicar

  A entrada interna (inferior) do apply é executada para cada linha da entrada externa (superior), com um ou mais valores de parâmetro do lado interno fornecidos pela linha externa atual. O resultado geral da aplicação é a combinação (união de todos) de todas as linhas produzidas pelas execuções do lado interno parametrizadas. A presença de parâmetros significa aplicar às vezes é chamada de junção correlacionada.

  Uma aplicação é sempre implementada nos planos de execução pelo operador Nested Loops . O operador terá uma propriedade Outer References em vez de juntar predicados. As referências externas são os parâmetros passados ​​do lado externo para o lado interno em cada iteração do loop.

• Juntar

  Uma junção avalia seu predicado de junção no operador de junção. A junção geralmente pode ser implementada pelos operadores Hash Match , Merge ou Nested Loops no SQL Server.

  Quando o Nested Loops é escolhido, ele pode ser diferenciado de um apply pela falta de Referências Externas (e geralmente pela presença de um predicado de junção). A entrada interna de uma junção nunca faz referência a valores da entrada externa - o lado interno ainda é executado uma vez para cada linha externa, mas as execuções do lado interno não dependem de nenhum valor da linha externa atual.

Para mais detalhes veja meu post Apply versus Nested Loops Join .

...por que há uma junção externa no plano de execução em vez de uma junção interna ?

A junção externa surge quando o otimizador transforma uma aplicação em uma junção (usando uma regra chamada ApplyHandler) para ver se ele pode encontrar um plano baseado em junção mais barato. A junção deve ser uma junção externa para correção quando a aplicação contém uma agregação escalar . Uma junção interna não seria garantida para produzir os mesmos resultados que a aplicação original , como veremos.

Agregados escalares e vetoriais

• Um agregado sem uma GROUP BYcláusula correspondente é um agregado escalar .
• Um agregado com uma GROUP BYcláusula correspondente é um agregado vetorial .

No SQL Server, uma agregação escalar sempre produzirá uma linha, mesmo que não receba nenhuma linha para agregar. Por exemplo, a agregação escalar COUNTde nenhuma linha é zero. Um agregado vetorial COUNT sem linhas é o conjunto vazio (sem linhas).

As seguintes perguntas de brinquedo ilustram a diferença. Você também pode ler mais sobre agregados escalares e vetoriais em meu artigo Fun with Scalar and Vector Aggregates .

-- Produces a single zero value
SELECT COUNT_BIG(*) FROM #MyTable AS MT WHERE 0 = 1;

-- Produces no rows
SELECT COUNT_BIG(*) FROM #MyTable AS MT WHERE 0 = 1 GROUP BY ();

db<>demonstração de violino

Transformando aplicar para ingressar

Mencionei antes que a junção deve ser uma junção externa para correção quando a aplicação original contém um agregado escalar . Para mostrar em detalhes por que esse é o caso, usarei um exemplo simplificado da consulta de perguntas:

DECLARE @A table (A integer NULL, B integer NULL);
DECLARE @B table (A integer NULL, B integer NULL);

INSERT @A (A, B) VALUES (1, 1);
INSERT @B (A, B) VALUES (2, 2);

SELECT * FROM @A AS A
CROSS APPLY (SELECT c = COUNT_BIG(*) FROM @B AS B WHERE B.A = A.A) AS CA;

O resultado correto para column cé zero , porque COUNT_BIGé um agregado escalar . Ao traduzir essa consulta de aplicação para o formulário de junção, o SQL Server gera uma alternativa interna que seria semelhante à seguinte se fosse expressa em T-SQL:

SELECT A.*, c = COALESCE(J1.c, 0)
FROM @A AS A
LEFT JOIN
(
    SELECT B.A, c = COUNT_BIG(*) 
    FROM @B AS B
    GROUP BY B.A
) AS J1
    ON J1.A = A.A;

Para reescrever o apply como uma junção não correlacionada, temos que introduzir umGROUP BY na tabela derivada (caso contrário, não poderia haver nenhuma Acoluna para a junção). A junção deve ser uma junção externa para que cada linha da tabela @Acontinue a produzir uma linha na saída. A junção esquerda produzirá uma NULLcoluna for cquando o predicado de junção não for avaliado como verdadeiro. Isso NULLprecisa ser convertido em zero COALESCEpara concluir uma transformação correta de apply .

A demonstração abaixo mostra como a junção externa e COALESCEsão necessárias para produzir os mesmos resultados usando a junção como a consulta de aplicação original:

db<>demonstração de violino

Com oGROUP BY

...por que descomentar a cláusula group by resulta em uma junção interna?

Continuando o exemplo simplificado, mas adicionando um GROUP BY:

DECLARE @A table (A integer NULL, B integer NULL);
DECLARE @B table (A integer NULL, B integer NULL);

INSERT @A (A, B) VALUES (1, 1);
INSERT @B (A, B) VALUES (2, 2);

-- Original
SELECT * FROM @A AS A
CROSS APPLY 
(SELECT c = COUNT_BIG(*) FROM @B AS B WHERE B.A = A.A GROUP BY B.A) AS CA;

O COUNT_BIGagora é um agregado vetorial , portanto, o resultado correto para um conjunto de entrada vazio não é mais zero, não é nenhuma linha . Em outras palavras, executar as instruções acima não produz nenhuma saída.

Essas semânticas são muito mais fáceis de respeitar ao traduzir de apply para join , já que CROSS APPLYnaturalmente rejeita qualquer linha externa que não gere linhas laterais internas. Podemos, portanto, usar com segurança uma junção interna agora, sem projeção de expressão extra:

-- Rewrite
SELECT A.*, J1.c 
FROM @A AS A
JOIN
(
    SELECT B.A, c = COUNT_BIG(*) 
    FROM @B AS B
    GROUP BY B.A
) AS J1
    ON J1.A = A.A;

A demonstração abaixo mostra que a reescrita da junção interna produz os mesmos resultados que a aplicação original com agregado vetorial:

db<>demonstração de violino

O otimizador escolhe uma junção interna de mesclagem com a tabela pequena porque encontra uma plano de junção barato rapidamente (plano bom o suficiente encontrado). O otimizador baseado em custo pode reescrever a junção de volta para uma aplicação - talvez encontrando um plano de aplicação mais barato, como acontecerá aqui se uma junção de loop ou uma dica de busca forçada for usada - mas não vale a pena o esforço neste caso.

Notas

Os exemplos simplificados usam tabelas diferentes com conteúdos diferentes para mostrar as diferenças semânticas com mais clareza.

Pode-se argumentar que o otimizador deve ser capaz de raciocinar sobre uma auto-junção não ser capaz de gerar nenhuma linha incompatível (não-junção), mas não contém essa lógica hoje. Acessar a mesma tabela várias vezes em uma consulta não garante a produção dos mesmos resultados em geral, dependendo do nível de isolamento e da atividade simultânea.

O otimizador se preocupa com essas semânticas e casos extremos para que você não precise.

Bônus: Plano de Aplicação Interna

O SQL Server pode produzir um plano de aplicação interna (não um plano de associação interna !) para a consulta de exemplo, mas opta por não fazê-lo por motivos de custo. O custo do plano de junção externa mostrado na pergunta é de 0,02898 unidades na instância do SQL Server 2017 do meu laptop.

Você pode forçar um plano de aplicação (junção correlacionada) usando o sinalizador de rastreamento não documentado e sem suporte 9114 (que desativa ApplyHandleretc.) apenas para ilustração:

SELECT      *
FROM        #MyTable AS mt
CROSS APPLY 
(
    SELECT COUNT_BIG(DISTINCT mt2.Col_B) AS dc
    FROM   #MyTable AS mt2
    WHERE  mt2.Col_A = mt.Col_A 
    --GROUP BY mt2.Col_A
) AS ca
OPTION (QUERYTRACEON 9114);

Isso produz um plano de loops aninhados de aplicação com um spool de índice lento. O custo total estimado é de 0,0463983 (superior ao plano selecionado):

Observe que o plano de execução usando loops aninhados de aplicação produz resultados corretos usando a semântica de "junção interna" independentemente da presença da GROUP BYcláusula.

No mundo real, normalmente teríamos um índice para dar suporte a uma busca no lado interno da aplicação para incentivar o SQL Server a escolher essa opção naturalmente, por exemplo:

CREATE INDEX i ON #MyTable (Col_A, Col_B);

db<>demonstração de violino

Cross Apply é uma operação lógica nos dados. Ao decidir como obter esses dados, o SQL Server escolhe o operador físico apropriado para obter os dados desejados.

Não há um operador de aplicação físico e o SQL Server o converte no operador de junção apropriado e, esperançosamente, eficiente.

Você pode encontrar uma lista dos operadores físicos no link abaixo.

https://learn.microsoft.com/en-us/sql/relational-databases/showplan-logical-and-physical-operators-reference?view=sql-server-2017

O otimizador de consulta cria um plano de consulta como uma árvore que consiste em operadores lógicos. Depois que o otimizador de consulta cria o plano, o otimizador de consulta escolhe o operador físico mais eficiente para cada operador lógico. O otimizador de consulta usa uma abordagem baseada em custo para determinar qual operador físico implementará um operador lógico.

Normalmente, uma operação lógica pode ser implementada por vários operadores físicos. No entanto, em casos raros, um operador físico também pode implementar várias operações lógicas.

edit/ Parece que entendi errado sua pergunta. O servidor SQL normalmente escolherá o operador mais apropriado. Sua consulta não precisa retornar valores para todas as combinações de ambas as tabelas, quando uma junção cruzada seria usada. Basta calcular o valor que você deseja para cada linha, o que é feito aqui.