These recommendations describe base table design strategies
that might increase the performance and eligibility of your materialized
query tables.
Procedure
To make your base tables work well
with materialized query tables:
- Define referential integrity as either ENFORCED or NOT
ENFORCED whenever possible.
- Define an index as unique if it is truly unique.
- Define all base table columns as NOT NULL if possible,
so that COUNT(x) is the same as COUNT(*).
Then
you do not need to include COUNT(x) for each nullable
column x in a materialized query table definition.
If necessary, use a special value to replace NULL.
- Emphasize normalized dimension design over denormalized
dimension design in your base tables.
When you use normalized
dimension design, you do not need to include non-primary key columns
in a materialized query table, thereby saving you storage space. Db2 compensates
for the lack of non-primary key columns by deriving these columns
through a re-join of the dimension table. If normalization causes
performance problems, you can define materialized query tables to
denormalize the snowflake dimensions.