Using EXPLAIN to determine UNION, INTERSECT, and EXCEPT activity and query rewrite

For each reference to a view or table expression that is defined with a UNION ALL operator, Db2 might rewrite the query into a logically equivalent statement with improved performance.

Begin program-specific programming interface information. Db2 rewrites the queries in the following manner:

Distributing qualified predicates, joins, and aggregations across the subselects of UNION ALL. Such distribution helps to avoid materialization. No distribution is performed for UNION, INTERSECT, EXCEPT, INTERSECT ALL, or EXCEPT ALL.
Eliminating unnecessary subselects of a view or table expression that was created by a UNION ALL operation. For Db2 to eliminate subselects, the referencing query and the view or table definition must have predicates that are based on common columns.

The QBLOCK_TYPE column in the plan table indicates set operator activity. If a set operation was used, the column contains one of the values shown in the following table.

Table 1. Meanings of the set operator values for the QBLOCK_TYPE column of PLAN_TABLE
QBLOCK_TYPE value	Set operator
UNION	UNION
UNIONA	UNION ALL
INTERS	INTERSECT
INTERA	INTERSECT ALL
EXCEPT	EXCEPT
EXCEPTA	EXCEPT ALL

When the value of QBLOCK_TYPE is set to UNION, INTERSECT, or EXCEPT, the METHOD column on the same row is set to 3 and the SORTC_UNIQ column is set to 'Y' to indicate that a sort is necessary to remove duplicates. As with other views and table expressions, the plan table also shows when Db2 uses materialization instead of merge.

Example: Consider the following statements, which define a viewby using the UNION ALL operator, reference that view, and demonstrate how Db2 can rewrite the referencing statement.

The statement that defines the view uses data from three tables of weekly data to create the view:

  CREATE VIEW V1 (CUSTNO, CHARGES, DATE) as 
    SELECT CUSTNO, CHARGES, DATE            
    FROM WEEK1                              
    WHERE DATE BETWEEN '01/01/2006' And '01/07/2006' 
  UNION ALL                                          
   SELECT CUSTNO, CHARGES, DATE            
    FROM WEEK2                             
    WHERE DATE BETWEEN '01/08/2006' And '01/14/2006' 
  UNION ALL                                          
    SELECT CUSTNO, CHARGES, DATE           
    FROM WEEK3                             
    WHERE DATE BETWEEN '01/15/2006' And '01/21/2006';

Another statement references the view to find the average charges for each customer in California during the first and third Friday of January 2006:

  SELECT V1.CUSTNO, AVG(V1.CHARGES)             
    FROM CUST, V1                             
    WHERE CUST.CUSTNO=V1.CUSTNO               
      AND CUST.STATE='CA'                     
      AND DATE IN ('01/07/2006','01/21/2006') 
    GROUP BY V1.CUSTNO;

Db2 can rewrite the statement (assuming that CHARGES is defined as NOT NULL):

  SELECT CUSTNO_U, SUM(SUM_U)/SUM(CNT_U)                     
    FROM                                                     
    ( SELECT WEEK1.CUSTNO, SUM(CHARGES), COUNT(CHARGES)      
        FROM CUST, WEEK1                                     
        Where CUST.CUSTNO=WEEK1.CUSTNO AND CUST.STATE='CA'   
              AND DATE BETWEEN '01/01/2006' And '01/07/2006' 
              AND DATE IN ('01/07/2006','01/21/2006')        
        GROUP BY WEEK1.CUSTNO                                
      UNION ALL                                              
      SELECT WEEK3.CUSTNO, SUM(CHARGES), COUNT(CHARGES)      
        FROM CUST,WEEK3                                      
        WHERE CUST.CUSTNO=WEEK3 AND CUST.STATE='CA'          
              AND DATE BETWEEN '01/15/2006' And '01/21/2006' 
              AND DATE IN ('01/07/2006','01/21/2006')        
        GROUP BY WEEK3.CUSTNO                                
    ) AS X(CUSTNO_U,SUM_U,CNT_U)                             
  GROUP BY CUSTNO_U;

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

Table 2. Plan table output for an example with a view with UNION ALL operations
QBLOCKNO	PLANNO	TNAME	TABLE_ TYPE	METHOD	QBLOCK_ TYPE	PARENT_ QBLOCK
1	1	DSNWFQB(02)	Q	0		0
1	2		?	3		0
2	1		?	0	UNIONA	1
3	1	CUST	T	0		2
3	2	WEEK1	T	1		2
4	1	CUST	T	0		2
4	2	WEEK3	T	2		2

Notice how Db2 eliminates the second subselect of the view definition from the rewritten query and how the plan table indicates this removal by showing a UNION ALL for only the first and third subselect in the view definition. The Q in the TABLE_TYPE column indicates that Db2 does not materialize the view. End program-specific programming interface information.

End program-specific programming interface information.