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.

Answer 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.

Answer 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.