Performance of merge versus materialization

The merge process, which is not related to MERGE statements in SQL, performs better than materialization.

Begin program-specific programming interface information.For materialization, Db2 uses a table space scan to access the materialized temporary result. Db2 also uses default statistics for the columns of the materialized temporary result, which might impact the selected access path.Db2 materializes a view or table expression only if it cannot use merge processing.

Materialization is a two-step process with the first step resulting in the formation of a temporary result. The smaller the temporary result, the more efficient is the second step. To reduce the size of the temporary result, Db2 attempts to evaluate certain predicates from the WHERE clause of the referencing statement at the first step of the process rather than at the second step. Only certain types of predicates qualify. First, the predicate must be a simple Boolean term predicate. Second, it must have one of the forms shown in Table 1.

Table 1. Predicate candidates for first-step evaluation
Predicate Example
COL op constant V1.C1 > hv1
COL IS (NOT) NULL V1.C1 IS NOT NULL
COL (NOT) BETWEEN constant AND constant V1.C1 BETWEEN 1 AND 10
COL (NOT) LIKE constant (ESCAPE constant) V1.C2 LIKE 'p\%%' ESCAPE '\'
COL IN (list) VI.C2 IN (a,b,c)
Note: Where "op" is =, <>, >, <, <=, or >=, and constant is either a host variable, constant, or special register. The constants in the BETWEEN predicate need not be identical.
Implied predicates generated through predicate transitive closure are also considered for first step evaluation. End program-specific programming interface information.