Part 5 of 5 · 4 min read
Exercises
Easy
Easy 1: Name the key difference from an ordinary view (15 minutes)
State the key difference between an ordinary view and a materialized view.
Done when: a materialized view physically storing its result, rather than re-running the query each time, is correctly described.
Hint
A materialized view stores its result physically; an ordinary view re-runs its underlying query on every access.
Easy 2: Name what a refresh does (15 minutes)
State what refreshing a materialized view does.
Done when: re-running the underlying query and replacing the stored result is correctly described.
Hint
It re-runs the underlying query and replaces the materialized view's stored result with the new one.
Easy 3: Name the two sides of the trade-off (15 minutes)
Name the two things a materialized view trades in exchange for faster reads.
Done when: extra storage space and potential staleness between refreshes are both correctly named.
Hint
Extra storage space for the stored result, and potential staleness between refreshes.
Medium
Medium 1: Decide if a materialized view fits a query (25 minutes)
A query joins several large tables and aggregates millions of rows, and is read by a public dashboard thousands of times a day. Decide whether this query is a good candidate for a materialized view, and explain why.
Done when: yes is correctly concluded, citing the combination of high per-run cost and frequent reads as justifying the storage and staleness trade-off.
Hint
Yes, since the query is expensive per run and read very frequently, the storage and staleness cost is worth the savings from skipping recomputation each time.
Medium 2: Decide against a materialized view (25 minutes)
A query is cheap to run and needs to reflect every change the instant it happens, such as a live account balance. Decide whether a materialized view is a good fit, and explain why.
Done when: no is correctly concluded, citing the query's low cost and the need for instant accuracy as reasons an ordinary view or direct query fits better.
Hint
No, since the query is already cheap and needs instant accuracy, which staleness between refreshes would directly undermine.
Medium 3: Choose between full and incremental refresh (30 minutes)
A materialized view over a huge table only has a small number of new rows added each day. Decide whether a full refresh or an incremental refresh, where supported, is the more efficient choice, and explain why.
Done when: incremental refresh is correctly chosen, citing the avoidance of recomputing the entire huge result when only a small number of rows actually changed.
Hint
Incremental refresh, since it avoids recomputing the entire huge result when only a small number of rows have actually changed.
Hard
Hard 1: Explain the risk of relying on a stale materialized view (30 minutes)
A materialized view is refreshed nightly, but a report reader assumes it reflects the current moment. Explain what could go wrong, and how this risk should be communicated.
Done when: the explanation correctly notes the reader could draw conclusions based on data that is up to a full day out of date, and that the view's refresh schedule and staleness should be clearly communicated to anyone relying on it.
Hint
The reader could unknowingly base decisions on data up to a day stale; the refresh schedule and the view's staleness should be stated clearly wherever the view's data is presented.
Hard 2: Weigh a materialized view against query-level optimization (35 minutes)
A slow query is a candidate for either a new sargable index, per this level's earlier pages, or a materialized view. Explain the difference in what each approach actually fixes, and when one might be preferred over the other.
Done when: the explanation correctly notes an index speeds up the live query itself while keeping results always current, whereas a materialized view skips running the query at all in exchange for staleness, making an index generally preferable when the query can be made fast enough directly, and a materialized view preferable when no amount of indexing makes the live query cheap enough for its read frequency.
Hint
An index keeps results always current while speeding up the live query; a materialized view skips the live query entirely in exchange for staleness, so an index is preferable when it alone makes the query fast enough, and a materialized view is preferable when the query stays too expensive for its read frequency even with good indexing.
Hard 3: Design a refresh schedule for a specific workload (40 minutes)
A materialized view powers a dashboard that business users check each morning, and the underlying data changes constantly throughout each business day. Propose a reasonable refresh schedule, and justify the staleness it accepts.
Done when: a reasonable schedule is proposed, such as a refresh overnight before the business day starts, with the justification correctly noting this keeps the view current at the moment users actually check it each morning, accepting staleness only during the day when the dashboard may not be actively consulted.
Hint
A nightly refresh before the business day starts keeps the dashboard current at the moment users actually check it each morning, accepting staleness during the day when the dashboard is not being actively consulted.