Other than the word "view" in their names and the fact that both are defined by an underlying SQL, there is little else common between Oracle views and materialized views – yet they can be a source of great confusion to beginners.
Here’s a summary of the key differences between views and materialized views, to put an end to all mix-ups. By the end of the article, you should be in a position to decide if your specific scenario needs a view, a materialized view, or neither.
1. Moment Of Execution
A view’s SQL is executed at run-time. The results are fetched from the view’s base tables when the view is queried.
A materialized view (called snapshot in older Oracle versions) is a "pre-answered" query – the query is executed when the materialized view is refreshed. Its result is stored in the database and the query only browses the result.
2. Space
A view occupies no space (other than that for its definition in the data dictionary).
A materialized view occupies space. It exists in the same way as a table: it sits on a disk and could be indexed or partitioned.
3. Freshness of Output
A view’s output is built on the fly; it shows real-time data from the base tables being queried.
A materialized view does not reflect real-time data. The data is only as up to date as the last time the materialized view was refreshed.
4. Where To Use
A view is best used when:
You want to hide the implementation details of a complex query
You want to restrict acc
ess to a set of rows/columns in the base tables
A materialized view is best used when:
You have a really big table and people do frequent aggregates on it, and you want fast response
You don’t mind th
e result being a little out of date, or your application data has more queries than updates (as in a BI/data warehousing system)
