Materialized View Schema Design

Summary management can perform many useful functions, including query rewrite
and materialized view refresh, even if your data warehouse design does not follow
these guidelines. However, you will realize significantly greater query execution
performance and materialized view refresh performance benefits and you will require
fewer materialized views if your schema design complies with these guidelines.
A materialized view definition includes any number of aggregates, as well as any
number of joins. In several ways, a materialized view behaves like an index:

■ The purpose of a materialized view is to increase query execution performance.
■ The existence of a materialized view is transparent to SQL applications, so that a
database administrator can create or drop materialized views at any time without
affecting the validity of SQL applications.
■ A materialized view consumes storage space.
■ The contents of the materialized view must be updated when the underlying
detail tables are modified.

Schemas and Dimension Tables
In the case of normalized or partially normalized dimension tables (a dimension that is stored in more than one table), identify how these tables are joined. Note whether the joins between the dimension tables can guarantee that each child-side row joins with one and only one parent-side row. In the case of denormalized dimensions, determine whether the child-side columns uniquely determine the parent-side (or attribute) columns. These relationships can be enabled with constraints, using the NOVALIDATE and RELY options if the relationships represented by the constraints are guaranteed by other means. Note that if the joins between fact and dimension tables do not support the parent-child relationship described previously, you still gain significant performance advantages from defining the dimension with the CREATE DIMENSION statement. Another alternative, subject to some restrictions, is to use outer joins in the materialized view definition (that is, in the CREATE MATERIALIZED VIEW statement).

You must not create dimensions in any schema that does not satisfy these
relationships. Incorrect results can be returned from queries otherwise.

Posted in: Materialized Views