Materialized view evaluation

Materialized view evaluation involves determining which of the existing materialized views can 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).

Copyright © 2010, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.0