Databases Reference
In-Depth Information
•
Tip 8. Materialized views need indexing too!
Materialized views, the best ones, are
generally useful to multiple queries. That often means that the materialized view will
not be a perfect match for a query, and may require indexing of its own.
•
Tip 9. Help the query compiler find matching materialized views.
Materialized
view routing is complex enough. In order to help the query compiler find the materi-
alized views you create give the compiler as much information as possible by doing
the following:
- Keep statistics up to date on the materialized views (some database do this auto-
matically, not all)
- Define refential integrity- Make FK columns NOT NULL
- Include FK columns in the materialized view
- Define functional dependencies (if the database supports it)
- Consider using GROUPING SETS, ROLLUP or CUBE
A few words on why RI is so helpful. RI can tell the database that a join to the dimen-
sion table in the materialized view does not add or delete rows. The aggregation is the
same with or without the join to the dimension table so the materialized view can be
used. If enforced RI is not possible consider informational constraints, if the database
supports it.
•
Tip 10: Avoid problematic materialized view designs that make routing hard.
The fol-
lowing clauses are notoriously hard for a DBMS to match for materialzed view routng
and should be avoided in materialized view design unless the materialized view is
designed as an exact match for a query: EXISTS, NOT EXISTS, SELECT DIS-
TINCT. For SELECT DISTINCT materialized view consider rewriting it to a
GROUP BY materialized view.
Secondly, avoid correlated predicates within materialized view design. Simple cases can
usually be handled but generally these are challenging for materialized view routing.
5.7
Summary
Materialized views are the results of a query stored to disk. Effectively a materialized
view caches calculations, permitting the reuse of the results, which leads to faster query
responses. The number of possible views is typically extremely large, requiring the data-
base designer to pursue the selection of a strategic set of materialized views. A good heu-
ristic is to focus on frequent queries, which also consume large amounts of disk I/O.
Each query naturally maps to a view. Product graphs and lattice structures can capture
the relationships between views, facilitating the choice of views to materialize. Good
candidates to consider for materialization are the natural views of frequent queries, and
also common ancestors of those views. The star schema is often a good model to follow,
multiplying the applicability of the fact table to a larger family of queries. The selection
of materialized views typically follows a greedy approach, selecting the most advanta-
