Início / dba / Perguntas / 193238
Accepted
jericzech
Asked: 2017-12-16 07:58:05 +0800 CST

Misteriosas escolhas de índice de cláusula condicional where

  • 772

Você tem uma explicação, por que o otimizador de consulta está escolhendo diferentes índices e modos neste exemplo?

/* crete objects and data for testing */

-- table
CREATE TABLE #Test (
    ID INT IDENTITY PRIMARY KEY
    ,CustNo INT NULL
    ,CustNo2 INT NULL
    );

-- populate with data
WITH
  L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
  L1   AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
  L2   AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
  L3   AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
  L4   AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
  L5   AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
  Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
INSERT INTO #Test (CustNo)
SELECT TOP (1000) n FROM Nums ORDER BY n;

-- index on CustNo
CREATE NONCLUSTERED INDEX IX__#Test__CustNo ON #Test (CustNo ASC);

/* running test */

-- variables
DECLARE @Step INT = 1;
DECLARE @FindNo INT = 5;

-- #1 - uses CX index scan
SELECT CustNo
FROM #Test
WHERE (@Step = 1 AND CustNo = @FindNo)
    OR (@Step = 2 AND CustNo2 = @FindNo);

-- #2 - uses NCX index seek
SELECT CustNo
FROM #Test
WHERE (@Step = 1 AND CustNo = @FindNo)
    OR (@Step = 2 AND CustNo2 = @FindNo)
OPTION (RECOMPILE);

-- #3 - uses NCX index seek
IF @Step = 1
    SELECT CustNo
    FROM #Test
    WHERE CustNo = @FindNo;
sql-server t-sql

1 respostas

  1. Best Answer

    O que você está vendo é uma dobra constante.

    Mais informações neste link :

    A dobra constante é uma técnica que o otimizador usa para remover qualquer código desnecessário para ajudar a melhorar o desempenho. O Constant Folding faz isso removendo variáveis ​​desnecessárias e simplificando a consulta antes de compilar o plano.

    OPTION (RECOMPILE) permite dobrar constantemente @Step e @FindNo em sua segunda consulta. Como @Step é conhecido como igual a 1, a linha OR (@Step = 2 AND CustNo2 = @FindNo)será otimizada. Observe que isso afeta as linhas estimadas.

    Se você quiser ver um exemplo mais drástico disso, tente o código abaixo. Observe como a dobra constante (ativada pela opção recompilar) permite que o segundo plano de consulta evite completamente a união, pois o otimizador sabe que nenhuma linha pode ser retornada.

    CREATE TABLE #t (
id int)

INSERT #t
VALUES (1)

DECLARE @v char(5) = 'false'

SELECT TOP 1 *
FROM #t
UNION ALL
SELECT TOP 1 *
FROM #t
WHERE @v = 'true'

SELECT TOP 1 *
FROM #t
UNION ALL
SELECT TOP 1 *
FROM #t
WHERE @v = 'true'
OPTION (RECOMPILE)
    • 8

relate perguntas