IBM Support

Query Re-write generally does no matching for OUTER JOIN

Technote (FAQ)


Why isn't an MQT table used in the explain plan for a query with (LEFT / RIGHT) OUTER JOIN ?


Even if "set current refresh age any" is used prior to creating the explain plan for MQT, for LEFT / RIGHT OUTER JOIN no MQT matching is generally done. One may see the following message in the explain output:

Diagnostic Details: EXP0057W The following MQT or statistical view was
not considered for rewrite matching because it was
based on recursive VIEWS or contained SQL
constructs other than SELECT, GROUP BY and UNION

Potential workarounds to this problem:

1. Check to see if the OUTER JOIN can be converted to an INNER JOIN by query re-write. An outer join construct is eligible of being transformed to inner join (ie. in the presence of RI relationship between the outer and inner tables, or when the inner stream has null rejection predicate applied in outer query block). Sometimes an outer join can even be removed when it can prove the inner stream has no join expansion nature and it has no consumer in the outer query block.

2. If the application has flexibility to change the query statement and create view, there is a nice DB2 feature to consider as of DB2 v9.7. It is called "View MQT". The usage is simple:

a) Create a view using the original MQT definition query statement
ie. create view v_for_mqt1 as (select a.c3 c3, a.c4 c4 from a left outer join b on a.c1=b.c1 and a.c2 = b.c2);

b) Create view MQT. A view MQT definition must be "select * from <view_name>"
ie. create table mqt1 as (select * from v_for_mqt1) data initially deferred refresh deferred enable query optimization;

c) Change query SQL statement. Search for the sub-query block that exactly matches view definition, replace the sub-query to directly reference the view.
To the end user, except the restriction on MQT definition query form, the view MQT is same as regular MQT - they are maintained the same way. The only different is the internal MQT matching. In addition to regular MQT matching, the view MQT matching has an additional processing - compiler searches the view (ie. v_for_mqt1) name in the query statement and replaces that view with the view MQT. Such "match" is based on the string of the view name. OUTER JOIN as well as other complex constructs for which MQT matching is not supported today is allowed in the view definition and hence MQT definition.

Related information

Materialized Query Table Restrictions

Document information

More support for: DB2 for Linux, UNIX and Windows
OTHER - Uncategorised

Software version: 9.7, 10.1, 10.5

Operating system(s): AIX, HP-UX, Linux, Solaris, Windows

Reference #: 1662887

Modified date: 18 November 2015