Materilaized View[MView]

Materialized View(MView)

• 구체화된 뷰, 데이터베이스에서 쿼리를 미리 계산하여 저장하는 물리적인 테이블

• 복잡한 쿼리로 인해 성능 문제를 완화하기 위해 사용

Materialized View vs. View

1. 데이터 조회 소요 시간

   분류	내용
   Materialized View	물리적인 테이블, 데이터를 사전에 계산하여 테이블에 저장 미리 계산된 결과를 바로 조회함으로 빠르게 데이터를 조회할 수 있음
   View	가상 테이블, 쿼리 실행시 원본 테이블을 접근하여 데이터 조회 데이터 조회 속도가 원본 테이블 성능이나 쿼리(SQL)문의 복잡도에 따라 달라짐

2. 데이터 갱신

   분류	내용
   Materialized View	이미 계산된 결과값이 물리적으로 저장, 원본테이블이 변경되면 해당 변경된 내용을 수동으로 반영해줘야 함 (옵션에 따라 제한적인 자동도 가능)
   View	쿼리 실행할 때마다 원본 테이블 접근하므로 원본 테이블이 변경되면 변경사항이 즉시 반영된 결과를 조회할 수 있음

3. 데이터 저장 유무

   분류	내용
   Materialized View	실제 데이터를 저장, 쿼리결과를 사전에 저장하며 실시간으로 업데이트 되지 않음. (옵션에 따라 제한적인 자동 가능)
   View	가상 테이블, 뷰를 생성하는 쿼리가 실행될 때 실시간으로 결과 생성 (테이블에 별도로 저장X)

Materialized View 예시

• Materialized View를 생성하기 전 Materialized View Log를 생성해줘야 함(FAST옵션 사용하고자 한다면)

• Materialized View Log는 원본 데이터에서 변화가 생긴 ROW를 모니터링하고 Refresh가 되어야할 데이터의 Primary Key를 관리함.

① Materialized View Log 생성

CREATE MATERILAIZED VIEW LOG <DB테이블명>
 -- PRIMARY KEY : 데이터 식별 값을 로그에 포함
 -- SEQUENCE : 변경 사항이 발생할 때마다 순차 번호 부여
 -- ROWID : Oracle 데이터베이스에 특정 행(ROW)의 물리적 위치를 나타내는 고유 식별자
WITH PRIMARY KEY, ROWID, SEQUNECE
 -- INCLUDING NEW VALUES : 갱신된 데이터를 저장(새로운 값 저장됨)
 -- INCLUDING NEW VALUES 생략된 경우, LOG에는 보통 ROWID와 PRIMARY KEY등만 저장되어 신규 값을 원본 테이블에서 조회해야 함.
INCLUDING NEW VALUES; -- FAST의 경우 필수 (새로운 값이 입력될 때 제대로 작동을 못하기 때문)

② Materialized View 생성

CREATE MATERIALIZED VIEW MY_MVIEW_TABLE
[BUILD IMMEDIATE] or [BUILD DEFERRED]
REFRESH
[FORCE] or [COMPLETE] or [FAST] or [NEVER]
[ON DEMAND] or [ON COMMIT]
ENABLE QUERY REWRITE
AS [SELECT 쿼리문]

• BUILD IMMEDIATE : MView 생성과 동시에 데이터들이 생성

• BUILD DEFERRED : MView 생성은 하지만, 추후에 데이터들이 생성(최초 조회시 생성)

• REFRESH : MView 데이터가 언제 어떻게 Refresh 되는지 옵션

  • REFRESH 설정 옵션 1

    • FORCE : FAST REFRESH가 가능하면 적용하고, 그렇지 않으면 COMPLETE

    • FAST : 데이터가 입력/변경될 때마다 점진적으로 Refresh (MView Log사용)

    • COMPLETE : 데이터 전체가 Refresh (MView Log 사용X)

    • NEVER : Refresh가 발생하지 않음

  • REFRESH 설정 옵션 2

    • ON COMMIT : 원본 테이블에 COMMIT이 일어날 때 Refresh

    • ON DEMAND : 사용자가 Refresh를 실행한 경우에만 Refresh

  • ENABLE QUERY REWRITE : 옵티마이저가 SQL문을 실행할 때 MView를 쿼리 재작성 대상으로 포함(성능 최적화)

  • DISABLE QUERY REWRITE : 옵티마이저가 SQL문 실행할 때 MView를 사용하지 않음

FAST & INCLUDING NEW VALUES 옵션 처리 프로세스

• REFRESH FAST ON DEMAND & INCLUDING NEW VALUES의 경우

  

• REFRESH FAST ON COMMIT & INCLUDING NEW VALUES 의 경우

  

• 기존 데이터가 변경되는 경우 : 기존 값과 변경 값 두개를 저장하여 찾고 Log에 ROW 2개가 쌓임

• 신규 데이터가 추가되는 경우 : 기존 데이터가 없으므로 Log에 ROW가 1개 쌓임

• INCLUDING NEW VALUES 설정을 안하면 FAST의 경우 새로운 값은 Log에 쌓이질 않으니 제대로 동작할 수가 없음. (FAST와 INCLUDING NEW VALUES는 세트)

Materialized View 장점 & 단점

○ 장점

• 빠른 조회 : 미리 계산된 데이터를 저장하여 조회 속도 빠름

• 복잡한 쿼리 최적화 : 조인 집계 등이 포함된 복잡한 쿼리 실행 부담 감소

• 네트워크 부하 감소 : 원격 DB 데이터를 로컬에 저장하여 트래픽 절감

• 백업 및 보고서 활용 : 주기적으로 갱신하여 데이터 분석 및 보고서 생성에 유리

○ 단점

• 데이터 갱신 필요 : 원본 데이터 변경 시 새로 갱신해야 함. (ON COMMIT 미적용시)

• 추가된 저장공간 필요 : 데이터 조회결과를 물리적으로 저장하기 때문에 디스크 공간 소모

• 갱신 비용 증가 : 실시간 데이터 변경이 많으면 성능 저하 발생

• 복잡한 유지 보수 : 자동 갱신 설정 시 성능 튜닝 필요. (성능 튜닝X - > 과부화)

  • 예시

      -- 1.
      CREATE MATERIALIZED VIEW MV_CTEST1  
      REFRESH FAST ON COMMIT -- 원본 테이블이 변경될때(커밋될 떄) 마다 변경된 데이터만 
      AS  
      SELECT * FROM X_MV_TABLE_TEST;
      
      -- 2.
      CREATE MATERIALIZED VIEW MV_CTEST2  
      REFRESH COMPLETE  --  데이터 전체 갱신
      START WITH SYSDATE  
      NEXT TRUNC(SYSDATE + 1) + 6/24  -- 새벽 6시 마다
      AS  
      SELECT * FROM X_MV_TABLE_TEST;

• 제한된 쿼리 ⭐ : MView 생성 시 일부 복잡한 SQL 구문은 지원X

  • 밑의 표 외에도 지원여부에 따라 추가된 내용이 더 있을 수 있음

" Q. FAST 옵션을 사용할 떄 JOIN이 안된다. 이유는?"

• INNER JOIN

  

• OUTER JOIN