日期:2014-05-18  浏览次数:20527 次

直接查询一个索引视图,但是执行计划显示并没有查询索引视图,查询的还是基础表?
USE AdventureWorks
GO

-- 建立索引视图时, 必须满足的选项设置
SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO

-- 建立视图
CREATE VIEW Sales.v_SaleOrders
WITH SCHEMABINDING -- 索引视图要求必须具有此项
AS
SELECT 
O.OrderDate, OD.ProductID,
Revenue = SUM(OD.UnitPrice * OD.OrderQty * (1 - OD.OrderQty)),
ItemCount = COUNT_BIG(*) -- 索引视图中必须用COUNT_BIG
FROM Sales.SalesOrderHeader O
INNER JOIN Sales.SalesOrderDetail OD
ON O.SalesOrderID = OD.SalesOrderID
GROUP BY O.OrderDate, OD.ProductID
GO
-- 在视图上建立索引
CREATE UNIQUE CLUSTERED INDEX IXUC_ProductID_OrderDate
ON Sales.v_SaleOrders(
ProductID, OrderDate)
GO

-- 执行与视图相关的查询
SET SHOWPLAN_TEXT ON
GO 
SELECT * FROM Sales.v_SaleOrders --WITH(INDEX = IXUC_ProductID_OrderDate)
GO
SET SHOWPLAN_TEXT OFF
GO

-- 删除演示环境
DROP VIEW Sales.v_SaleOrders


/*

StmtText
----------------------------------------
SELECT * FROM Sales.v_SaleOrders --WITH(INDEX = IXUC_ProductID_OrderDate)

(1 row(s) affected)

StmtText
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Parallelism(Gather Streams)
  |--Hash Match(Aggregate, HASH:([O].[OrderDate], [OD].[ProductID]), RESIDUAL:([AdventureWorks].[Sales].[SalesOrderHeader].[OrderDate] as [O].[OrderDate] = [AdventureWorks].[Sales].[SalesOrderHeader].[OrderDate] as [O].[OrderDate] AND [AdventureWorks].[Sales].[SalesOrderDetail].[ProductID] as [OD].[ProductID] = [AdventureWorks].[Sales].[SalesOrderDetail].[ProductID] as [OD].[ProductID]) DEFINE:([Expr1004]=SUM([Expr1006]), [Expr1005]=COUNT(*)))
  |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([O].[OrderDate], [OD].[ProductID]))
  |--Hash Match(Inner Join, HASH:([O].[SalesOrderID])=([OD].[SalesOrderID]))
  |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([O].[SalesOrderID]))
  | |--Clustered Index Scan(OBJECT:([AdventureWorks].[Sales].[SalesOrderHeader].[PK_SalesOrderHeader_SalesOrderID] AS [O]))
  |--Compute Scalar(DEFINE:([Expr1006]=([AdventureWorks].[Sales].[SalesOrderDetail].[UnitPrice] as [OD].[UnitPrice]*CONVERT_IMPLICIT(money,[AdventureWorks].[Sales].[SalesOrderDetail].[OrderQty] as [OD].[OrderQty],0))*CONVERT_IMPLICIT(money,(1)-CONVERT_IMPLICIT(int,[AdventureWorks].[Sales].[SalesOrderDetail].[OrderQty] as [OD].[OrderQty],0),0)))
  |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([OD].[SalesOrderID]))
  |--Clustered Index Scan(OBJECT:([AdventureWorks].[Sales].[SalesOrderDetail].[PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID] AS [OD]))

(9 row(s) affected)
*/

------解决方案--------------------
没看到他
------解决方案--------------------
搬个板凳看.

------解决方案--------------------
楼主,你的代码在我这里执行出来的结果是不一样的,我的是:
|--Clustered Index Scan(OBJECT:([AdventureWorks].[Sales].[v_SaleOrders].[IXUC_ProductID_OrderDate]))
------解决方案--------------------
SQL code

当满足下列条件时,SQL Server 查询优化器使用索引视图: 

下列会话选项均设置为 ON: 


ANSI_NULLS


ANSI_PADDING


ANSI_WARNINGS


ARITHABORT


CONCAT_NULL_YIELDS_NULL


QUOTED_IDENTIFIER 


NUMERIC_ROUNDABORT 会话选项设置为 OFF。


查询优化器查找视图索引列与查询中的元素之间的匹配,例如: 


WHERE 子句中的搜索条件谓词


联接操作


聚合函数


GROUP BY 子句


表引用


估计的索引使用成本是查询优化器考虑使用的所有访问机制中的最低成本。 


查询中引用(直接或通过展开视图访问其基础表)的且与索引视图中的表引用相对应的每个表在该查询中都必须具有应用于表的相同提示集。

 
注意:在此上下文中,不管当前的事务隔离级别是