Início / dba / Perguntas / 300125
Accepted
J.D.
Asked: 2021-09-25 08:36:54 +0800 CST

Por que adicionar uma cláusula WHERE interrompe minha consulta de trabalho, com o erro "O processador de consultas não pôde produzir um plano de consulta devido às dicas definidas ..."?

  • 772

Eu tenho a seguinte consulta (estupidamente simplificada) que aproveita dois CTEs que se referem à mesma tabela e os une:

WITH CTE1 AS
(
    SELECT dbo.RemoveNonNumericCharacters(PhoneNumber) AS PhoneNumberCleaned
    FROM PhoneNumbersTable
    GROUP BY dbo.RemoveNonNumericCharacters(PhoneNumber)
),
CTE2 AS
(
    SELECT CTE1.PhoneNumberCleaned
    FROM CTE1
    INNER HASH JOIN PhoneNumbersTable
        ON CTE1.PhoneNumbersCleaned = dbo.RemoveNonNumericCharacters(PhoneNumbersTable.PhoneNumber)
    WHERE PhoneNumbersTable.AreaCode IN (718, 212)
)

SELECT PhoneNumberCleaned
FROM CTE2

Observe o HASH JOINque acontece dentro CTE2. Isso tudo funciona bem e bem até agora.

Se eu adicionar a seguinte WHEREcláusula à consulta final SELECT, minha consulta inteira agora se torna:

WITH CTE1 AS
(
    SELECT dbo.RemoveNonNumericCharacters(PhoneNumber) AS PhoneNumberCleaned
    FROM PhoneNumbersTable
    GROUP BY dbo.RemoveNonNumericCharacters(PhoneNumber)
),
CTE2 AS
(
    SELECT CTE1.PhoneNumberCleaned
    FROM CTE1
    INNER HASH JOIN PhoneNumbersTable
        ON CTE1.PhoneNumbersCleaned = dbo.RemoveNonNumericCharacters(PhoneNumbersTable.PhoneNumber)
    WHERE PhoneNumbersTable.AreaCode IN (718, 212)
)

SELECT PhoneNumberCleaned
FROM CTE2
WHERE PhoneNumberCleaned = 'SomePhoneNumberInTheResultSet' -- E.g. 7183998888

Então recebo o erro clássico:

Msg 8622, Level 16, State 1, Line 50 O processador de consultas não pôde produzir um plano de consulta devido às dicas definidas nesta consulta. Reenvie a consulta sem especificar nenhuma dica e sem usar SET FORCEPLAN.

Isso só acontece se o valor que eu uso na minha WHEREcláusula realmente existir no conjunto de resultados. Se eu escolher qualquer valor que não exista, não recebo o erro acima.

Agora, obviamente, meu exemplo é meio estúpido para o que está acontecendo, e posso reescrevê-lo de algumas maneiras diferentes para provavelmente corrigi-lo, mas estou mais curioso para saber por que isso acontece? Se o SQL Server Engine é capaz de produzir um plano de consulta que retorna todos os registros, por que ele não consegue adicionar um operador de filtro adicional no final desse plano de consulta para o valor escalar que estou filtrando na minha WHEREcláusula?

Aqui está o código da caixa preta da minha funçãodbo.RemoveNonNumericCharacters (observe que não escrevi isso):

CREATE FUNCTION [dbo].[RemoveNonNumericCharacters] (@strText VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
    WHILE PATINDEX('%[^0-9]%', @strText) > 0
    BEGIN
        SET @strText = STUFF(@strText, PATINDEX('%[^0-9]%', @strText), 1, '')
    END
    RETURN @strText
END

Observe também que a coluna PhoneNumberno PhoneNumbersTableé do tipo VARCHAR(20).

sql-server sql-server-2016

1 respostas

  1. Best Answer

    A pergunta não contém um script de reprodução, mas esse erro geralmente ocorre porque um predicado implícito torna o predicado de junção redundante.

    Em outras palavras, uma implicação lógica em sua especificação de consulta transforma a junção interna em um produto cruzado lógico (após a simplificação). Isso não é necessariamente uma coisa ruim (como as pessoas tendem a supor que os produtos cruzados são), significa apenas que a especificação da consulta pode ser simplificada dessa maneira.

    Uma junção de hash requer um predicado de igualdade. Sem um predicado adequado na árvore de consulta no momento em que o otimizador considera a implementação de junção, a compilação falha com um erro. Em um mundo ideal, talvez, o otimizador não simplificaria um predicado de junção necessário para satisfazer uma dica.

    Este é o meu palpite baseado no conteúdo textual da pergunta. Se você precisar de uma explicação mais detalhada, forneça uma reprodução completa e especifique o ambiente.

    Como uma questão secundária, e além das sugestões de Erik Darling, aqui está uma função escalar determinística somente de números adequada para o SQL Server 2016:

    CREATE FUNCTION dbo.RemoveNonNumericCharacters
    (@string nvarchar(4000))
RETURNS 
    nvarchar(4000)
WITH 
    SCHEMABINDING, 
    RETURNS NULL ON NULL INPUT
AS
BEGIN
RETURN
    ISNULL(
        CONVERT(nvarchar(4000),
        (
            SELECT This.ch AS [text()]
            FROM OPENJSON(N'[1' + REPLICATE(N',1', LEN(@string) - 1) + N']') AS J
            CROSS APPLY (SELECT 1 + CONVERT(integer, J.[Key])) AS V (v)
            OUTER APPLY (SELECT SUBSTRING(@string COLLATE Latin1_General_100_BIN2, V.v, 1)) AS This (ch)
            WHERE This.ch COLLATE Latin1_General_100_BIN2 LIKE N'[0123456789]'
            ORDER BY V.v
            FOR XML PATH (N'')
        )), 
        N'');
END;

    Essa pode não ser a última palavra em eficiência, mas é interessante. Você pode converter sua função para ser determinística usando CHARINDEX.

    Para SQL Server 2017 e posterior:

    CREATE FUNCTION dbo.RemoveNonNumericCharacters
    (@string nvarchar(4000))
RETURNS 
    nvarchar(4000)
WITH 
    SCHEMABINDING, 
    --INLINE = ON, /* for 2019 */
    RETURNS NULL ON NULL INPUT
AS
BEGIN
    RETURN
        REPLACE(
            TRANSLATE(@string COLLATE Latin1_General_100_BIN2, 
                TRANSLATE(@string COLLATE Latin1_General_100_BIN2, 
                    N'0123456789',
                    N'XXXXXXXXXX'),
                REPLICATE(N'X', LEN(@string))), 
            N'X', N'');
END;

    Ambos são triviais para converter em uma função com valor de tabela embutida.

    • 6

relate perguntas