Materialized View — [Notes]

3 min readSep 9, 2022

In computing, a materialized view is a database object that contains the results of a query. For example, it may be a local copy of data located remotely, or may be a subset of the rows and/or columns of a table or join result, or may be a summary using an aggregate function.
The process of setting up a materialized view is sometimes called materialization.[1] This is a form of caching the results of a query, similar to memoization of the value of a function in functional languages, and it is sometimes described as a form of precomputation.[2][3] As with other forms of precomputation, database users typically use materialized views for performance reasons, i.e. as a form of optimization.[4]

//OracleCREATE MATERIALIZED VIEW MV_MY_VIEW
REFRESH FAST START WITH SYSDATE
   NEXT SYSDATE + 1
     AS SELECT * FROM <table_name>;

Cost of MV

you pay for materialized views with memory — need physical space to store results
MV will give back the old result (if not refreshed manually or using DB auto refresh)

complete refreshes
→ Complete refreshes rerun the query underlying a materialized view to completely recalculate and replace the stored results.
→ Just like queries against tables and views, every complete refresh will cost = the cost of original query.
→ will have to decide when to perform a refresh: when someone queries the view? Once an hour? Once a week?
→ You will want to choose a cadence that does not force the database to do unnecessary work.
→ For example, you don't want to pay cost of query to recompute if there are no new entry. In real world systems, users will have to choose a refresh schedule that maintains a careful balance between data freshness requirements and acceptable query costs.
incremental refreshes.
→ keep materialized views up to date by only performing work only on data that has changed.
→ To return to our example, instead of rescanning each row of the table to calculate the sum, an incrementally maintained materialized view would only do the following work:
→ will only pay a fraction of cost proportional to the size of the changed data.
→ And, because this refresh mechanism is triggered by changes in the underlying data, you can be certain that your database is refreshing your view no more than necessary.

COMPLETE REFRESH:
   SUM(30,10,50,20)-> 110INCREMENTAL REFRESH:
   SUM(90, 20) -> 110

One of the biggest benefits of using a materialized view is that Oracle takes care of keeping the data in sync.
→ If you have a separate aggregate table, you are responsible for keeping the data synchronized. That generally requires a reasonable amount of code and a decent amount of testing and most organizations manage to make mistakes that leave holes that cause the aggregate table to get out of sync.
→ This is particularly true when you try to implement incremental refreshes of the aggregate table.
Another major benefit is that, depending on the settings, Oracle can use query rewrite to use materialized views when users issue queries against base tables.
→ So, for example, if you have a bunch of existing reports against a detail table that produces daily, monthly, and yearly aggregate results, you can create a materialized view on the base table that aggregates the data at a daily level and the optimizer can utilize that materialized view for all your existing queries.
→ This makes it much easier to optimize reporting workloads in a data warehouse without trying to go and rewrite dozens of reports to use your new aggregate table or to mess with DBMS_ADVANCED_REWRITE to force your own rewrites of the queries.