Materialized View vs. Tables: What are the advantages?
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.
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.
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.
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.
The materialized view will stay synchronized with the base relations on which it depends.
If the materialized view is updatable, when you modify the materialized view, it will also modify the base relation on which it depends.