Materialized View와 Query Rewrite: Refresh·정합성·운영 비용

핵심 요약

Materialized View(MV)는 Query 결과를 실제 Segment에 저장해 비싼 Join·집계 작업을 미리 계산하는 Schema Object입니다. 사용자는 Detail Table을 조회하는 원래 SQL을 그대로 실행할 수 있고, Query Rewrite가 가능하고 비용상 유리하면 Optimizer가 투명하게 MV를 사용하는 실행계획을 선택할 수 있습니다.

일반 View
→ SQL 정의를 저장하고 조회 시 Base Object를 실행

Materialized View
→ Query 결과를 물리적으로 저장
→ Refresh로 Base Table 변경을 반영
→ Query Rewrite로 원래 SQL을 MV Access로 바꿀 수 있음

MV는 조회 비용을 줄이는 대신 Storage, Refresh, Base Table DML, 정합성 관리 비용을 추가합니다. 다음 네 가지는 별도로 판단합니다.

1. MV를 생성할 수 있는가?
2. Fast·PCT·LPCT Refresh가 가능한가?
3. 특정 Query가 MV로 Rewrite될 수 있는가?
4. Optimizer가 실제로 MV Plan을 선택하는가?

이 이론의 범위

SQLP SQL 고급 활용 및 튜닝 → 고급 SQL 활용 → Materialized View와 Query Rewrite 범위에서 사전 계산, Refresh 방식, Rewrite Integrity, Capability 진단과 운영 Trade-off를 다룹니다.

학습 목표

이 이론을 학습한 뒤에는 다음을 설명할 수 있어야 합니다.

1. 일반 View와 Materialized View의 저장 구조 차이를 설명한다.
2. MV가 Join·Aggregation Query를 빠르게 할 수 있는 이유를 이해한다.
3. Complete·Fast·Force Refresh와 On Demand·On Commit의 차이를 구분한다.
4. Fast Refresh와 Query Rewrite가 서로 다른 기능임을 설명한다.
5. Query Rewrite가 성립하기 위한 기본 조건을 확인한다.
6. QUERY_REWRITE_ENABLED와 QUERY_REWRITE_INTEGRITY의 역할을 구분한다.
7. ENFORCED, TRUSTED, STALE_TOLERATED의 정확성 Trade-off를 설명한다.
8. DBMS_MVIEW.EXPLAIN_REWRITE와 EXPLAIN_MVIEW의 목적을 구분한다.
9. 실제 실행계획에서 MV 사용 여부를 검증한다.
10. BUILD IMMEDIATE·BUILD DEFERRED와 Rewrite 가능 시점을 구분한다.
11. ON STATEMENT와 PCT·LPCT Refresh의 목적을 설명한다.
12. Nested MV의 Refresh 순서와 실제 운영 비용을 평가한다.

1. 일반 View와 Materialized View

일반 View

CREATE VIEW monthly_sales_v AS
SELECT product_id,
       TRUNC(sale_date, 'MM') AS sale_month,
       COUNT(*)               AS sale_count,
       SUM(amount)            AS total_amount
FROM   sales
GROUP BY product_id, TRUNC(sale_date, 'MM');

일반 View는 SELECT 정의를 저장합니다. 조회할 때 Optimizer가 View와 Base Table을 포함한 실행계획을 만들며, View 결과 자체를 별도 Segment에 항상 저장하지는 않습니다.

Materialized View

CREATE MATERIALIZED VIEW monthly_sales_mv
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT product_id,
       TRUNC(sale_date, 'MM') AS sale_month,
       COUNT(*)               AS sale_count,
       SUM(amount)            AS total_amount
FROM   sales
GROUP BY product_id, TRUNC(sale_date, 'MM');

MV는 결과 행을 물리적으로 저장합니다.

SALES 수억 행 Scan·Group
→ PRODUCT_ID·월별 결과 수십만 행을 MV에 저장
→ 반복 보고서는 작은 MV를 읽을 가능성

MV에도 Index와 Statistics를 만들 수 있습니다. Query Rewrite 여부와 관계없이 MV를 직접 조회하는 것도 가능하지만, Application SQL이 MV 이름에 직접 의존하면 MV 구조 변경이 Application 변경으로 이어질 수 있습니다.

BUILD IMMEDIATE와 BUILD DEFERRED

BUILD IMMEDIATE
→ CREATE 시점에 정의 Query를 실행해 MV를 채움

BUILD DEFERRED
→ 구조만 만들고 이후 Complete Refresh로 데이터를 채움

BUILD DEFERRED MV는 데이터가 채워지기 전에는 Query Rewrite 대상으로 사용할 수 없습니다. 운영 배포에서는 초기 Build 시간이 업무 시간에 미치는 영향과 첫 Complete Refresh 시점을 계획합니다.

2. Query Rewrite의 기본 원리

사용자는 Base Table을 조회합니다.

SELECT product_id,
       TRUNC(sale_date, 'MM') AS sale_month,
       SUM(amount)            AS total_amount
FROM   sales
WHERE  sale_date >= DATE '2026-01-01'
AND    sale_date <  DATE '2027-01-01'
GROUP BY product_id, TRUNC(sale_date, 'MM');

Optimizer는 다음 후보를 비교할 수 있습니다.

후보 1: SALES Detail Table을 읽고 월별 집계
후보 2: MONTHLY_SALES_MV에서 필요한 월만 조회

Query Rewrite는 SQL Text가 MV 정의와 완전히 같을 때만 가능한 기능으로 제한되지 않습니다. Join, Filter, Grouping Column, Aggregate가 호환되고 MV 결과로 Query 답을 만들 수 있으면 일반 Rewrite를 검토할 수 있습니다.

예를 들어 월별 MV를 다시 합산해 상품별 연간 합계를 만들 수 있습니다.

SELECT product_id,
       SUM(amount) AS total_amount
FROM   sales
WHERE  sale_date >= DATE '2026-01-01'
AND    sale_date <  DATE '2027-01-01'
GROUP BY product_id;

MV가 Query보다 더 세밀한 Grain을 가지고 필요한 데이터와 Aggregate를 포함한다면 Roll-up Rewrite가 가능할 수 있습니다.

Query Grain
→ PRODUCT_ID별 연간

MV Grain
→ PRODUCT_ID·월별

월별 MV를 다시 SUM
→ 연간 Query 답변 가능

AVG는 단순히 월별 평균을 다시 평균내면 가중치가 달라질 수 있습니다. 일반적으로 재집계에 필요한 SUM과 COUNT를 MV가 보유해야 합니다.

전체 AVG
= SUM(월별 SUM) / SUM(월별 COUNT)

MIN·MAX처럼 재집계 가능한 Aggregate와 COUNT(DISTINCT ...)처럼 제약이 큰 Aggregate를 구분하고 EXPLAIN_MVIEW·EXPLAIN_REWRITE로 확인합니다.

3. Query Rewrite가 성립하는 기본 조건

다음 조건을 순서대로 확인합니다.

3.1 MV가 Rewrite 대상으로 활성화되어 있는가

ALTER MATERIALIZED VIEW monthly_sales_mv
ENABLE QUERY REWRITE;

3.2 Session에서 Query Rewrite가 활성화되어 있는가

ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;

대표 값은 다음과 같습니다.

3.3 MV가 Query를 답할 수 있는가

다음 요소가 호환되어야 합니다.

• 필요한 Detail Table과 Join
• Filter 조건
• Grouping Grain
• 반환 Column
• Aggregate 계산 가능성
• Constraint·Dimension 관계
• 데이터 타입과 표현식

3.4 Integrity 수준이 MV 사용을 허용하는가

Fresh 상태와 Constraint 신뢰 수준을 확인합니다.

3.5 MV 사용 Plan이 적절한가

QUERY_REWRITE_ENABLED=TRUE에서는 Rewrite된 후보와 원래 후보의 Cost를 비교합니다. MV Statistics가 없거나 오래됐거나 MV가 지나치게 크면 Optimizer가 Detail Plan을 선택할 수 있습니다.

FORCE도 Query를 답할 수 없는 MV를 강제로 사용하거나 Integrity 규칙을 무시하는 설정은 아닙니다.

Rewrite Eligibility
→ 의미·Constraint·Integrity 조건

Plan Selection
→ Cost·Statistics·Access Path

Rewrite 가능
≠
실제 Rewrite 선택

4. Refresh 방식

Base Table이 변경되면 MV 저장 결과를 갱신해야 합니다.

4.1 Complete Refresh

MV 정의 Query를 다시 수행해 전체 결과를 재생성합니다.

장점: 적용 가능한 MV 범위가 넓고 이해하기 쉬움
비용: 대용량 Base Table 재Scan·Join·Group 비용

BEGIN
    DBMS_MVIEW.REFRESH(
        list   => 'MONTHLY_SALES_MV',
        method => 'C'
    );
END;
/

4.2 Fast Refresh

Base Table의 변경분을 이용해 MV를 증분 갱신합니다.

변경된 Row 정보
→ 필요한 Delta 계산
→ MV에 증분 반영

Fast Refresh는 MV Log와 MV 정의의 제약을 충족해야 합니다. 필요한 Log Column, Primary Key·ROWID, Aggregate, Join 구조에 따라 가능 여부가 달라집니다.

대표 증분 Refresh 방법입니다.

Partition Maintenance Operation이 발생한 경우 PCT가 핵심 증분 방법이 될 수 있습니다. 기능 이름을 보고 적용 가능하다고 단정하지 않고 EXPLAIN_MVIEW Capability를 확인합니다.

4.3 Force Refresh

Fast Refresh를 시도하고 조건을 만족하지 못하면 Complete Refresh를 사용할 수 있는 방식입니다.

FAST 가능 → FAST
FAST 불가 → COMPLETE

운영 시간과 허용 부하를 예측하려면 실제로 어느 방식이 사용됐는지 확인합니다.

4.4 On Demand·On Commit·On Statement

ON COMMIT과 ON STATEMENT는 최신성을 높이지만 각각 Commit 또는 DML 응답시간을 늘릴 수 있습니다. ON STATEMENT는 지원되는 Fast Refreshable MV에 제한되며 모든 MV에서 사용할 수 있는 것은 아닙니다.

조회 최신성 증가
↕
Base DML·Commit 지연 증가

쓰기 빈도가 높은 OLTP Table에서는 조회 이득과 Transaction 비용을 함께 검토합니다. Refresh 실패 시 자동 Refresh가 중단될 수 있으므로 Alert·Trace와 수동 복구 절차를 준비합니다.

5. Fast Refresh 가능과 Query Rewrite 가능의 차이

두 기능은 목적이 다릅니다.

가능한 조합은 다양합니다.

Fast Refresh 가능 + Query Rewrite 가능
Fast Refresh 가능 + Query Rewrite 불가
Complete Refresh만 가능 + Query Rewrite 가능
둘 다 불가

Fast Refresh가 되더라도 Query가 MV Grain과 맞지 않으면 Rewrite되지 않을 수 있습니다. Complete Refresh 방식의 MV도 Query Rewrite에 사용할 수 있습니다.

Fast Refresh Capability
→ MV 자체를 Delta로 갱신 가능한가?

Rewrite Capability
→ Query 결과를 MV로 계산 가능한가?

Actual Rewrite
→ 현재 Statistics와 Cost에서 MV Plan이 선택됐는가?

6. QUERY_REWRITE_INTEGRITY

QUERY_REWRITE_INTEGRITY는 Optimizer가 어떤 관계와 MV 상태를 신뢰할지 정합니다.

ALTER SESSION SET QUERY_REWRITE_INTEGRITY = ENFORCED;

STALE_TOLERATED는 단순한 성능 Option이 아니라 얼마나 오래된 결과를 허용할지에 대한 업무 정책입니다. 주문 결제·재고처럼 최신 정합성이 필요한 Query와 전일 기준 분석 보고서는 허용 기준이 다를 수 있습니다.

TRUSTED에서는 Database가 강제 검증하지 않은 RELY Constraint와 Dimension 관계를 신뢰할 수 있습니다. 선언과 실제 데이터가 다르면 Rewrite 결과가 틀릴 수 있으므로 데이터 품질 검증 책임이 운영 조직에 있습니다.

ENFORCED
→ 검증·강제된 관계 중심

TRUSTED
→ 선언된 관계를 신뢰

STALE_TOLERATED
→ Stale 결과까지 허용

7. MV 상태와 운영 정보 확인

SELECT mview_name,
       rewrite_enabled,
       staleness,
       refresh_mode,
       refresh_method,
       last_refresh_type,
       last_refresh_date
FROM   user_mviews
WHERE  mview_name = 'MONTHLY_SALES_MV';

주요 항목은 다음과 같습니다.

DDL에 FAST를 적었다는 사실보다 실제 Refresh 결과와 소요시간을 운영 로그로 확인합니다.

다음 항목도 운영 환경에서 함께 확인할 수 있습니다.

• COMPILE_STATE: MV가 정상적으로 Compile된 상태인가?
• USE_NO_INDEX: Refresh 시 기본 Index 정책과 실제 Index 상태
• Dependency·Nested MV의 Freshness
• Refresh Job 실패와 Alert·Trace 기록

STALENESS='UNKNOWN'이나 Compile 문제가 있으면 단순히 마지막 Refresh 시각만 보고 Fresh하다고 판단하지 않습니다.

8. Rewrite와 Refresh 가능성 진단

8.1 DBMS_MVIEW.EXPLAIN_REWRITE

특정 Query가 특정 MV로 Rewrite될 수 있는지와 실패 이유를 분석합니다.

BEGIN
    DBMS_MVIEW.EXPLAIN_REWRITE(
        query        => 'SELECT product_id,
                                TRUNC(sale_date, ''MM'') sale_month,
                                SUM(amount)
                         FROM sales
                         GROUP BY product_id,
                                  TRUNC(sale_date, ''MM'')',
        mv           => 'MONTHLY_SALES_MV',
        statement_id => 'REWRITE_TEST_01'
    );
END;
/

설명 결과를 저장할 Rewrite Table이 준비되어 있어야 하며, 메시지에서 다음과 같은 원인을 확인합니다.

• 필요한 Column·Aggregate가 MV에 없음
• Grouping Grain이 호환되지 않음
• Constraint·Dimension 관계 부족
• 표현식이 호환되지 않음
• MV가 Rewrite 대상으로 비활성화됨
• Integrity 수준이 허용하지 않음

8.2 DBMS_MVIEW.EXPLAIN_MVIEW

MV 자체의 기능 가능성을 분석합니다.

BEGIN
    DBMS_MVIEW.EXPLAIN_MVIEW('MONTHLY_SALES_MV');
END;
/

Fast Refresh, PCT·LPCT, 일반 Query Rewrite 같은 Capability와 제한 이유를 확인하는 데 사용합니다.

EXPLAIN_MVIEW는 이미 존재하는 MV뿐 아니라 생성하려는 정의 Query의 Capability를 사전에 분석하는 데도 사용할 수 있습니다. REFRESH FORCE로 생성하면 Fast 불가가 Complete로 숨겨질 수 있으므로 설계 단계에서 별도 진단합니다.

EXPLAIN_REWRITE
→ 이 Query가 MV로 Rewrite되는가?

EXPLAIN_MVIEW
→ 이 MV가 어떤 Refresh·Rewrite Capability를 가지는가?

9. 실제 실행계획에서 Rewrite 확인

Query Rewrite는 Application SQL Text를 바꾸지 않고 내부에서 일어납니다. SQL 실행 후 실제 Cursor Plan을 확인합니다.

SELECT /*+ GATHER_PLAN_STATISTICS */
       product_id,
       TRUNC(sale_date, 'MM') AS sale_month,
       SUM(amount)            AS total_amount
FROM   sales
GROUP BY product_id, TRUNC(sale_date, 'MM');

SELECT *
FROM TABLE(
    DBMS_XPLAN.DISPLAY_CURSOR(
        NULL,
        NULL,
        'ALLSTATS LAST +OUTLINE +NOTE'
    )
);

실행계획에서 다음을 확인합니다.

• MONTHLY_SALES_MV Object가 실제 Access되었는가?
• Detail SALES Access가 제거되거나 줄었는가?
• Rewrite 관련 Note가 있는가?
• MV Plan과 Detail Plan의 A-Rows, Buffers, Reads, A-Time 차이는 얼마인가?
• MV Statistics가 수집되어 있는가?

REWRITE Hint는 특정 MV를 Rewrite 후보로 제한하거나 시험할 때 사용할 수 있고, NOREWRITE는 해당 Statement의 Rewrite를 막을 수 있습니다. Hint가 존재해도 의미적 호환성과 Integrity 조건은 확인해야 합니다.

비교 실험은 동일한 Bind·Session Parameter·Statistics 조건에서 수행합니다.

Detail Plan
→ NOREWRITE 또는 Rewrite 비활성 비교

MV Plan
→ Rewrite 활성·후보 MV 확인

비교
→ 결과 Row·Checksum
→ A-Rows·Buffers·Reads·CPU·A-Time

10. MV 설계의 비용과 Trade-off

MV로 절감되는 Query 비용만 보면 운영 부담을 놓칠 수 있습니다.

얻는 이점

• 반복 Join·Aggregation 제거
• Detail Table Scan량 감소
• 보고서 응답시간 단축
• Application SQL 변경 없이 Rewrite 가능

추가되는 비용

• MV Segment와 Index Storage
• MV Log Storage와 Base DML 기록 비용
• Refresh CPU·I/O·TEMP·Redo·Undo
• On Commit 사용 시 Transaction 지연
• Refresh 실패·Stale 상태 모니터링
• 통계 수집과 배포 절차

좋은 후보

자주 실행되는 Query
+ 원래 Join·집계 비용이 큼
+ 결과 Grain이 Detail보다 훨씬 작음
+ 여러 Query가 같은 사전 계산을 재사용
+ 허용 가능한 Refresh 주기가 명확함

효과가 제한될 수 있는 후보

거의 실행되지 않는 Query
결과가 Detail과 비슷하게 큼
Base DML이 매우 빈번함
Query마다 Filter·Grain이 크게 다름
실시간 정합성이 반드시 필요함

Partitioned Fact Table에서는 PCT·LPCT와 Partition 단위 Refresh가 운영 비용을 줄일 수 있지만, Partition 구조와 MV 정의가 Capability 조건을 만족하는지 별도 검증해야 합니다.

Nested Materialized View

다른 MV를 Base로 하는 Nested MV는 Refresh 순서가 중요합니다.

하위 MV가 Stale
→ 상위 MV를 먼저 Refresh
→ Stale 하위 내용 기준으로 상위가 갱신될 수 있음

DBMS_MVIEW.REFRESH에서 nested => TRUE를 사용하거나 Dependency 순서를 보장하는 Refresh 절차를 설계합니다. 개별 MV Refresh는 Refresh Group의 Transaction Consistency를 깨뜨릴 수 있으므로 관련 MV가 같은 기준 시점을 가져야 하는지 확인합니다.

Refresh 실행 방식

Complete Refresh는 설정에 따라 기존 Segment를 유지하며 DML하는 Atomic 방식과 별도 구조를 만든 뒤 교체하는 Out-of-Place 방식 등을 검토할 수 있습니다. 가용성·Undo·Redo·추가 Storage·Index 재구축 비용이 달라지므로 운영 창에서 실측합니다.

11. 적용 판단 순서

1. 반복적으로 비싼 Query와 공통 Grain을 찾는다.
2. MV가 저장할 한 행의 의미와 Key를 정의한다.
3. 필요한 Join·Grouping·Aggregate를 설계한다.
4. BUILD IMMEDIATE·DEFERRED와 초기 적재 방식을 정한다.
5. Complete·Fast·Force·PCT·LPCT 가능성을 분석한다.
6. On Demand·On Commit·On Statement와 허용 Staleness를 정한다.
7. QUERY_REWRITE_ENABLED·INTEGRITY 정책을 확인한다.
8. EXPLAIN_MVIEW로 MV Capability를 확인한다.
9. EXPLAIN_REWRITE로 대표 Query의 호환성을 확인한다.
10. MV와 Detail Object의 Statistics를 수집한다.
11. Nested Dependency와 Refresh 순서를 검증한다.
12. 실제 Cursor Plan과 Refresh·DML·Storage 비용을 함께 측정한다.

혼동하기 쉬운 판단