Materialized View vs. Tables: What are the advantages?

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.

Solution 1

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.

Solution 2

They're basically equivalent, but the MV has various options for automatically refreshing the data, which not only improve ease of maintenance but also, in some cases, efficiency, since it can track changes by row.

Solution 3

Materialized views can be refreshed - they are snapshots of data taken at regular intervals.

Your second statement is just a one time deal - data gets inserted into Table at that moment. Further changes to the original data do not get reflected in the table.

Solution 4

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.

Solution 5

the big advantage of a Materialized View is extremely fast retrieval of aggregate data, since it is precomputed and stored, at the expense of insert/update/delete. The database will keep the Materialized View in sync with the real data, no need to re-invent the wheel, let the database do it for you.

View more solutions

Comments

[codeObserver]

The first point doesnt sound like an advantage. Also it seems like it is copied from here without any reference itknowledgeexchange.techtarget.com/itanswers/…

[Keith Davies]

@codeObserver I work in a data warehouse. If I could keep the user view static until a very quick replacement of the content (i.e. I can rebuild the warehouse without disturbing the users, then flip a switch and they see new data) I would.

[Amin Shojaei]

Are they really sync? or we should refresh them for latest changes?