Using EXPLAIN to determine when materialization occurs

Rows that describe the access path for both steps of the materialization process, for each reference to a view or table expression that is materialized, appear in the PLAN_TABLE.

Begin program-specific programming interface information. These rows describe the access path used to formulate the temporary result indicated by the defining fullselect of the view, and they describe the access to the temporary result as indicated by the referencing statement. The defining fullselect can also refer to views or table expressions that need to be materialized.

When Db2 chooses materialization, TNAME contains the name of the view or table expression, and TABLE_TYPE contains a W. A value of Q in TABLE_TYPE for the name of a view or nested table expression indicates that the materialization was virtual and not actual. (Materialization can be virtual when the view or nested table expression definition contains a UNION ALL that is not distributed.) When Db2 chooses merge, EXPLAIN data for the merged statement appears in PLAN_TABLE; only the names of the base tables on which the view or table expression is defined appear.

Example

Consider the following statements, which define a view and reference the view:

  View defining statement: 

  CREATE VIEW V1DIS (SALARY, WORKDEPT) as 
    (SELECT DISTINCT SALARY, WORKDEPT FROM DSN8810.EMP) 
   
  View referencing statement:

  SELECT * FROM DSN8810.DEPT
    WHERE DEPTNO IN (SELECT WORKDEPT FROM V1DIS)

The following table shows a subset of columns in a plan table for the query.

Table 1. Plan table output for an example with view materialization
QBLOCKNO	PLANNO	QBLOCK_ TYPE	TNAME	TABLE_ TYPE	METHOD
1	1	SELECT	DEPT	T	0
2	1	NOCOSUB	V1DIS	W	0
2	2	NOCOSUB		?	3
3	1	NOCOSUB	EMP	T	0
3	2	NOCOSUB		?	3

Notice how TNAME contains the name of the view and TABLE_TYPE contains W to indicate that Db2 chooses materialization for the reference to the view because of the use of SELECT DISTINCT in the view definition.

Example

Consider the following statements, which define a view and reference the view:

  View defining statement: 

  CREATE VIEW V1NODIS (SALARY, WORKDEPT) as 
    (SELECT SALARY, WORKDEPT FROM DSN8810.EMP) 
   
  View referencing statement:

  SELECT * FROM DSN8810.DEPT
     WHERE DEPTNO IN (SELECT WORKDEPT FROM V1NODIS)

If the VIEW is defined without DISTINCT, Db2 chooses merge instead of materialization. In the sample output, the name of the view does not appear in the plan table, but the table name on which the view is based does appear.

The following table shows a sample plan table for the query.

Table 2. Plan table output for an example with view merge
QBLOCKNO	PLANNO	QBLOCK_ TYPE	TNAME	TABLE_ TYPE	METHOD
1	1	SELECT	DEPT	T	0
2	1	NOCOSUB	EMP	T	0
2	2	NOCOSUB		?	3

When Db2 avoids materialization in such cases, TABLE_TYPE contains a Q to indicate that Db2 uses an intermediate result that is not materialized, and TNAME shows the name of this intermediate result as DSNWFQB(xx), where xx is the number of the query block that produced the result. End program-specific programming interface information.