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

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.

Answer 2

The difference between table and MV is with table , you can do DML operations which will be seen by other users whereas the changes you do to MV will not be available to others until you update your database server.

MV has another advantage when you build MV based on multiple tables using complex queries, the users when using MV the performance increases drastically.

Answer 3

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.

Answer 4

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.