By following these recommendations, you might improve the
performance of queries that use materialized query tables.
Procedure
To get better performance from
your materialized query tables:
- Include aggregate functions strategically in the fullselect
of a materialized query table definition:
- Include COUNT(*) and SUM(expression).
- Include SUM(expression*expression)
only if you plan to query VAR(expression), STDDEV(expression),
VAR_SAMP(expression), or STDDEV_SAMP(expression).
- Include COUNT(expression) in addition to COUNT(*)
if expression is nullable.
- Include MIN(expression) and MAX(expression)
if you plan to query them.
- Do not include AVG(expression), VAR(expression),
or STDDEV(expression) directly if you include either
of the following parameter combinations:
- SUM(expression), SUM(expression*expression),
and COUNT(*)
- SUM(expression), SUM(expression*expression),
and COUNT(expression)
Db2 can
derive AVG(expression), VAR(expression),
and STDDEV(expression) from SUM(expression),
SUM(expression*expression),
and the appropriate COUNT aggregate function.
- Include the foreign key of a dimension table in the GROUP
BY clause of a materialized query table definition.
For
example, if you include PGROUP.ID, also include PGROUP.LINEID. Then Db2 can use
the materialized query table to derive a summary at the LINEID level,
without rejoining PGROUP.
- Include all the higher-level columns in the materialized
query table if Db2 does
not know the functional dependency in a denormalized dimension table.
For example, if you include CITY in a GROUP BY clause, also
include STATE and COUNTRY in the clause. Similarly, if you include
MONTH in the GROUP BY clause, also include YEAR in the clause.
- Do not use the HAVING clause in your materialized query
tables.
A materialized query table with a HAVING clause
in its definition has limited usability during query rewrite.
- Create indexes on materialized query tables as you would
for base tables.
