How Db2 considers automatic query rewrite

In general, Db2 considers automatic query rewrite at the query block level. A read-only, dynamic query can contain multiple query blocks.

For example, it might contain a subselect of UNION or UNION ALL, temporarily materialized views, materialized table expressions, and subquery predicates. Db2 processes the query without automatic query rewrite if the query block is or contains any of the following items:

• A fullselect in the UPDATE SET statement.
• A fullselect in the INSERT statement.
• A fullselect in the materialized query table definition in the REFRESH TABLE statement.
• An outer join.
• A query block that contains user-defined scalar or table functions with the EXTERNAL ACTION attribute or the NON-DETERMINISTIC attribute, or with the built-in function RAND.
• Parameter markers

If none of these items exist in the query block, Db2 considers automatic query rewrite. Db2 analyzes the query block in the user query and the fullselect in the materialized query table definition to determine if it can rewrite the query. The materialized query table must contain all of the data from the source tables (in terms of both columns and rows) that Db2 needs to satisfy the query. For Db2 to choose a rewritten query, the rewritten query must provide the same results as the user query. (Db2 assumes the materialized query table contains current data.) Furthermore, the rewritten query must offer better performance than the original user query.

Db2 performs a sophisticated analysis to determine whether it can obtain the results for the query from a materialized query table:

• Db2 compares the set of base tables that were used to populate the materialized query table to the set of base tables that are referenced by the user query. If these sets of tables share base tables in common, the query is a candidate for query rewrite.
• Db2 compares the predicates in the materialized query table fullselect to the predicates in the user query. The following factors influence the comparison:
  • The materialized query table fullselect might contain predicates that are not in the user query. If so, Db2 assumes that these predicates might have resulted in discarded rows when the materialized query table was refreshed. Thus, any rewritten query that makes use of the materialized query table might not give the correct results. The query is not a candidate for query rewrite.
    Exception: Db2 behavior differs if a predicate joins a common base table to an extra table that is unique to the materialized query table fullselect. The predicate does not result in discarded data if you define a referential constraint between the two base tables to make the predicate lossless. However, the materialized query table fullselect must not have any local predicates that reference this extra table.

    For an example of a lossless predicate, see Example 2 under Automatic query rewrite—complex examples.

  • Referential constraints on the source tables are very important in determining whether automatic query rewrite uses a materialized query table.
  • Predicates are much more likely to match if you code the predicate in the user query so that it is the same or very similar to the predicate in the materialized query table fullselect. Otherwise, the matching process might fail on some complex predicates.

    For example, the matching process between the simple equal predicates such as COL1 = COL2 and COL2 = COL1 succeeds. Furthermore, the matching process between simple equal predicates such as COL1 * (COL2 + COL3) = COL5 and COL5 = (COL3 + COL2) * COL1 succeeds. However, the matching process between equal predicates such as (COL1 + 3) * 10 = COL2 and COL1 * 10 + 30 = COL2 fails.

  • The items in an IN-list predicate do not need to be in exactly the same order for predicate matching to succeed.
• Db2 compares GROUP BY clauses in the user query to GROUP BY clauses in the materialized query table fullselect. If the user query requests data at the same or higher grouping level as the data in the materialized query table fullselect, the materialized query table remains a candidate for query rewrite. Db2 uses functional dependency information and column equivalence in this analysis.
• Db2 compares the columns that are requested by the user query with the columns in the materialized query table. If Db2 can derive the result columns from one or more columns in the materialized query table, the materialized query table remains a candidate for query rewrite.Db2 uses functional dependency information and column equivalence in this analysis.
• Db2 examines predicates in the user query that are not identical to predicates in the materialized query table fullselect. Then, Db2 determines if it can derive references to columns in the base table from columns in the materialized query table instead. If Db2 can derive the result columns from the materialized query table, the materialized query table remains a candidate for query rewrite.

If all of the preceding analyses succeed, Db2 rewrites the user query. Db2 replaces all or some of the references to base tables with references to the materialized query table. If Db2 finds several materialized query tables that it can use to rewrite the query, it might use multiple tables simultaneously. If Db2 cannot use the tables simultaneously, it chooses which one to use according to a set of rules.

After writing the new query, Db2 determines the cost and the access path of that query. Db2 uses the rewritten query if the estimated cost of the rewritten query is less than the estimated cost of the original query. The rewritten query might give only approximate results if the data in the materialized query table is not up to date.