What are Materialized views ?

Materialized views are a database objects that contains the result of a query . They are similar to regular views , which are virtual tables representing the result of a query. However , unlike regular views that dynamically retrieve data from the underlying tables each time they are accessed , materialized views store the query result as a physical table( Data is actually stored on disk or memory , making access to the data much faster at the expense of using more storage space).

Physical Tables(Materialized views) : its about the actual , persistent storage of data in the database . They are where the data resides on disk.

Virtual Tables (Views) : are an abstraction layer over physical tables, providing a dynamic , calculated presentation of data without storing it themselves.

Key characteristic's of Materialized views -

1) Performance : Because the results are precomputed and stored , accessing data from a materialized view can be significantly faster then running the original query , especially for complex queries involving joins , aggregations or calculations.

2) Data Freshness : The data in materialized view can become stale since it does not automatically update when the underlying data changes . Therefore materialized views need to be refreshed periodically , either manually or on a schedule , to ensure they reflect the current state of the underlying data .

3) Storage : Materialized views consumes more storage space then regular views because they store a copy of the query results.

4) Use Cases: They are particularly useful in scenarios where the data does not change frequently , but the query is complex and time consuming such as in data warehousing and reporting applications.

Refresh Strategies :

• Manual Refresh: The materialized view is updated only when explicitly instructed to do so.
• On Commit Refresh : The materialized view is refreshed automatically whenever a transaction that modifies the underlying data is committed. This ensures the materialized view always contains up-to-date data but can impact the performance of write operations.
• On Demand or Schedule Refresh : The materialized view is refreshed on demand or according to a predefined schedule. This is a common approach when its acceptable for the view to contain slightly stale data and balances the need for up-to-date information with system performance.