Plain View vs Materialized View: and When to Use Each

-- Plain view: always fresh, computed on every access
CREATE VIEW branch_deposit_totals AS
SELECT
    b.branch_id,
    b.branch_name,
    SUM(a.balance) AS total_deposits
FROM branches b
JOIN accounts a ON b.branch_id = a.branch_id
WHERE a.status = 'ACTIVE'
GROUP BY b.branch_id, b.branch_name;

-- Every query re-runs the aggregation
SELECT * FROM branch_deposit_totals
WHERE branch_name = 'DELHI';

-- Materialized view: stored snapshot, fast reads
CREATE MATERIALIZED VIEW mv_branch_deposit_totals AS
SELECT
    b.branch_id,
    b.branch_name,
    SUM(a.balance) AS total_deposits
FROM branches b
JOIN accounts a ON b.branch_id = a.branch_id
WHERE a.status = 'ACTIVE'
GROUP BY b.branch_id, b.branch_name;

-- Reads hit stored data, no re-aggregation
SELECT * FROM mv_branch_deposit_totals
WHERE branch_name = 'DELHI';

-- Full refresh: re-runs the entire query, blocks reads briefly
REFRESH MATERIALIZED VIEW mv_branch_deposit_totals;

-- Concurrent refresh (PostgreSQL): no read blocking, requires a unique index
CREATE UNIQUE INDEX idx_mv_branch ON mv_branch_deposit_totals(branch_id);
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_branch_deposit_totals;

package com.bank.service;

import java.sql.Connection;
import java.sql.Statement;
import java.util.logging.Logger;

public class MaterializedViewRefreshService {

    private static final Logger LOG = Logger.getLogger(
        MaterializedViewRefreshService.class.getName());

    private final Connection connection;

    public MaterializedViewRefreshService(Connection connection) {
        this.connection = connection;
    }

    public void refreshBranchTotals() {
        String sql = "REFRESH MATERIALIZED VIEW CONCURRENTLY mv_branch_deposit_totals";
        try (Statement stmt = connection.createStatement()) {
            long start = System.currentTimeMillis();
            stmt.execute(sql);
            long elapsed = System.currentTimeMillis() - start;
            LOG.info("Refreshed mv_branch_deposit_totals in " + elapsed + " ms");
        } catch (Exception e) {
            LOG.severe("Refresh failed: " + e.getMessage());
        }
    }
}

-- Plain view: always current but slow on 100M rows
SELECT * FROM branch_deposit_totals;  -- re-aggregates every call

-- Materialized view: fast but may be 15 minutes stale
SELECT * FROM mv_branch_deposit_totals;  -- reads cached result

-- Compliance screen: balance must never be stale
CREATE VIEW compliance_account_status AS
SELECT account_id, holder_name, balance, status, kyc_verified
FROM accounts
WHERE kyc_verified = FALSE;

-- Always shows the real-time list of unverified accounts
SELECT * FROM compliance_account_status;

-- Executive dashboard: reads thousands of times per hour,
-- aggregation over millions of rows, 15-minute staleness acceptable
CREATE MATERIALIZED VIEW mv_executive_dashboard AS
SELECT
    b.branch_name,
    a.account_type,
    COUNT(*) AS account_count,
    SUM(a.balance) AS total_balance,
    AVG(a.balance) AS avg_balance
FROM accounts a
JOIN branches b ON a.branch_id = b.branch_id
WHERE a.status = 'ACTIVE'
GROUP BY b.branch_name, a.account_type;

-- Schedule refresh every 15 minutes via cron or pg_cron
-- SELECT cron.schedule('*/15 * * * *',
--   'REFRESH MATERIALIZED VIEW CONCURRENTLY mv_executive_dashboard');

-- MySQL: simulate a materialized view with a summary table
CREATE TABLE summary_branch_deposits (
    branch_id    INT PRIMARY KEY,
    branch_name  VARCHAR(100),
    total_balance DECIMAL(18,2),
    refreshed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Refresh procedure
DELIMITER //
CREATE PROCEDURE refresh_branch_deposits()
BEGIN
    TRUNCATE TABLE summary_branch_deposits;
    INSERT INTO summary_branch_deposits (branch_id, branch_name, total_balance)
    SELECT b.branch_id, b.branch_name, SUM(a.balance)
    FROM branches b
    JOIN accounts a ON b.branch_id = a.branch_id
    WHERE a.status = 'ACTIVE'
    GROUP BY b.branch_id, b.branch_name;
END //
DELIMITER ;

CALL refresh_branch_deposits();

-- Wrong: full refresh blocks reads on a large materialized view
REFRESH MATERIALIZED VIEW mv_executive_dashboard;  -- blocks readers

-- Correct: add a unique index and use CONCURRENTLY
CREATE UNIQUE INDEX idx_mv_exec ON mv_executive_dashboard(branch_name, account_type);
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_executive_dashboard;  -- no read block

-- Oracle: incremental (fast) refresh with a materialized view log
CREATE MATERIALIZED VIEW LOG ON accounts
WITH ROWID, PRIMARY KEY (account_id, branch_id, balance)
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW mv_branch_totals
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
AS
SELECT branch_id, SUM(balance) AS total_balance
FROM accounts
GROUP BY branch_id;

-- Fast refresh applies only the changes since the last refresh
EXEC DBMS_MVIEW.REFRESH('mv_branch_totals', 'F');