Performance of merge versus materialization
The merge process, which is not related to MERGE statements in SQL, performs better than materialization.
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.
| 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) |