View Matching algorithm examples

If a certain partition of a base table is frequently accessed by queries, then it may be beneficial to define a materialized view to store that partition. For example, the materialized view V_Canada defined below stores all the customers from the Customer table who live in Canada. As this materialized view is used when the State column is restricted to certain values, it is advisable to create the index V_Canada_State on the State column of the V_Canada materialized view.

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

Any query block that requires just a subset of customers living in Canada may benefit from this materialized view. For example, Query 1 below, which computes the total price of the products for all customers in Ontario for each company, may use the V_Canada materialized view in its access plans. An access plan for Query 1 using the V_Canada materialized view represents a valid plan as if Query 1 was rewritten as Query 1_v, which is semantically equivalent to it. Note that the optimizer doesn't rewrite the query using the materialized views, instead the generated access plans using materialized views can theoretically be seen corresponding to the rewritten query.

The execution plan of the Query 1 uses the V_Canada materialized view, as shown here: Work[ GrByH[ V_Canada<V_Canada_State> JNLO SalesOrders<FK_CustomerID_ID> JNLO SalesOrderItems<FK_ID_ID> JNLO Products<ProductsKey> ] ]

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

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

Query 2 may use this view in both the main query block and the HAVING subquery. Some of the access plans enumerated by the optimizer using the V_Canada materialized view represent Query 2_v, which is semantically equivalent to Query 2 where the Customer table was replaced by the V_Canada view.

The execution plan is: 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>

Query 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' ) );

Query 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' ) );

The grouped materialized views have the potential for the highest performance impact on the grouped queries. If similar aggregations are used in frequently-executed queries, a materialized view should be defined to pre-aggregate data on a superset of the group by clauses used in those queries. Composite aggregate functions of the queries can be computed from the simple aggregates used in the views. So, it is recommended that only simple aggregate functions are stored in the materialized views.

The materialized view V_Quantity, below, precomputes the sum and count of quantities per product for each month and year. Query 3, below, can use this view to select only the months of the year 2000 (the short plan is Work[ GrByH[ V_Quantity<seq> ] ], corresponding to Query 3_v).

Query 4, which doesn't reference the extension table SalesOrders, can still use V_Quantity as the view contains all the data necessary to compute Query 4 (the short plan is Work[ GrByH[ V_Quantity<seq> ] ], corresponding to Query 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;

Query 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 );

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

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

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

The View Matching algorithm is applied per query block, so it is possible to use more than one materialized view per query block and also more than one materialized view for the whole query. Query 5 below may use the three materialized views: V_Canada for one of the null-supplying sides of the LEFT OUTER JOIN; V_ShipDate, defined below, for the preserved side of the main query block; and V_Quantity for the subquery block. The execution plan for Query 5_v is: 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 );

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

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

The view matching algorithm can match views and queries with OUTER JOINs using similar rules as for the views with only inner joins. Null-supplying sides of the OUTER JOINs of a materialized view may not appear in the query as long as all the tables in the null-supplying side are extension tables. The query is allowed to contain inner joins to match the view's outer joins. Queries 6_v, 7_v, 8_v, and 9_v below illustrate how a materialized view containing an OUTER JOIN in its definition can be used to answer queries.

Query 6 below matches exactly the materialized view V_SalesOrderItems_2000 and can be evaluated as if it is Query 6_v.

Query 7 contains some extra predicates on the preserved side of the outer join and it can still be computed using V_SalesOrderItems_2000. Note that the null-supplying table, Products, is an extension table in the view V_SalesOrderItems_2000. This means the view can also be matched with Query 8, which does not contain the Products table.

Query 9 contains only the inner join of the tables SalesOrderItems and Products, and it is matched with the V_SalesOrderItems_2000 view by selecting only those rows of the view which are not null-supplying rows from the table Products. The extra predicate, V.Description IS NOT NULL, in Query 9_v is used to select exactly those rows which are not null-supplied.

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

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

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

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

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

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

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

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

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

View Matching algorithm examples

Example 1: Matching select-project-join views

Example 2: Matching grouped-select-project-join views

Example 3: Matching complex queries

Example 4: Matching materialized views with OUTER JOINs