视图匹配算法示例

如果查询频繁访问某个基表的某一部分，则最好定义一个实例化视图来存储此部分。例如，以下定义了一个实例化视图 V_Canada，该视图存储 Customer 表中居住在加拿大的所有客户。当在 State 列限定于某些值的情况下使用该实例化视图时，最好在实例化视图 V_Canada 的 State 列上创建索引 V_Canada_State。

CREATE MATERIALIZED VIEW V_Canada AS
 SELECT c.ID, c.City, c.State, c.CompanyName
  FROM Customers c
  WHERE c.State IN ( 'AB', 'BC', 'MB', 'NB', 'NL', 
   'NT', 'NS', 'NU', 'ON', 'PE', 'QC', 'SK', 'YT' );
REFRESH MATERIALIZED VIEW V_Canada;
CREATE INDEX V_Canada_State on V_Canada( State );

任何仅需要居住在加拿大的客户的子集的查询块都可以从该实例化视图中受益。例如，下面的查询 1 将为每个公司计算在安大略的所有客户的产品总价，可以在其访问计划中使用实例化视图 V_Canada。查询 1 的使用实例化视图 V_Canada 的访问计划将提供一个有效的计划，就像查询 1 已被重写为查询 1_v 一样，这在语义上是等效的。注意，优化程序并不使用实例化视图重写该查询，而生成的、使用实例化视图的访问计划在理论上可以视为与重写的查询对应。

查询 1 的执行计划使用实例化视图 V_Canada，如下所示： Work[ GrByH[ V_Canada<V_Canada_State> JNLO SalesOrders<FK_CustomerID_ID> JNLO SalesOrderItems<FK_ID_ID> JNLO Products<ProductsKey> ] ]

查询 1：

SELECT SUM( SalesOrderItems.Quantity
   * Products.UnitPrice ) AS Value
 FROM Customers c
  LEFT OUTER JOIN SalesOrders 
   ON( SalesOrders.CustomerID = c.ID )
  LEFT OUTER JOIN SalesOrderItems 
   ON( SalesOrderItems.ID = SalesOrders.ID )
  LEFT OUTER JOIN Products 
   ON( Products.ID = SalesOrderItems.ProductID )
 WHERE c.State = 'ON'
 GROUP BY c.CompanyName;

查询 1_v：

SELECT SUM( SalesOrderItems.Quantity
   * Products.UnitPrice ) AS Value
  FROM V_Canada 
   LEFT OUTER JOIN SalesOrders ON( SalesOrders.CustomerID = V_Canada.ID )
   LEFT OUTER JOIN SalesOrderItems ON( SalesOrderItems.ID = SalesOrders.ID )
   LEFT OUTER JOIN Products ON( Products.ID = SalesOrderItems.ProductID )
  WHERE V_Canada.State = 'ON'
  GROUP BY V_Canada.CompanyName;

查询 2 可以在主查询块和 HAVING 子查询中使用该视图。优化程序枚举的某些使用实例化视图 V_Canada 的访问计划会提供查询 2_v，其与查询 2 在语义上是等效的，其中用 V_Canada 视图替代了 Customer 表。

执行计划为： Work[ GrByH[ V_Canada<V_Canada_State> JNLO SalesOrders<FK_CustomerID_ID> JNLO SalesOrderItems<FK_ID_ID> JNLO Products<ProductsKey> ] ] : GrByS[ V_Canada<seq> JNLO SalesOrders<FK_CustomerID_ID> JNLO SalesOrderItems<FK_ID_ID> JNLO Products<ProductsKey>

查询 2：

SELECT  SUM( SalesOrderItems.Quantity
  * Products.UnitPrice ) AS Value
 FROM Customers c
  LEFT OUTER JOIN SalesOrders 
   ON( SalesOrders.CustomerID = c.ID )
  LEFT OUTER JOIN SalesOrderItems 
   ON( SalesOrderItems.ID = SalesOrders.ID )
  LEFT OUTER JOIN Products 
   ON( Products.ID = SalesOrderItems.ProductID )
 WHERE c.State = 'ON'
 GROUP BY CompanyName
 HAVING Value > 
  ( SELECT AVG( SalesOrderItems.Quantity
        * Products.UnitPrice ) AS Value
      FROM Customers c1
       LEFT OUTER JOIN SalesOrders 
       ON( SalesOrders.CustomerID = c1.ID )
       LEFT OUTER JOIN SalesOrderItems 
       ON( SalesOrderItems.ID = SalesOrders.ID )
        LEFT OUTER JOIN Products 
       ON( Products.ID = SalesOrderItems.ProductID )
       WHERE c1.State IN ('AB', 'BC', 'MB', 'NB', 'NL', 'NT', 'NS', 
      'NU', 'ON', 'PE', 'QC', 'SK', 'YT' ) );

查询 2_v：

SELECT  SUM( SalesOrderItems.Quantity
         * Products.UnitPrice ) AS Value
 FROM V_Canada
 LEFT OUTER JOIN SalesOrders 
  ON( SalesOrders.CustomerID=V_Canada.ID )
 LEFT OUTER JOIN SalesOrderItems 
  ON( SalesOrderItems.ID=SalesOrders.ID )
 LEFT OUTER JOIN Products 
  ON( Products.ID=SalesOrderItems.ProductID )
WHERE V_Canada.State = 'ON'
GROUP BY V_Canada.CompanyName
HAVING Value > 
   ( SELECT AVG( SalesOrderItems.Quantity
         * Products.UnitPrice ) AS Value
       FROM V_Canada
        LEFT OUTER JOIN SalesOrders 
        ON( SalesOrders.CustomerID = V_Canada.ID )
        LEFT OUTER JOIN SalesOrderItems 
        ON( SalesOrderItems.ID = SalesOrders.ID )
         LEFT OUTER JOIN Products 
        ON( Products.ID = SalesOrderItems.ProductID )
        WHERE V_Canada.State IN ('AB', 'BC', 'MB', 
       'NB', 'NL', 'NT', 'NS', 'NU', 'ON', 'PE', 'QC', 
       'SK', 'YT' ) );

分组实例化视图可以使分组查询性能达到最佳。如果频繁执行的查询中使用了相似的集合，则应在这些查询中使用的 GROUP BY 子句的超集上定义实例化视图，以预先集合数据。查询的组合集合函数可从视图中使用的简单集合函数计算得出。因此，建议您在实例化视图中只存储简单集合函数。

下面的实例化视图 V_Quantity 预先计算每年和每月每种产品的总和和计数。下面的查询 3 可使用该视图只选择 2000 年中的月份（简要计划为 Work[ GrByH[ V_Quantity<seq> ] ]，对应于查询 3_v）。

下面的查询 4（未引用扩展表 SalesOrders）仍可使用 V_Quantity，因为视图包含计算查询 4 所需的全部数据（简要计划为 Work[ GrByH[ V_Quantity<seq> ] ]，对应于查询 4_v）。

CREATE MATERIALIZED VIEW V_Quantity AS
 SELECT s.ProductID, 
  Month( o.OrderDate ) AS month,
  Year( o.OrderDate ) AS year, 
  SUM( s.Quantity ) AS q_sum,
  COUNT( s.Quantity  ) AS q_count       
 FROM SalesOrderItems s KEY JOIN SalesOrders o
 GROUP BY s.ProductID, Month( o.OrderDate ), 
  Year( o.OrderDate );
REFRESH MATERIALIZED VIEW V_Quantity;

查询 3：

SELECT s.ProductID, 
  Month( o.OrderDate ) AS month,
  AVG( s.Quantity) AS avg, 
  SUM( s.Quantity ) AS q_sum,
  COUNT( s.Quantity ) AS q_count       
 FROM SalesOrderItems s KEY JOIN SalesOrders o 
 WHERE year( o.OrderDate ) = 2000
 GROUP BY s.ProductID, Month( o.OrderDate );

查询 3_v：

SELECT V_Quantity.ProductID, 
  V_Quantity.month AS month,
  SUM( V_Quantity.q_sum ) / SUM( V_Quantity.q_count ) 
   AS avg, 
  SUM( V_Quantity.q_sum ) AS q_sum,
  SUM( V_Quantity.q_count ) AS q_count       
 FROM V_Quantity
 WHERE V_Quantity.year  = 2000
 GROUP BY V_Quantity.ProductID, V_Quantity.month;

查询 4：

SELECT s.ProductID, 
  AVG( s.Quantity ) AS avg,
  SUM( s.Quantity ) AS sum 
 FROM SalesOrderItems s  
 WHERE s.ProductID IS NOT NULL
 GROUP BY s.ProductID;

查询 4_v

SELECT V_Quantity.ProductID, 
  SUM( V_Quantity.q_sum ) / SUM( V_Quantity.q_count ) 
   AS avg, 
  SUM( V_Quantity.q_sum ) AS sum  
 FROM V_Quantity
 WHERE V_Quantity.ProductID IS NOT NULL
 GROUP BY V_Quantity.ProductID;

视图匹配算法会应用于每个查询块，因此每个查询块可以使用多个实例化视图，而且整个查询也可以使用多个实例化视图。下面的查询 5 可以使用以下三个实例化视图：V_Canada，用于 LEFT OUTER JOIN 的提供空值方；V_ShipDate（下面进行了定义），用于主查询块的保留方；V_Quantity，用于子查询块。查询 5_v 的执行计划为： Work[ Window[ Sort[ V_ShipDate<V_ShipDate_date> JNLO ( so<SalesOrdersKey> JH V_Canada<V_Canada_State> ) ] ] ] : GrByS[V_Quantity<seq> ].

CREATE MATERIALIZED VIEW V_ShipDate AS
 SELECT s.ProductID, p.Description, 
  s.Quantity, s.ShipDate, s.ID
 FROM SalesOrderItems s KEY JOIN Products p ON ( s.ProductId = p.ID )
 WHERE s.ShipDate >= '2000-01-01' 
  AND s.ShipDate <= '2001-01-01';
REFRESH MATERIALIZED VIEW V_ShipDate;
CREATE INDEX V_ShipDate_date ON V_ShipDate( ShipDate );

查询 5：

SELECT p.ID, p.Description, s.Quantity, 
  s.ShipDate, so.CustomerID, c.CompanyName,
  SUM( s.Quantity ) OVER ( PARTITION BY s.ProductID
                       ORDER BY s.ShipDate
                       ROWS BETWEEN UNBOUNDED PRECEDING
                       AND CURRENT ROW ) AS cumulative_qty
 FROM SalesOrderItems s JOIN Products p 
  ON (s.ProductID = p.ID) LEFT OUTER JOIN ( 
   SalesOrders so JOIN Customers c 
    ON ( c.ID = so.CustomerID AND c.State = 'ON' ) )
  ON (s.ID = so.ID )
 WHERE s.ShipDate >= '2000-01-01' 
   AND s.ShipDate <= '2000-12-31' 
   AND s.Quantity  > ( SELECT  AVG( s.Quantity ) AS avg
                        FROM SalesOrderItems s KEY JOIN SalesOrders o
                        WHERE year( o.OrderDate) = 2000 )
 FOR READ ONLY;

查询 5_v：

SELECT  V_ShipDate.ID, V_ShipDate.Description, 
  V_ShipDate.Quantity, V_ShipDate.ShipDate, 
  so.CustomerID, V_Canada.CompanyName,
  SUM( V_ShipDate.Quantity ) OVER ( PARTITION BY V_ShipDate.ProductID
                                     ORDER BY V_ShipDate.ShipDate
                                     ROWS BETWEEN UNBOUNDED PRECEDING
                                     AND CURRENT ROW ) AS cumulative_qty
 FROM V_ShipDate
  LEFT OUTER JOIN ( SalesOrders so JOIN V_Canada
   ON ( V_Canada.ID = so.CustomerID AND V_Canada.State = 'ON' ) )
  ON ( V_ShipDate.ID = so.ID )
 WHERE V_ShipDate.ShipDate >= '2000-01-01' 
  AND V_ShipDate.ShipDate <= '2000-12-31' 
  AND V_ShipDate.Quantity >
    ( SELECT SUM( V_Quantity.q_sum ) / SUM( V_Quantity.q_count ) 
       FROM V_Quantity
       WHERE V_Quantity.year = 2000 )
 FOR READ ONLY;

使用与仅带有内连接的视图类似的规则，视图匹配算法可匹配带有 OUTER JOIN 的视图和查询。只要提供空值方的所有表都是扩展表，则实例化视图的 OUTER JOIN 空值提供方可不在查询中出现。查询可包含内连接，以匹配视图的外连接。下面的查询 6_v、7_v、8_v 和 9_v 说明了如何使用在其定义中包含 OUTER JOIN 的实例化视图来回应查询。

下面的查询 6 完全匹配实例化视图 V_SalesOrderItems_2000，并可作为查询 6_v 来计算。

查询 7 包含一些额外的外连接保留方的谓语，并且仍然可以使用 V_SalesOrderItems_2000 进行计算。请注意，在视图 V_SalesOrderItems_2000 中，提供空值的表 Products 是扩展表。这意味着该视图也与查询 8 匹配，查询 8 不包含表 Products。

查询 9 只包含表 SalesOrderItems 和表 Products 的内连接，且通过仅选择视图 V_SalesOrderItems_2000 中表 Products 的非空值提供行，与该视图匹配。查询 9_v 中的额外谓语 V.Description IS NOT NULL 用于仅选择那些非提供空值的行。

CREATE MATERIALIZED VIEW V_SalesOrderItems_2000 AS
 SELECT s.ProductID, p.Description, 
  s.Quantity, s.ShipDate, s.ID
 FROM SalesOrderItems s LEFT OUTER JOIN Products p 
    ON ( s.ProductId = p.ID )
 WHERE s.ShipDate >= '2000-01-01' 
  AND s.ShipDate <= '2001-01-01';
REFRESH MATERIALIZED VIEW V_SalesOrderItems_2000;
CREATE INDEX V_SalesOrderItems_shipdate ON V_SalesOrderItems_2000( ShipDate );

查询 6：

SELECT s.ProductID, p.Description, 
  s.Quantity, s.ShipDate, s.ID
 FROM SalesOrderItems s LEFT OUTER JOIN Products p 
    ON ( s.ProductId = p.ID )
 WHERE s.ShipDate >= '2000-01-01' 
  AND s.ShipDate <= '2001-01-01'
 FOR READ ONLY;

查询 6_v：

SELECT V.ProductID,  V.Description, V.Quantity, V.ShipDate,
  SUM( V.Quantity ) OVER ( PARTITION BY V.ProductID
                       ORDER BY V.ShipDate
                       ROWS BETWEEN UNBOUNDED PRECEDING
                       AND CURRENT ROW ) AS cumulative_qty
 FROM V_SalesOrderItems_2000 as V     
 FOR READ ONLY;

查询 7：

SELECT s.ProductID,  p.Description, s.Quantity, s.ShipDate,
  SUM( s.Quantity ) OVER ( PARTITION BY s.ProductID
                       ORDER BY s.ShipDate
                       ROWS BETWEEN UNBOUNDED PRECEDING
                       AND CURRENT ROW ) AS cumulative_qty
 FROM SalesOrderItems s LEFT OUTER JOIN Products p 
    ON (s.ProductID = p.ID )
      
 WHERE s.ShipDate >= '2000-01-01' 
   AND s.ShipDate <= '2001-01-01'  
   AND s.Quantity >= 50  
 FOR READ ONLY;

查询 7_v：

SELECT V.ProductID,  V.Description, V.Quantity, V.ShipDate,
  SUM( V.Quantity ) OVER ( PARTITION BY V.ProductID
                       ORDER BY V.ShipDate
                       ROWS BETWEEN UNBOUNDED PRECEDING
                       AND CURRENT ROW ) AS cumulative_qty
 FROM V_SalesOrderItems_2000 as V  
 WHERE V.Quantity >= 50  
 FOR READ ONLY;

查询 8：

SELECT s.ProductID, s.Quantity, s.ShipDate,
  SUM( s.Quantity ) OVER ( PARTITION BY s.ProductID
                       ORDER BY s.ShipDate
                       ROWS BETWEEN UNBOUNDED PRECEDING
                       AND CURRENT ROW ) AS cumulative_qty
 FROM SalesOrderItems s  
 WHERE s.ShipDate >= '2000-01-01' 
   AND s.ShipDate <= '2001-01-01' 
   AND s.Quantity >= 50   
 FOR READ ONLY;

查询 8_v：

SELECT V.ProductID, V.Quantity, V.ShipDate,
  SUM( V.Quantity ) OVER ( PARTITION BY V.ProductID
                       ORDER BY V.ShipDate
                       ROWS BETWEEN UNBOUNDED PRECEDING
                       AND CURRENT ROW ) AS cumulative_qty
 FROM V_SalesOrderItems_2000 as V  
 WHERE V.Quantity >= 50  
 FOR READ ONLY;

查询 9：

SELECT s.ProductID,  p.Description, s.Quantity, s.ShipDate,
  SUM( s.Quantity ) OVER ( PARTITION BY s.ProductID
                       ORDER BY s.ShipDate
                       ROWS BETWEEN UNBOUNDED PRECEDING
                       AND CURRENT ROW ) AS cumulative_qty
 FROM SalesOrderItems s JOIN Products p 
      ON (s.ProductID = p.ID )
      
 WHERE s.ShipDate >= '2000-01-01' 
   AND s.ShipDate <= '2001-01-01'   
 FOR READ ONLY;

查询 9_v：

SELECT V.ProductID,  V.Description, V.Quantity, V.ShipDate,
  SUM( V.Quantity ) OVER ( PARTITION BY V.ProductID
                       ORDER BY V.ShipDate
                       ROWS BETWEEN UNBOUNDED PRECEDING
                       AND CURRENT ROW ) AS cumulative_qty
 FROM V_SalesOrderItems_2000 as V  
 WHERE V.Description IS NOT NULL   
 FOR READ ONLY;

视图匹配算法示例

示例 1：匹配选择项目连接视图

示例 2：匹配分组选择项目连接视图

示例 3：匹配复杂查询

示例 4：匹配带有 OUTER JOIN 的实例化视图