Materialized Views and Automatic Refresh Mechanisms in Databases

Problem Description
A Materialized View is a special object in a database that improves the performance of complex queries by precomputing and storing query results. Unlike regular views (logical views), materialized views physically store data, but they require addressing the issue of data consistency (i.e., how to synchronize and update the materialized view after changes to the base tables). In interviews, discussions often revolve around its core principles, applicable scenarios, refresh mechanisms (such as incremental refresh and full refresh), and optimization strategies.

Step-by-Step Explanation

1. Basic Concepts of Materialized Views

   • Difference from Regular Views: Regular views do not store data; they execute SQL dynamically each time they are queried. Materialized views persist query results to physical storage, similar to an actual table.
   • Core Value: By precomputing time-consuming operations such as aggregations and multi-table joins, they reduce computational overhead during querying, making them particularly suitable for data warehousing and reporting scenarios.

2. Refresh Mechanisms for Materialized Views

   • Complete Refresh:
     • Process: Clears existing data in the materialized view, re-executes the query defined in its definition, and completely rebuilds the data.
     • Pros and Cons: Simple to implement but resource-intensive for large datasets and may block queries.
   • Fast (Incremental) Refresh:
     • Principle: Records changes (inserts, deletes, updates) to the base tables through logs (e.g., Oracle's materialized view logs) and synchronizes only the changed portions to the materialized view.
     • Conditions: The definition of the materialized view must satisfy constraints for incremental refresh (e.g., must include all primary keys, prohibit certain complex operations).

3. Refresh Trigger Methods

   • Manual Refresh: Triggered actively via commands (e.g., REFRESH MATERIALIZED VIEW mv_name).
   • Automatic Refresh:
     • Time-Based: Automatically refreshes at fixed intervals (e.g., daily at midnight).
     • Transaction-Based: Refreshes immediately upon commit of changes to the base tables (e.g., Oracle's ON COMMIT mode), but this increases transaction latency.
     • Hybrid Mode: Combines time intervals with change thresholds (e.g., refresh only if at least 10 minutes have passed and more than 1000 rows have changed).

4. Key Technologies for Implementing Incremental Refresh

   • Materialized View Logs:
     • Creates a log table on the base table to record unique identifiers of changed rows (e.g., primary keys) and the type of operation.
     • Example: CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID.
   • Incremental Computation Logic:
     • Computes new data (e.g., INSERT INTO mv SELECT ... FROM log) or deletes expired data by associating the logs with the materialized view.
     • Example: If a row is added to the base table, the log records the primary key of that row, and during refresh, that row's data is inserted into the materialized view.

5. Optimization Strategies and Considerations

   • Index Optimization: Create indexes on commonly queried fields of the materialized view to avoid full table scans.
   • Query Rewrite:
     • The database automatically redirects queries to the materialized view (e.g., when querying a base table, if the materialized view's data can satisfy the query, it queries the materialized view directly).
     • Constraints: The query conditions must be covered by the materialized view's definition.
   • Balancing Refresh Frequency: High-frequency refreshes ensure data timeliness but increase system load, while low-frequency refreshes may lead to data staleness.

6. Practical Application Example

   • Scenario: Daily sales statistics report for an e-commerce platform.
   • Steps:
     1. Create a materialized view:

        CREATE MATERIALIZED VIEW daily_sales_mv  
        BUILD IMMEDIATE  
        REFRESH FAST ON COMMIT  
        AS  
        SELECT date, product_id, SUM(amount) AS total_sales  
        FROM sales  
        GROUP BY date, product_id;  
        

     2. Create a base table log to support incremental refresh:

        CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID;  
        

     3. Automatic query rewrite during querying:

        -- Original query  
        SELECT date, SUM(amount) FROM sales WHERE date = '2023-10-01' GROUP BY date;  
        -- The database automatically rewrites this to query daily_sales_mv directly  
        

Summary
Materialized views improve query performance by trading space for time. Their core challenge lies in balancing data timeliness with refresh overhead. During design, it's essential to select a refresh strategy based on business needs and combine it with optimization techniques such as indexing and query rewrite to maximize benefits.