Can somebody help me understanding the difference between view,materialized view and snapshot?
Can somebody help me understanding the difference between view,materialized view and snapshot
oracle
Related Question
- Difference between commit and checkpoint
- The exact difference between Oracle (+) and comma separate tables and join tables
- Snapshot too old from materialized view refresh job
- Difference between master site and materialized view site in oracle
- Materialized view became INVALID and USER_MVIEWS.COMPILE_STATE becomes ‘NEEDS_COMPILE’
- The proper procedure for fixing materialized view replication
- How to force a Nested Loop join on Materialized View and Table in Oracle 12c
- Materialized View: What is the Update Trigger property
Best Answer
snapshot
is an old and deprecated term for amaterialized 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.