Oracle Interview Question - oracle difference between view and materialized view

Summary

In this video, the differences between a View and a Materialized View in SQL are explored, focusing on key aspects such as storage, data retrieval, and refresh behavior. A View is essentially a stored query that does not store data, fetching results directly from the base tables. On the other hand, a Materialized View stores a physical snapshot of the query results, making data retrieval faster but requiring periodic refreshes to sync with the base tables. The video also touches on interview-related questions, providing clear explanations of the practical uses of both in SQL development.

Takeaways

• 😀 A **view** is a virtual table that stores a query, not actual data. It fetches results from base tables at runtime.
• 😀 A **materialized view** is similar to a view, but it stores the query result physically, improving performance for repeated access.
• 😀 Both views and materialized views allow the combination of multiple base tables and can even be based on other views.
• 😀 **Views** do not store data; they only store the query's metadata. The data is fetched dynamically from base tables.
• 😀 **Materialized views** store actual data and do not need to fetch from base tables every time. This improves query performance.
• 😀 Any changes to base tables will be immediately reflected in a **view**, as it runs the query in real-time.
• 😀 **Materialized views** require periodic refreshing to reflect changes from base tables. This refresh can be done using the `DBMS_MVIEW` package.
• 😀 **Views** are ideal for scenarios where real-time data is needed, while **materialized views** are better for performance optimization in scenarios with less frequent updates.
• 😀 The refresh process for materialized views allows them to sync with the base table data, but until refreshed, they show stale data.
• 😀 In an interview, highlighting the performance difference and the need for a refresh process for materialized views will demonstrate a strong understanding of the concept.

Q & A

What is the main difference between a view and a materialized view?

A view is a virtual table that stores a query but no data, fetching live data from the base tables each time it is queried. A materialized view, on the other hand, stores the result of a query physically, providing faster access to data but potentially becoming outdated unless refreshed.

Does a view store data?

No, a view does not store any data. It only stores the SQL query and fetches data from the base tables every time it is queried.

How does a materialized view differ in terms of data storage compared to a view?

A materialized view stores the actual data from the query result in a physical location, while a view does not store data and always fetches the latest data from the base tables.

What happens when you query a view?

When you query a view, the SQL statement associated with the view is executed on the base tables at runtime, and the most current data from the base tables is fetched.

How does querying a materialized view work?

When you query a materialized view, it retrieves the data stored physically within the materialized view, rather than executing the underlying query each time.

What is the major advantage of using a materialized view over a view?

The primary advantage of a materialized view is that it improves query performance by storing the result of the query physically, which eliminates the need to re-execute the query every time it is accessed.

What is the refresh process for a materialized view?

The refresh process for a materialized view involves using the `DBMS_MVIEW.REFRESH` procedure to synchronize the data between the base tables and the materialized view. Without refreshing, the materialized view may become outdated.

When should a view be used over a materialized view?

A view should be used when real-time data consistency is required, and performance is not a critical concern. Views are ideal for scenarios where up-to-the-minute data must be accessed without worrying about refresh delays.

What is the performance difference between a view and a materialized view?

Views tend to have slower performance because the underlying query is executed every time it is accessed. Materialized views, by storing the query result, offer faster access but may have outdated data until refreshed.

Can a materialized view reflect changes to the base table immediately?

No, a materialized view does not reflect changes to the base table immediately. It requires a refresh operation to update the stored data in the materialized view, unlike a view which reflects changes instantly.