Materialization

Views and table expressions cannot always be merged. In certain cases, DB2® materializes the view or table expression

Begin program-specific programming interface information.

Introductory concepts
In the following example, DB2 performs materialization of the view or table expression, which is a two step process.
  1. The fullselect that defines the view or table expression is executed against the database, and the results are placed in a temporary copy of a result table.
  2. The statement that references the view or table expression is then executed against the temporary copy of the result table to obtain the intended result.
Whether materialization is needed depends upon the attributes of the referencing statement, or logically equivalent referencing statement from a prior merge, and the attributes of the fullselect that defines the view or table expression.

Example

Look at the following statements:

View defining statement
CREATE VIEW VIEW1 (VC1,VC2) AS
  SELECT SUM(C1),C2 FROM T1
    GROUP BY C2;
View referencing statement
SELECT MAX(VC1)
  FROM VIEW1;

Column VC1 occurs as the argument of an aggregate function in the view referencing statement. The values of VC1, as defined by the view-defining fullselect, are the result of applying the aggregate function SUM(C1) to groups after grouping the base table T1 by column C2. No equivalent single SQL SELECT statement can be executed against the base table T1 to achieve the intended result. You cannot specify that aggregate functions be applied successively.

End program-specific programming interface information.

When views and nested table expressions are materialized

DB2 uses materialization to satisfy a reference to a view or table expression when aggregate processing is involved (such grouping, aggregate functions, and distinct operations).This processing is indicated by the defining fullselect, with either aggregate processing indicated by the statement references the view or table expression, or by the view or table expression that participates in a join. For views and table expressions that are defined with set operators, DB2 can often distribute aggregate processing, joins, and qualified predicates to avoid materialization.

The following table indicates some cases in which materialization occurs. DB2 can also use materialization in statements that contain multiple outer joins, outer joins that combine with inner joins, or merges that cause a join of greater than 15 tables.

Table 1. Cases when DB2 performs view or table expression materialization. Each "X" indicates a case of materialization.
SELECT FROM view or table expression uses...1 View definition or table expression2 uses GROUP BY View definition or table expression2 uses DISTINCT View definition or table expression2 uses Aggregate function View definition or table expression2 uses Aggregate function DISTINCT View definition or table expression2 uses UNION View definition or table expression2 uses UNION ALL4
Joins 3 X X X X X  
GROUP BY X X X X X  
DISTINCT   X   X X  
Aggregate function X X X X X X
Aggregate function DISTINCT X X X X X  
SELECT subset of view or table expression columns   X     X  
Notes:
  1. If the view is referenced as the target of an insert, update, or delete operation to satisfy the view reference. Only updatable views can be the target in insert, update, and delete operations.

    An SQL statement can reference a particular view multiple times where some of the references can be merged and some must be materialized.

  2. If a SELECT list contains a host variable in a table expression, then materialization occurs. For example:
    SELECT C1 FROM
   (SELECT :HV1 AS C1 FROM T1) X;

    If a view or nested table expression is defined to contain a user-defined function, and if that user-defined function is defined as NOT DETERMINISTIC or EXTERNAL ACTION, then the view or nested table expression is always materialized.

  3. Additional details about materialization with outer joins:
    • If a WHERE clause exists in a view or table expression, and it does not contain a column, materialization occurs.
      SELECT X.C1 FROM
   (SELECT C1 FROM T1
     WHERE 1=1) X LEFT JOIN T2 Y
                  ON X.C1=Y.C1;
    • If the outer join is a full outer join and the SELECT list of the view or nested table expression does not contain a standalone column for the column that is used in the outer join ON clause, then materialization occurs. 
      SELECT X.C1 FROM
   (SELECT C1+10 AS C2 FROM T1) X FULL JOIN T2 Y
                  ON X.C2=Y.C2;
    • If the SELECT list of a view or nested table expression contains no column, materialization occurs. 
      SELECT X.C1 FROM
   (SELECT 1+2+:HV1. AS C1 FROM T1) X LEFT JOIN T2 Y
                  ON X.C1=Y.C1;
    • Start of changeIf certain conditions are met, materialization can be avoided when a left or right outer join contains the following types of expressions: CASE, COALESCE, or VALUE.

      If the preserved-row view or table expression (the left side of a left join) contains a CASE, COALESCE, or VALUE expression, the view or table expression is materialized only if the expression is referenced in an ON or WHERE clause predicate outside of the table expression. A reference in the select list to the CASE, COALESCE, or VALUE expression on the preserved-row side does not cause materialization.

      However, if the null-supplied view or table expression (the right side in a left join) contains a CASE, COALESCE, or VALUE expression, the view or table expression is materialized if the expression is referenced as a predicate, or in the select list of the outer query.

      For example, consider the following statement:

      SELECT A.C1, B.C1, A.C2, B.C2
FROM T1 ,(SELECT COALESCE(C1, 0) AS C1 ,C2 FROM T2 ) A 
	LEFT OUTER JOIN
	(SELECT COALESCE(C1, 0) AS C1 ,C2 FROM T3 ) B 
	ON A.C2 = B.C2
WHERE T1.C2 = A.C2;

      A is the preserved table expression of a left outer join. Because A.C1 is not referenced by any predicate, materialization can be avoided for table expression A.

      B is the null-supplied table expression of the left outer join. Because B.C1 is referenced in the select list for the statement, table expression B must be materialized. If any predicate contained a reference to B.C1, that would also require materialization of table expression B.

      End of change
  4. DB2 cannot avoid materialization for UNION ALL in all cases. Some of the situations in which materialization occurs includes:
    • When the view is the operand in an outer join for which nulls are used for non-matching values, materialization occurs. This situation happens when the view is either operand in a full outer join, the right operand in a left outer join, or the left operand in a right outer join.
    • If the number of tables would exceed 225 after distribution, then distribution does not occur, and the result is materialized.
  5. For INTERSECT and EXCEPT set operators, the EXPLAIN information might help to determine if the view is materialized.

End program-specific programming interface information.