Merge processing

In merge processing, a statement that references a view or table expression is combined with the fullselect that defined the view or table expression. This combination creates a logically equivalent statement. This equivalent statement is executed against the database.

Begin program-specific programming interface information. The merge process is more efficient than materialization.

Important: The merge process and the MERGE statement are not related concepts, and must not be confused.

Example: opportunity for merge processing

Consider the following statements, one of which defines a view, the other of which references the view:

View-defining statement:                 View referencing statement:
 
CREATE VIEW VIEW1 (VC1,VC21,VC32) AS     SELECT VC1,VC21
  SELECT C1,C2,C3 FROM T1                  FROM VIEW1
    WHERE C1 > C3;                         WHERE VC1 IN (A,B,C);

The fullselect of the view-defining statement can be merged with the view-referencing statement to yield the following logically equivalent statement:

Merged statement:
 
SELECT C1,C2 FROM T1
  WHERE C1 > C3 AND C1 IN (A,B,C);

Merge processing for correlated table expressions

Db2 can perform merge processing for correlated table expressions.

SELECT *
	FROM T1,
		TABLE(
			SELECT T1.C2 from T3 AS T2
			WHERE T1.C1 = T2.C1
			) AS X;

Db2 does not materialize the table expression in this query. The expression is merged into the parent query block, and the query is rewritten as the following query:

SELECT * 
FROM T1, T3 AS T2
WHERE T1.C1 = T2.C1

Merge processing for views and table expressions with subqueries on outer joins

Db2 can avoid materialization for queries that have table expressions and views either side of left and right outer joins. The kind of merge processing that Db2 can use depends on which side of the outer join contains the view or table expression, and whether the query contains a subquery.

For example, the following query uses a left outer join and contains reference to a view on the left side of the join:

CREATE VIEW V1
	AS SELECT C1, C2
			FROM T3
			WHERE T3.C1 IN (SELECT T4.C1
										FROM T4 WHERE T4.C2 = T3.C2
										GROUP BY T4.C1);
SELECT T2.C1, T2.C2, T1.C1,T2.C2
FROM V1 AS T1
	LEFT OUTER JOIN
	T2 ON T1.C1= T2.C1
	WHERE (T1.C2 IN('712' , '713', '714'));

In such cases, where the view or table expression is on the preserved row side of the of the join, Db2 does not materialize the view so that selective predicates such as T1.C2 IN('712' , '713', '714') can be applied earlier and reduce the size of the join.

Similarly, if the view or table expression is on the null-supplying side of a left or right outer join, and the following conditions are also true, Db2 merges the table expression:

The view or table expression contains a subquery.
The view or table expression contains references to only a single table.

For example, Db2 merges the table expression for the following query:

SELECT *
FROM T1
	LEFT OUTER JOIN
		(SELECT *
			FROM T2
			WHERE T2.C1 = (SELECT MAX(T3.C1) FROM T3 )
		) TE
	ON T1.C1 = TE.C1;

Db2 merges the table expression by converting the subquery predicate into a before-join predicate to avoid materialization. For example:

SELECT *
FROM T1
	LEFT OUTER JOIN
	T2 as TT 
	ON TT.C1 = (SELECT MAX(TTT.C1) 
							FROM T3 AS TTT)
							AND T1.C1 = TT.C1;

Merge processing in statements with CASE, VALUE, NULLIF, IFNULL, and COALESCE expressions

If there are CASE, VALUE, NULLIF, IFNULL, or COALESCE expressions on the preserved side of an outer join, Db2 can merge the view or table instead of materializing the view or table.

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

In this case, Db2 merges table expression A, but materializes table expression B to apply the COALESCE operation before supplying nulls.

More examples

The following statements show another example of when a view and table expression can be merged:

SELECT * FROM V1 X
  LEFT JOIN
    (SELECT * FROM T2) Y ON X.C1=Y.C1
       LEFT JOIN T3 Z ON X.C1=Z.C1;

Merged statement:

SELECT * FROM V1 X 
  LEFT JOIN 
    T2 ON X.C1 = T2.C1
      LEFT JOIN T3 Z ON X.C1 = Z.C1;

End program-specific programming interface information.