Materialized View vs. Tables: What are the advantages? Materialized View vs. Tables: What are the advantages? oracle oracle

Materialized View vs. Tables: What are the advantages?


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.