Materialized View [MView]

Ga0·2025년 2월 17일

MATERIALIZED VIEW db mView

기타

목록 보기

17/17

Materialized View(MView)

Materialized View는 데이터베이스에서 쿼리를 미리 계산하여 저장하는 물리적인 테이블이다.
복잡한 쿼리를 실행할 때 발생하는 성능 문제를 완화하기 위해 사용된다.
복잡한 쿼리의 경우 실행시간도 오래걸리고 데이터베이스 서버의 부하를 증가시키는 문제가 있는데 MView는 그러한 문제를 해결해준다.

MView vs View

"MView와 View는 같은 View인데 어떻게 다를까?"

1. 데이터 조회 소요시간

View(가상)의 경우 가상 테이블로써 쿼리 실행시 원본 테이블에 접근하여 데이터를 조회하게 된다. 그렇끼 때문에 데이터 조회 속도가 원본 테이블의 성능이나 쿼리(SQL)문의 복잡도에 따라 성능이 달라진다.
MView(물리)의 경우 데이터를 사전에 계산하여 테이블에 저장해두고 해당 Mview를 조회하는 쿼리 실행시 미리 계산된 결과를 바로 조회하기 때문에 빠르게 데이터를 조회할 수 있다.

2. 데이터 갱신

View(가상)의 경우 원본 테이블을 접근하기 때문에 원본 테이블이 변경되면 변경사항이 즉시 반영된 결과값을 뽑아낼 수 있다.
MView(물리)의 경우 이미 계산된 결과값이 물리적으로 저장되어 있는 형태이기때문에 원본 테이블이 변경되면 해당 변경된 내용을 수동으로 반영해줘야 한다.

3. 데이터 저장 유무

View(가상)의 경우 가상의 테이블로 뷰를 생성하는 쿼리가 실행될때 실시간으로 결과를 생성한다. 생성된 결과는 테이블에 별도로 저장하진 않는다.
MView(물리)의 경우 실제로 데이터를 저장하는 테이블로 쿼리 결과를 사전에 저장하며 실시간으로 업데이트 되지 않는다. (쿼리 결과값이 바뀌어야 하면 수동으로 바꿔줘야한다.)

MView 쿼리문(SQL)

1. 생성

MView를 생성하기 전 Materialized View log를 생성해줘야 한다.
MView-log는 원본 데이터에서 변화가 생긴 row를 모니터링하고 Refresh가 되어야 할 데이터의 primary Key를 관리한다.

(1) Materialized View log 생성

CREATE MATERIALIZED VIEW LOG ON MY_DB_TABLE 
-- PRIMARY KEY : 데이터 식별 값을 로그에 포함
-- SEQUENCE : 변경 사항이 발생할 때마다 순차 번호 부여
-- ROWID : Oracle 데이터베이스에서 특정 행(row)의 물리적인 위치를 나타내는 고유한 식별자(디스크의 어느 블록(Block)에 위치하는지를 식별하는 값 ; 16바이트) : 빠른 행 식별 및 갱신
WITH PRIMARY KEY, ROWID, SEQUENCE 
-- INCLUDING NEW VALUES : 갱신된 데이터 저장 (새로운 값이 저장됨)
-- INCLUDING NEW VALUES 생략될 경우 : LOG에는 보통 ROWID랑 PRIMARY KEY 등만 저장되어 MView 갱신 시 신규 값들을 다시 원본테이블에서 조회해야 함 (FAST REFRESH ; 빠른 갱신을 효율적으로 이용하려면 필수)
INCLUDING NEW VALUES;

-- WITH 뒤에 오는 것은 로그의 설정 값이라고 보면 된다. 
-- 이외에도 설정할 수 있는 값이 많음.

(2) Materialized View 생성

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

BUILD IMMEDIATE : MView 생성과 동시에 데이터들이 생성
BUILD DEFERRED : MView를 생성은 하지만, 추후에 데이터들이 생성
- MView를 처음 조회할 때 데이터가 실제로 생성
REFRESH : MView 데이터가 언제, 어떻게 refresh가 되는지 옵션을 설정
REFRESH 하는 방법1
- FORCE : Fast Refresh가 가능한지 점검 후 가능하면 적용하고 그렇지 않으면 complete Refresh를 적용
- FAST : 새로운 데이터가 입력될때마다 점진적으로 변경된/새로운 데이터만 refresh되며 이때 MView log를 사용
- COMPLETE : MView의 데이터 전체가 Refresh되는 것을 의미 (MView log를 사용X)
- NEVER : MView의 Refresh가 발생되지 않음
REFRESH 하는 방법2
- ON COMMIT : 원본 테이블에 Commit이 일어날 때 Refresh가 일어남
- ON DEMAND : 사용자가 Refresh를 실행한 경우에만 Refresh가 일어남
ENABLE QUERY REWRITE : 옵티마이저가 SQL쿼리문을 실행할 때 MView를 쿼리 재작성 대상으로 포함(MView 사용) : 성능 최적화!
DISABLE QUERY REWRITE : 옵티마이저가 SQL쿼리문을 실행할 때 MView를 사용하지 않음

MView의 장단점

장점

빠른 조회 : 미리 계산된 데이터를 저장하여 조회 속도가 빠름
복잡한 쿼리 최적화 : 조인, 집계 등이 포함된 복잡한 쿼리 실행 부담 감소
- Materilized View 자체가 모든 복잡한 쿼리를 직접 실행할 수 있는 건 아니지만 복잡한 쿼리의 실행 부담을 줄이는데 도움을 줌
네트워크 부하 감소 : 원격 DB 데이터를 로컬에 저장하여 트래픽 절감
- 원격 DB 데이터를 사용할 경우 : ON COMMIT, FAST REFRESH에 제한이 있을 수 있음
백업 및 보고서 활용 : 주기적으로 갱신하여 데이터 분석 및 보고서 생성에 유리

단점

데이터 갱신 필요 : 원본 데이터 변경 시 새로 갱신해야 함. (ON COMMIT이 아니면 자동 갱신이 안됨)
추가된 저장공간 필요 : 데이터를 물리적으로 저장하기 때문에 디스크 공간 소모
갱신 비용 증가 : 실시간 데이터 변경이 많으면 성능 저하 발생

복잡한 유지보수 : 자동 갱신 설정 시 성능 튜닝 필요. (안하면 과부화 발생할 수 있음)

성능 튜닝 예시

  -- 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;

제한된 쿼리 : 생성할 때 일부 복잡한 SQL 구문이 지원되지 않음

"왜 INNER JOIN은 모니터링하는데 어려움이 있을까?"

매칭안되는 건에 대해 추적이 불가능할 때가 있다. (데이터 일관성이 깨져버림)

"왜 OUTER JOIN은 변경 사항을 추적하는데 복잡할까?"

NULL이 어떤 의미의 NULL인지 추적하기 어렵다.

Materialized View 처리 방식

Materialized View(MView)가 처리되는 순서는 아래의 그림과 같다.
기존 데이터가 변경되는 경우만 예시로 들었지만 만약 새로운 데이터가 삽입되는 경우는 MView log에 한개의 Row만 추가된다.
기존 데이터가 변경되는 경우는 기존 값과 변경 값 두개를 저장하여 찾고 변경해야하기 때문에 Log에 Row가 2개가 쌓인다.
신규 데이터가 추가되는 경우는 기존 데이터 없이 새로 데이터가 쌓이는 것이므로 Row가 1개만 쌓인다.

그외 추가적인 내용

oracle 11g 환경에서 MView를 생성하려고 했을 때 ON COMMIT 옵션을 넣게되면 'ORA-12054: 구체화된 뷰에 ON COMMIT 재생 속성을 설정할 수 없습니다.' 이라는 에러가 발생해 생성할 수 없었다.

ON COMMIT 옵션을 넣기 위한 조건(?)

FAST REFRESH가 가능한 경우에만 사용할 수 있다.
Materialized View Log가 생성되어 있어야 한다.

ON COMMIT이 안될 수 있는 이유

사용하는 쿼리가 너무 복잡하거나 집계 함수, 서브쿼리가 포함된 경우엔 FAST REFRESH가 안될 수 있다. 그렇기 때문에 ON COMMIT 또한 사용하지 못한다.

단순한 쿼리로 실행여부 확인

	-- 단순한 쿼리로 진행 (실패)
	CREATE MATERIALIZED VIEW MV_STAT_ORG
    BUILD IMMEDIATE
    REFRESH FAST ON COMMIT
    AS 
    SELECT NAME, AGE
     FROM ORG_TABLE; -- 기존 테이블
     
     
   -- 간단한 테이블을 하나 생성해서 진행 (성공)
   CREATE MATERIALIZED VIEW X_MV_TABLE_CREATE
    BUILD IMMEDIATE
    REFRESH FAST ON COMMIT
    AS 
    SELECT ID, NAME, AGE
      FROM X_MV_TABLE_TEST; -- 새로 생성한 테이블

실패한 경우도 있고 성공한 경우도 있는데 다른 점은 조회 테이블이 다르다는 점이였다. (쿼리가 문제가 되지 않았다는 말)

ON COMMIT을 설정할 때 PRIMARY KEY가 있어야 FAST REFRESH를 지원한다. PRIMARY KEY나 UNIQUE KEY가 없는 경우 ON COMMIT이 작동하지 않을 수 있다.
- 하지만 필자의 경우 REFRESH FAST ON DEMAND가 되기 때문에 REFRESH FAST을 지원한다.
- 물론, PRIMARY KEY도 있었다.
환경 문제로 Oracle 11g에서 ON COMMIT은 지원되지만, 특정 환경 설정이나 제한이 있을 수도 있다.
- 필자의 경우 Oracle 11g버전을 사용하고 있다.

MView의 생성과 갱신을 위한 권한이 부족할 경우 ON COMMIT이 정상적으로 작동하지 않을 수 있다.

테이블 소유자 확인 및 권한 설정

-- 테이블 소유자 확인 (소유자인 것으로 나옴)
  SELECT * FROM ALL_TAB_PRIVS WHERE TABLE_NAME = '테이블명';
-- 테이블 권한 설정 (소유자이더라도 MView의 ON COMMIT REFRESH**를 사용할 때는 추가적인 권한이 필요할 수 있어 설정 완료)
  GRANT QUERY REWRITE TO 소유자명;
  GRANT CREATE MATERIALIZED VIEW TO 소유자명;

필자의 경우 권한 설정을 완료 했음에도 같은 에러가 떴다.

Materialized View log가 없는 경우 생성되지 않을 수 있다.

원본 테이블의 로그테이블 확인

-- 로그 테이블은 생성된걸로 확인이 되었다.
	SELECT * FROM user_mview_logs WHERE master = '로그를 생성한 테이블명';

FAST REFRESH 지원 여부에 따라 ON COMMIT이 안될 수 있다.

만약 안된다면 아래의 절차를 따라서 확인하면 된다.

CREATE TABLE mv_capabilities_table AS
SELECT * FROM TABLE(DBMS_MVIEW.EXPLAIN_MVIEW('YOUR_MVIEW_NAME'));

-- 위의 CREATE문 실행 권한이 없을 경우 권한 확인
SELECT * FROM USER_TAB_PRIVS WHERE TABLE_NAME = 'DBMS_MVIEW';

-- 쿼리의 결과가 비어있는 경우 권한 부여
GRANT EXECUTE ON DBMS_MVIEW TO 조회하고자하는사용자명;

-- 위의 절차를 따랐음에도 ORA-01031: 권한이 불충분합니다. 뜨는 경우는 DBA 사용자에게 위의 GRANT 쿼리문을 요청하여야 함.

-- Capability_name이 FAST인 지원 여부 확인
SELECT capability_name, possible, related_text, msgtxt  
FROM mv_capabilities_table  
WHERE possible = 'NO';

하지만 필자의 경우 REFRESH FAST ON DEMAND가 되기 때문에 REFRESH FAST을 지원한다.

해당 테이블이 분산 환경의 경우이면 ON COMMIT이 안될 수 있다.
- ON COMMIT 옵션은 트랜잭션의 커밋 시점에만 MView를 갱신하는 방식
- 분산 환경에서는 분산 트랜잭션이 발생할 수 있기 때문에 원본 테이블과 Mview 간에 트랜잭션을 일관되게 관리하는 게 어려움
  - 분산 시스템에서 트랜잭션 커밋이 여러 데이터베이스에서 동시에 이루어지거나 원자성을 보장하기 어려운 경우가 많기 때문
- 분산 환경에서는 여러 데이터베이스 간의 일관된 로그 관리와 트랜잭션 동기화가 어렵기 때문에 FAST 갱신 방식이 제한될 수 있음
  - 분산 시스템에서 트랜잭션 커밋이 여러 데이터베이스에서 동시에 이루어지거나 원자성을 보장하기 어려운 경우가 많기 때문
  - 하나의 데이터베이스에서는 트랜잭션이 커밋되었지만 다른 데이터베이스에선 롤백되는 경우가 발생할 수 있음
  - 모든 데이터베이스가 트랜잭션의 결과를 동기화하고, 실패시 모두 롤백을 해야함. 하지만 이 과정에서 네트워크 장애나 시스템 다운 등이 발생할 수 있음 (이때, 트랜잭션을 어떻게 복구해야할지가 매우 복잡)
  - 각 데이터베이스 시스템의 로그 포맷과 동기화 주기가 일치해야 함(일치하지 않으면 트랜잭션 변경 사항이 다른 데이터베이스 로그에 반영되지 않거나 로그 기록이 누락될 수 있음)
- 다양한 데이터베이스 시스템 사용
  - 분산 환경에서는 여러 다른 데이터베이스 시스템이 연동될 수 있음 -> 각 데이터 베이스는 트랜잭션 처리 방식, 고유한 일관성 모델이 다를 수 있음 -> 일관되게 관리하기 어려움
기본 테이블이 여러 계정에 의해 참조되는 경우 ON COMMIT이 제한이 있을 수 있음
- 기본 테이블이 다른 계정에서 권한을 부여받아 사용되는 경우(GRANT, SELECT, INSRET, UPDATE, DELETE)에 Oracle에서는 트랜잭션 동기화 문제로 인해 ON COMMIT을 허용하지 않음
- 즉, MView가 의존하는 모든 기본 테이블이 "순수하게" MView 사유자 스키마 내에 있어야 함.
- 해결방법 : ON DEMAND 방식과 DBMS_MVIEW.REFRESH를 사용하면 생성 가능
- 다만, , Oracle이 해당 테이블을 관리하는 방식에 문제가 없다고 판단되면 생성이 됨...
SDO_GEMETRY와 관련된 도메인 인덱스가 있는 경우 ON COMMIT 방식의 MView를 생성할 수 없음
- SDO_GEMETRY와 같은 스페이셜 데이터를 다룰땐 스페이셜 인덱스와 도메인 인덱스가 문제가 될 수 있음.
- SDO_GEMETRY와 같은 특수 데이터 타입은 일반적인 데이터 타입에 비해 갱신할 때 더 많은 리소스가 필요하므로 빠른 갱신이 어렵기 때문에 제약이 존재함.
여기서 의문!
Q. MView를 생성할때 조회 컬럼에 SDO_GEMETRY 컬럼을 포함하지 않는데 왜 안될까?
A. 원본 테이블의 도메인 때문! MView는 참조하는 원본 테이블의 인덱스와 제약조건을 함께 고려함.
원본 테이블이 SDO_GEMETRY 컬럼을 가지고 있고 이 컬럼에 대한 SPATIAL_INDEX(도메인 인덱스)도 가지고 있으니 ON COMMIT을 사용하지 못하게 된 것! 해당 컬럼에 대해 자동 갱신을 보장할 수 없기 때문
Q. 도메인 인덱스가 뭐길래 왜 ON COMMIT을 막아?
A. 차근차근 설명해보자면...
- MView의 ON COMMIT 방식으로 원본 테이블이 변경되어 커밋될 때 즉시 MView에 반영하는 구조.
- Oracle은 수행하기 위해 원본 테이블의 트랜잭션 로그를 활용해서 MView를 업데이트하는데... 도메인 인덱스는 트랜잭션 로그를 사용X
- 공간 데이터 인덱스는 배치 프로세스 또는 지연 업데이트 방식 또는 수동으로 명령어를 돌려서 갱신되는데 이 말은 즉, 트랜잭션 커밋될 때 즉시 변경 사항을 반영할 수 없다는 뜻
- 하지만 MView를 만들려면 원본 테이블이 모든 인덱스 변경을 트랜잭션 커밋 시점에 즉시 처리할 수 있어야 함 그렇기 때문에 ON COMMIT을 사용할 수 없음.
- Oracle은 "컬럼 단위 변경 추적"이 아니라, "테이블 단위 변경 추적"을 수행
- 조회하는 컬럼이 아니므로 영향이 없어야 할 것 같지만, 테이블 단위로 변경 사항을 감지하기 때문에 변경된 행이 MView 갱신 대상으로 포함되고 이 과정에서 테이블의 모든 인덱스가 연관된 트랜잭션에 포함되면서 문제가 생기는 것...
- 해결 방법 : ON COMMIT 대신 ON DEMAND 사용하거나 SDO_GEMETRY 컬럼을 따로 뺀 테이블을 생성하고 기존 컬럼을 삭제해야 함.

필자의 경우는 (9)번으로 인해 MView를 생성하지 못하는 경우가 있었지만 모두 해당되지 않는다면 MView를 사용하는 것도 도움이 될 것 같다.

Ga0

이전 포스트