View matching

The View Matching algorithm determines whether materialized views can be used to satisfy a query. This determination takes place in two steps: a query evaluation step, and a materialized view evaluation step.

Query evaluation step

During the query evaluation step, the View Matching algorithm examines the query. If any of the following conditions are true, materialized views are not used to process the query.

  • All of the tables referenced by the query are updatable (see View matching).

    Consequently, the optimizer will not consider materialized views for a SELECT statement that is inherently updatable, or is explicitly declared in an updatable cursor. This situation can occur when using Interactive SQL, which utilizes updatable cursors by default for SELECT statements.

  • The statement is a simple DML statement that uses optimizer bypass (and thus is optimized heuristically). However, you can force cost-based optimization of any SELECT statement using the FORCE OPTIMIZATION option of the OPTION clause. See SELECT statement.
  • The query's execution plan has been cached, as in the case of queries contained inside stored procedures and user-defined functions. The database server may cache the execution plans for these queries so that they can be reused. For this class of queries, the query execution plan is cached after execution. The next time the query is executed, the plan is retrieved and all the phases up to the execution phase are skipped. See Plan caching.
Materialized view evaluation step

The materialized view evaluation step involves determining which of the existing materialized views could be used to compute all or parts of the query.

Once a materialized view has been matched with parts of a query, a decision is made whether to use the view in the final query execution plan; this decision is cost-based. The role of the enumeration phase is to generate plans containing views recommended by the View Matching algorithm and choose, based on the estimated cost of the plans, the best access plan which may or may not contain some of the materialized views.

If the materialized view is defined as a grouped-select-project-join query (also known as a grouped query, or a query containing a GROUP BY clause), then the View Matching algorithm can match it with grouped query blocks. If a materialized view is defined as a select-project-join query (that is, it is not a grouped query), then the View Matching algorithm can match it to any type of query block.

Listed below are the conditions necessary for the View Matching algorithm to decide if a view, V, matches part of a query block, QB, belonging to a query, Q. In general, V must contain a subset of the query QB’s tables. The only exception is the extension tables of V. An extension table of V is a table that joins with exactly one row with the rest of the tables of V. For example, a primary key table is an extension table if its only predicate is an equijoin between a not-null foreign key column and its primary key column. For an example of a materialized view that contains an extension table, see Example 2: Matching grouped-select-project-join views.

  • The option values used to create the materialized view V match the option values for the connection executing the query. For a list of the options that must match, see Restrictions on materialized views.
  • The last refresh of the V materialized view, does not exceed the staleness threshold specified by the materialized_view_optimization database option, or by the MATERIALIZED VIEW OPTIMIZATION clause, if specified, in the SELECT statement. See Setting the optimizer staleness threshold for materialized views.
  • All the tables used in V, with possible exceptions of some extension tables of V, are present in the QB. This set of common tables in the QB is hereinafter referred to as CT.
  • No table in CT is updatable in the query Q.
  • All tables in CT belong to the same side of an outer join in QB (that is, they are all in the preserved side of the outer join or all in the null-supplying side of an outer join of QB).
  • It can be decided that the predicates in V subsume the subset of the predicates in QB that reference CT only. In other words, the predicates in V are less restrictive than those in QB. A predicate in QB that exactly matches one in V is called a matched predicate.
  • Any expression of QB referencing tables in CT that is not used in a matched predicate must appear in the select list of V.
  • If both V and QB are grouped, then QB doesn’t contain extra tables besides the ones in CT. Additionally, the set of expressions in the GROUP BY clause of V must be equal to or a superset of the set of expressions in the GROUP BY clause of QB.
  • If both V and QB are grouped on an identical set of expressions, all aggregate functions in QB must be also computed in V, or it is possible to compute them from V’s aggregate functions. For example, if QB contains AVG(x) then V must contain AVG(x), or it must contain both SUM(x) and COUNT(x).
  • If QB’s GROUP BY clause is a subset of V’s GROUP BY clause, then the simple aggregate functions of QB must be found among V’s aggregate functions, while its composite aggregate functions have to be computed from simple aggregate functions of V. The simple aggregate functions are:
    • BIT_AND
    • BIT_OR
    • BIT_XOR
    • COUNT
    • LIST
    • MAX
    • MIN
    • SET_BITS
    • SUM
    • XMLAGG

    The composite aggregate functions that can be computed from the simple aggregate functions are:

    • SUM(x)
    • COUNT(x)
    • SUM(CAST(x AS DOUBLE))
    • SUM(CAST(x AS DOUBLE) * CAST(x AS DOUBLE))
    • VAR_SAMP(x)
    • VAR_POP(x)
    • VARIANCE(x)
    • STDDEV_SAMP(x)
    • STDDEV_POP(x)
    • STDDEV(x)

    The following statistical aggregate functions:

    • COVAR_SAMP(y,x)
    • COVAR_POP(y,x)
    • CORR(y,x)
    • REGR_AVGX(y,x)
    • REGR_AVGY(y,x)
    • REGR_SLOPE(y,x)
    • REGR_INTERCEPT(y,x)
    • REGR_R2(y,x)
    • REGR_COUNT(y,x)
    • REGR_SXX(y,x)
    • REGR_SYY(y,x)
    • REGR_SXY(y,x)

    can be computed from the following simple aggregate functions:

    • SUM(y1)
    • SUM(x1)
    • COUNT(x1)
    • COUNT(y1)
    • SUM(x1*y1)
    • SUM(y1*x1)
    • SUM(x1*x1)
    • SUM(y1*y1)

    where x1 = CAST(IFNULL(x, x,y) AS DOUBLE)) and y1 = CAST(IFNULL(y,y,x) AS DOUBLE).

Example1: Matching select-project-join views

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' ) );
Example 2: Matching grouped-select-project-join views

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, hence it is advisable to store only simple aggregate functions in the materialized views.

The materialized view V_quantity, below, pre-computes 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;
Example 3: Matching complex queries

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_ship_date, 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_ship_date<V_Ship_date_date> JNLO ( so<SalesOrdersKey> JH V_Canada<V_Canada_state> ) ] ] ] : GrByS[V_quantity<seq> ]. 

CREATE MATERIALIZED VIEW V_ship_date 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_ship_date;
CREATE INDEX V_ship_date_date ON V_ship_date( 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_ship_date.ID, V_ship_date.Description, 
  V_ship_date.Quantity, V_ship_date.ShipDate, 
  so.CustomerID, V_Canada.CompanyName,
  SUM( V_ship_date.Quantity ) OVER ( PARTITION BY V_ship_date.ProductID
                                     ORDER BY V_ship_date.ShipDate
                                     ROWS BETWEEN UNBOUNDED PRECEDING
                                     AND CURRENT ROW ) AS cumulative_qty
 FROM V_ship_date
  LEFT OUTER JOIN ( SalesOrders so JOIN V_Canada
   ON ( V_Canada.ID = so.CustomerID AND V_Canada.State = 'ON' ) )
  ON ( V_ship_date.ID = so.ID )
 WHERE V_ship_date.ShipDate >= '2000-01-01' 
  AND V_ship_date.ShipDate <= '2000-12-31' 
  AND V_ship_date.Quantity >
    ( SELECT SUM( V_quantity.q_sum ) / SUM( V_quantity.q_count ) 
       FROM V_Quantity
       WHERE V_Quantity.year = 2000 )
 FOR READ ONLY;
Example 4: Matching materialized views with OUTER JOINs

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:

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_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;
Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0