The Difference Between Views and Materialized Views in R12

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 access 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 the result being a little out of date, or your application data has more queries than updates (as in a BI/data warehousing system)