머티리얼라이즈드 뷰

-- PostgreSQL: 머티리얼라이즈드 뷰 생성
CREATE MATERIALIZED VIEW monthly_sales_summary AS
SELECT
    DATE_TRUNC('month', order_date) AS month,
    product_id,
    SUM(quantity) AS total_quantity,
    SUM(amount) AS total_amount,
    COUNT(*) AS order_count
FROM orders
GROUP BY DATE_TRUNC('month', order_date), product_id;

-- 고유 인덱스 추가 (CONCURRENTLY 갱신에 필요)
CREATE UNIQUE INDEX idx_monthly_sales ON monthly_sales_summary(month, product_id);

-- 조회 (매우 빠름 - 이미 계산된 결과)
SELECT * FROM monthly_sales_summary
WHERE month >= '2024-01-01';

-- 수동 갱신
REFRESH MATERIALIZED VIEW monthly_sales_summary;

-- 동시 접근 허용 갱신 (인덱스 필수)
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales_summary;

-- 자동 갱신: cron 또는 pg_cron 확장 사용
-- SELECT cron.schedule('refresh-sales', '0 * * * *',
--   'REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales_summary');

-- Oracle: 자동 갱신 옵션
CREATE MATERIALIZED VIEW mv_daily_sales
REFRESH FAST ON COMMIT  -- 트랜잭션 커밋 시 자동 갱신
AS SELECT date, SUM(amount) FROM orders GROUP BY date;