索引未应用于索引视图

本文介绍了索引未应用于索引视图的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有一个索引视图，但是当我在该视图上运行查询时，未应用构建在视图上的索引，并且查询在没有索引的情况下运行.下面是我的虚拟脚本:表格 + 视图 + 视图索引

I have an indexed view but when I run queries on that view the index which is built on View is not applied and the query runs without index. Below is my dummy script:Tables + View+ Index on View

CREATE TABLE P_Test
  (
     [PID]      INT IDENTITY,
     [TID]      INT,
     [StatusID] INT
  )

CREATE TABLE T_Test
  (
     [TID] INT IDENTITY,
     [FID] INT,
  )

CREATE TABLE F_Test
  (
     [FID]      INT IDENTITY,
     [StatusID] INT
  )

GO

INSERT INTO F_Test
SELECT TOP 1000 ABS(CAST(NEWID() AS BINARY(6)) %10) --below 100
FROM   master..spt_values

INSERT INTO T_Test
SELECT TOP 10000 ABS(CAST(NEWID() AS BINARY(6)) %1000) --below 1000
FROM   master..spt_values,
       master..spt_values v2

INSERT INTO P_Test
SELECT TOP 100000 ABS(CAST(NEWID() AS BINARY(6)) %10000) --below 10000
                  ,
                  ABS(CAST(NEWID() AS BINARY(6)) %10)--below 10
FROM   master..spt_values,
       master..spt_values v2

GO

CREATE VIEW [TestView]
WITH SCHEMABINDING
AS
  SELECT P.StatusID AS PStatusID,
         F.StatusID AS FStatusID,
         P.PID
  FROM   dbo.P_Test P
         INNER JOIN dbo.T_Test T
           ON T.TID = P.TID
         INNER JOIN dbo.F_Test F
           ON T.FID = F.FID

GO

CREATE UNIQUE CLUSTERED INDEX [PK_TestView]
  ON [dbo].[TestView] ( [PStatusID] ASC, [FStatusID] ASC, [PID] ASC )
  WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

现在，当我运行以下查询时，未应用 [PK_TestView] 索引:

Now when I run the following queries the [PK_TestView] index is not being applied:

    SELECT PStatusID ,
        FStatusID ,
        PID  FROM [TestView]

SELECT PStatusID ,
        FStatusID ,
        PID  FROM [TestView]
WHERE [PStatusID]=1

SELECT COUNT(PStatusID) FROM [TestView]
WHERE [PStatusID]=1

你能帮我解决这个问题吗?

Can you help me fixing this?

推荐答案

您需要使用 NOEXPAND 提示.除非您使用的是企业版引擎，否则 SQL Server 不会考虑匹配索引视图(即使查询中引用了视图名称).

You need to use the NOEXPAND hint. SQL Server will not consider matching indexed views without this (even if the view name is referenced in the query) unless you are on Enterprise Edition engine.

SELECT COUNT(PStatusID)
FROM [TestView]
     WITH (NOEXPAND) -- this line
WHERE [PStatusID]=1

这应该会给你第一个更便宜的计划

This should give you the first, much cheaper, plan

这篇关于索引未应用于索引视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！