主页 / dba / 问题 / 135290
Accepted
LCJ
Asked: 2016-04-15 05:43:10 +0800 CST

使用两列条件时过滤索引错误

  • 772

我创建了一个过滤索引（在 SQL Server 2012 中）并将其与非过滤索引进行了比较——我可以看到有希望的改进，如下所示。现在我需要更改过滤条件以使用表中的两列。所需条件是 WHERE InboundQuantity - OutboundQuantity <> 0或 InboundQuantity <> OutboundQuantity

但是当我应用此过滤器时，我收到错误消息

“过滤索引 'IX_WO_PlantCD_FilterQtyNotEqual' 的 WHERE 子句不正确”。

我知道过滤索引有局限性。但是，有没有办法通过两列条件，使用过滤索引等来实现这种改进？

询问

--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

计划

在此处输入图像描述

sql-server performance

2 个回答

  1. Best Answer

    如果计算列上的筛选索引过于有限，您可以选择创建索引视图。

    索引视图由数据库自动维护，因此您无需担心所有可能的 DML 操作的触发逻辑是否正确。您也不必担心高并发下复杂的正确性问题。

    维护视图索引的开销通常也比触发器少。

    例如，给定一个表：

    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;

    一个合适的索引视图可能是：

    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;

    如果您正在运行企业版，自动索引视图匹配意味着在不直接引用视图的情况下编写的查询仍然可以使用它：

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

    执行计划显示正在访问的索引视图：

    执行计划1

    对于其他版本，您需要直接引用视图，并使用NOEXPAND提示：

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

    执行计划：

    执行计划2

    与索引计算列相同的连接设置限制适用于索引视图。即使在企业版中，您也可以选择使用该NOEXPAND版本，因为视图的使用是有保证的（不是由优化器决定的），并且基数估计通常也会更好。

    添加索引视图的效果取决于很多因素，因此您需要对其进行测试。任何安全的解决方案都有可能增加争用，但在索引计算列不适合的情况下，索引视图通常是下一个最佳选择。如果做得好，效果可能很小。单个表上的索引视图的开销不会超过向基表添加新的非聚集索引的开销。

    • 3

  2. 我考虑过添加这样的计算列：

    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 ;

    但这不起作用。

    这有点麻烦，但它有效：

    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 ;

    但您需要在表上添加触发器以保持diff列更新。

    添加约束也很好：

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

    ...您如何选择维护列（触发或更改表上的所有插入/更新过程）。

    • 2

相关问题