Views and Materialized

Differences between views and materialized 

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. Base on Rowid

Another difference between View vs materialized view is that, when we create view using any table,  rowid of view is same as original table but in case of Materialized view rowid is different.

5. 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)