~楓花雪岳~: [SQL] Indexed View

星期二, 6月 23, 2020

[SQL] Indexed View

根據建立索引檢視(Indexed View)來提高查詢效能內的 TSQL 範例來學習，利用 AdventrueWorks2017 的 Sales.SalesOrderHeader 和 Sales.SalesOrderDetail 來跑彙總統計

未建立 Indexed View 前的 TSQL 執行計畫

-- 建立 IX_SalesOrderDetail_ProductID，讓執行計畫單純一些
CREATE INDEX IX_SalesOrderDetail_ProductID ON Sales.SalesOrderDetail (ProductID)
INCLUDE (OrderQty, UnitPrice, UnitPriceDiscount)
WITH (DROP_EXISTING = ON)

SELECT 
    o.OrderDate , 
    od.ProductID ,
    SUM(od.UnitPrice * od.OrderQty * (1.00 - od.UnitPriceDiscount)) AS Revenue
FROM Sales.SalesOrderHeader AS o 
    JOIN Sales.SalesOrderDetail AS od ON o.SalesOrderID = od.SalesOrderID
WHERE od.ProductID BETWEEN 700 and 800
    AND o.OrderDate >= '20130501'
GROUP BY o.OrderDate, od.ProductID
ORDER BY Revenue DESC;
GO

建立 Indexed View 限制

必須使用 WITH SCHEMABINDING
有 GROUP BY，必須包含 COUNT_BIG(*)，且不能包含 HAVING
必須使用兩部分名稱 schema . tablename
檢視不可參考其他檢視

建立 Indexed View

-- 設定 Indexed View 設定
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, 
   ANSI_WARNINGS, 
   CONCAT_NULL_YIELDS_NULL, 
   ARITHABORT,
   QUOTED_IDENTIFIER, 
   ANSI_NULLS 
   ON;

DROP VIEW IF EXISTS Sales.vwOrdersStat

-- 建立使用 Schemabinding 的 View
CREATE OR ALTER VIEW Sales.vwOrdersStat
   WITH SCHEMABINDING
   AS  
      SELECT 
          o.OrderDate , 
          od.ProductID ,
          SUM(od.UnitPrice * od.OrderQty * (1.00 - od.UnitPriceDiscount)) AS Revenue
          COUNT_BIG(*) AS COUNT
      FROM Sales.SalesOrderHeader AS o 
          JOIN Sales.SalesOrderDetail AS od ON o.SalesOrderID = od.SalesOrderID
      GROUP BY o.OrderDate, od.ProductID
GO

-- 建立 View 叢集索引後，該 View 即為 Index View
CREATE UNIQUE CLUSTERED INDEX IX_vwOrdersStat_OrderDate_ProductID
   ON Sales.vwOrdersStat (OrderDate, ProductID);
GO

-- TSQL Query 中即使沒有在 From 中使用 Index View，仍然會觸發 Index View
SELECT 
    o.OrderDate , 
    od.ProductID ,
    SUM(od.UnitPrice * od.OrderQty * (1.00 - od.UnitPriceDiscount)) AS Revenue
FROM Sales.SalesOrderHeader AS o 
    JOIN Sales.SalesOrderDetail AS od ON o.SalesOrderID = od.SalesOrderID
WHERE od.ProductID BETWEEN 700 AND 800
   AND o.OrderDate >= '20130501'
GROUP BY o.OrderDate, od.ProductID
ORDER BY Revenue DESC;
GO

範例中特別提到下列 TSQL 也會跑 Indexed View，因為有用到 Indexed View 的第一個欄位，假如只對 ProductID 進行彙總的話，就無法囉

SELECT 
    o.OrderDate , 
    SUM(od.UnitPrice * od.OrderQty * (1.00 - od.UnitPriceDiscount)) AS Revenue
FROM Sales.SalesOrderHeader AS o 
    JOIN Sales.SalesOrderDetail AS od ON o.SalesOrderID = od.SalesOrderID
WHERE od.ProductID BETWEEN 700 AND 800
    AND o.OrderDate >= '20130501'
GROUP BY o.OrderDate
ORDER BY Revenue DESC;
GO