Can somebody help me understanding the difference between view,materialized view and snapshot

oracle

Can somebody help me understanding the difference between view,materialized view and snapshot?

Best Answer

snapshot is an old and deprecated term for a materialized view. They're exactly the same thing, snapshot is just an older term.

A view is a stored query. It consumes (effectively) no space, just the space required to store the text of the query in the data dictionary. When you query the view, the stored query is executed. The results, therefore, will always be up to date.

A materialized view is a both a stored query and a segment. The stored query is executed and the results are materialized into the segment. That means that you can do things like index the materialized view. It also means that if you query the materialized view, you may see stale data (depending on how you have configured the materialized view to refresh itself). Depending on a variety of factors (whether query rewrite is enabled, whether stale data is tolerated, whether the materialized view is up to date, whether dimensions exist), materialized views can be used by Oracle automatically to speed up queries that do not directly reference the materialized view. This is particularly useful when you're building a data warehouse and want to be able to pre-aggregate data for various reports.