Início / dba / Perguntas / 135290
Accepted
LCJ
Asked: 2016-04-15 05:43:10 +0800 CST

Erro de índice filtrado ao usar a condição de duas colunas

  • 772

Eu criei um índice filtrado (no SQL Server 2012) e o comparei com um índice não filtrado – pude ver melhorias promissoras conforme mostrado abaixo. Agora preciso alterar a condição do filtro para usar duas colunas na tabela. A condição necessária é WHERE InboundQuantity - OutboundQuantity <> 0ou InboundQuantity <> OutboundQuantity

Mas quando aplico este filtro, recebo uma mensagem de erro

“Cláusula WHERE incorreta para índice filtrado 'IX_WO_PlantCD_FilterQtyNotEqual'”.

Eu sei que existem limitações com índice filtrado. No entanto, existe uma maneira de obter essa melhoria com duas condições de coluna, usando índice filtrado ou algo semelhante?

Consulta

--Normal Index
CREATE NONCLUSTERED INDEX IX_WO_NormalPlantCD
ON dbo.MyTable (PlantCD) INCLUDE (InboundQuantity,OutboundQuantity)

--Filtered Index
CREATE NONCLUSTERED INDEX IX_WO_PlantCD_FilterInboundQtyNotEqual
ON dbo.MyTable (PlantCD) INCLUDE (InboundQuantity,OutboundQuantity)
WHERE InboundQuantity <> 0


--Query 1
SELECT SUM([InboundQuantity] - [OutboundQuantity])
FROM [MyTable]
WHERE [PlantCD] = 'XX'

--Query2 (suing same where condition as filtered index)
SELECT SUM([InboundQuantity] - [OutboundQuantity])
FROM [MyTable]
WHERE [PlantCD] = 'XX'
AND InboundQuantity<>0

Plano

insira a descrição da imagem aqui

sql-server performance

2 respostas

  1. Best Answer

    Onde um índice filtrado em uma coluna computada é muito limitado, você tem a opção de criar uma exibição indexada.

    A exibição indexada é mantida automaticamente pelo banco de dados, portanto, você não precisa se preocupar em obter a lógica de disparo correta para todas as operações DML possíveis. Você também não precisa se preocupar com problemas complicados de correção em alta simultaneidade.

    A sobrecarga de manter os índices de exibição também é normalmente menor do que para gatilhos.

    Por exemplo, dada uma tabela:

    CREATE TABLE dbo.MyTable
(
    PlantCD varchar(10) NOT NULL,
    InboundQuantity integer NOT NULL,
    OutboundQuantity integer NOT NULL
);
GO
-- Pretend the table has rows
UPDATE STATISTICS dbo.MyTable 
WITH ROWCOUNT = 1000000, PAGECOUNT = 50000;

    Uma exibição indexada adequada pode ser:

    CREATE VIEW dbo.MyTableQtyDiff
WITH SCHEMABINDING
AS
SELECT
    MT.PlantCD,
    QtyDiff = SUM(MT.InboundQuantity - MT.OutboundQuantity),
    NumRows = COUNT_BIG(*)
FROM dbo.MyTable AS MT
WHERE
    MT.InboundQuantity <> MT.OutboundQuantity
GROUP BY
    MT.PlantCD;
GO
CREATE UNIQUE CLUSTERED INDEX 
    CUQ_dbo_MyTableQtyDiff__PlantCD
ON dbo.MyTableQtyDiff
    (PlantCD);
GO
-- Pretend the view has some rows
UPDATE STATISTICS dbo.MyTableQtyDiff
WITH ROWCOUNT = 10000, PAGECOUNT = 50;

    Se você estiver executando o Enterprise Edition, a correspondência de visualização indexada automática significa que uma consulta escrita sem referenciar a visualização diretamente ainda pode usá-la:

    -- Enterprise Edition required
SELECT 
    SUM(MT.InboundQuantity - MT.OutboundQuantity)
FROM dbo.MyTable AS MT
WHERE
    MT.InboundQuantity <> MT.OutboundQuantity;

    O plano de execução mostra a exibição indexada sendo acessada:

    Plano de execução 1

    Para outras edições, você precisaria referenciar a view diretamente e usar a NOEXPANDdica:

    -- Any edition
SELECT 
    SUM(MTQD.QtyDiff)
FROM dbo.MyTableQtyDiff AS MTQD WITH (NOEXPAND);

    Plano de execução:

    Plano de execução 2

    As mesmas restrições de configuração de conexão se aplicam a exibições indexadas para colunas computadas indexadas. Você pode optar por usar a NOEXPANDversão mesmo na Enterprise Edition, pois o uso da exibição é garantido (não decidido pelo otimizador) e as estimativas de cardinalidade geralmente também serão melhores .

    O efeito de adicionar uma exibição indexada depende de muitos fatores, portanto, você precisa testá-lo. Qualquer solução segura tem o potencial de aumentar a contenção, mas onde uma coluna computada indexada não se encaixa, uma exibição indexada geralmente é a segunda melhor opção. Se bem feito, o efeito pode ser mínimo. A sobrecarga de uma exibição indexada em uma única tabela não pode ser maior do que a adição de um novo índice não clusterizado à tabela base.

    • 3

  2. Pensei em adicionar uma coluna computada como esta:

    ALTER TABLE dbo.mytable 
  ADD Diff AS InboundQuantity - OutboundQuantity PERSISTED ;
go
CREATE NONCLUSTERED INDEX IX_WO_PlantCD_FilterInboundQtyNotEqual
  ON dbo.MyTable (PlantCD) 
  INCLUDE (InboundQuantity,OutboundQuantity)
  WHERE Diff <> 0 ;

    mas isso não funciona.

    Isso é um pouco complicado, mas funciona:

    ALTER TABLE dbo.mytable 
  ADD Diff INT ;
go
UPDATE dbo.mytable 
  SET Diff= InboundQuantity - OutboundQuantity ;
GO
CREATE NONCLUSTERED INDEX IX_WO_PlantCD_FilterInboundQtyNotEqual
  ON dbo.MyTable (PlantCD) 
  INCLUDE (InboundQuantity,OutboundQuantity)
  WHERE Diff <> 0 ;

    mas você precisa adicionar um gatilho na tabela para manter a diffcoluna atualizada.

    Também seria bom adicionar uma restrição:

    ALTER TABLE dbo.mytable 
  ADD CONSTRAINT CK_Diff_equals_Inbound_minus_Outbound_quantity
    CHECK (Diff = InboundQuantity - OutboundQuantity) ;

    ...como quer que você escolha manter a coluna (acionar ou alterar todos os procedimentos de inserção/atualização na tabela).

    • 2

relate perguntas