Materialized View vs. Tables: What are the advanta

Question

It's clear to me why a materialized view is preferable over just querying a base table. What is not so clear is the advantage over just creating another table with the same data as the MV. Is the only advantage to the MV really just the ease of creation/maintenance?

Isn't an MV equivalent to a table with matching schema and an INSERT INTO using the MVs SELECT statement?

Meaning, you can create an MV as follows

CREATE MATERIALIZED VIEW ... AS
SELECT * FROM FOO;

And you can create an equivalent table:

CREATE TABLE bar (....);
INSERT INTO bar 
SELECT * FROM FOO;

Not to say that ease of creation / maintenance isn't enough of an advantage, I just want to make sure I'm not missing anything.

Answer 1

In additition to the already mentionned advantages:

• dynamic query rewriting (in short, the DB optimizer knows how the MV is created, so it can reuse it to optimize other queries),
• optional, automatic, possibly incremental refresh,

I'd like to mention:

• some materialized views can be written to, which updates the source table (for instance joins with primary keys can be written to, on the opposite if the materialized view is the result of a group by it can't be written to)
• the DB server retains the query that created the data and can rerun it. If you create a table, you need an external tool (possibly just a custom script) to rerun the query whenever a refresh is needed / asked by the user. (I work for a company developing a tool that does that and much more).

Answer 2

In addition to the other answers (because I haven't seen it), I would say that although they both use up space, the materialized view is logically normalized, whereas the extra table is logically denormalized. If this is something that is not a temporary one-off, you will have to remember to update the second table whenever you update the base table.

Answer 3

Materialize views are in fact best choice over tables where aggregations are required regularly to show updated result sets. We can use Materialized view other than Data ware housing in Inventory modules for calculating daily, weekly, monthly stock with closing balance rather than using complex queries every time , we can make materialized views to fetch such results in no time.

Answer 4

Dynamic query rewriting. Materialized views define not only relationships, but also allow you to precompute expensive joins and aggregations. The optimizer is smart enough to use the MV to fetch relevant data even if the MV isn't explicitly used in the query (given DB settings, etc).

Your question was tagged as Oracle, but MSSQL also does similar tricks.

Answer 5

1. The materialized view will stay synchronized with the base relations on which it depends.

2. If the materialized view is updatable, when you modify the materialized view, it will also modify the base relation on which it depends.

Answer 6

1) Speeding up write operations: Since indexes can be created on materialized views, reading from them is very fast. Note that if you create an index on a table that includes a lot of writes, index maintenance overhead tends to slow down the write process. To avoid this you can create a materialize view and create indexes on them. These indexes can be maintained in the background and does not adversely affect table write operations.

2) Speeding read operations: Complex joins; pivots that take ages to run can be speed up by creating indexes on the materialized views. This becomes very handy in most reporting scenarios.