오라클의 Materialized View 이야기 - 1) 구체화된 뷰의 두 가지 쓰임새

Oracle Materialized View의 개념

몇 년 전 프로젝트를 수행하는 중 OLAP에 사용할 DW 구축을 위해 오라클의 Materialized View(이하 MView라고 표기)를 활용했던 적이 있다. Oracle 8i 이전 버전에선 Snapshot이라는 이름으로 불렸던 MView는 오라클의 문서를 보면 그 주된 용도를 다음 두 가지로 요약한다.

• 하나의 데이터베이스에서 또 다른 데이터베이스로의 데이터 복제
• 데이터 웨어하우스 환경에 사용되는 복잡한 쿼리에 대한 캐쉬

Data Warehouse 환경에서의 Materialized View

두 번째의 경우는 이미 경험한 바가 있다고 얘기했다. 보통 데이터 웨어하우스를 위한 쿼리라는 것은 여러 개의 테이블들을 조인하고 이로 부터 통계를 구하기 위해 SUM, AVG, RANK, CUM_DIST 등의 집합함수와 통계함수를 많이 쓰게 된다. 이는 쿼리 옵티마이저 입장에서 보자면 값비싼 쿼리임에 틀림 없다. 더우기 사용자는 이런 쿼리에 매번 다른 파라미터를 넘기는 이상한 습성이 있다. 쿼리를 던질 때 마다 다른 조건과 기간을 넘겨서 결과셋을 바꿔 보기를 원하는 것이다. 오라클은 이런 경우에 MView를 사용하라고 권고한다. 실제로 나 또한 MView를 사용하여 재미를 봤다. 우선 SQL에서 조인이 없어진다는 것이 매력적이다. 게다가 경우에 따라서는 집합함수나 통계함수도 사용할 필요가 없다. 그냥 단순 쿼리를 통해서 원하는 결과를 얻을 수 있다. 왜냐면 조인이나 집합함수 등을 사용한 쿼리의 결과를 하나의 테이블로 저장하고 색인까지 걸어 놓은 것이 바로 구체화된 뷰(Materialized View),  MView이기 때문이다. 

다음의 쿼리는 기업들의 벤처인증 보유 정보를 연별, 분기별 그리고 월별로 조회할 수 있도록 데이터 마트(Data Mart)로 구성한 MView의 예이다. 

CREATE MATERIALIZED VIEW MVIEW_EXAM_VENTURE

    BUILD IMMEDIATE

    USING INDEX TABLESPACE IDX

    REFRESH FORCE ON DEMAND

    WITH PRIMARY KEY

    ENABLE QUERY REWRITE

AS 

select '년' term_gb, year_gb yymm_gb, 

       biz_no, comp_nm, ceo_nm, 

       co_size_gb, co_years_gb, sido_gb, 

       venture_yn, innobiz_yn, gs_cnt, iso_cnt, net_cnt, nep_cnt

  from exam_month

 where SUBSTR(yymm_gb, 5, 2) = '12'

UNION

select '분기' term_gb, 

       case when SUBSTR(yymm_gb, 5, 2) = '03' then year_gb||'Q1'

            when SUBSTR(yymm_gb, 5, 2) = '06' then year_gb||'Q2'

            when SUBSTR(yymm_gb, 5, 2) = '09' then year_gb||'Q3'

            when SUBSTR(yymm_gb, 5, 2) = '12' then year_gb||'Q4'

        end yymm_gb, 

       biz_no, comp_nm, ceo_nm, 

       co_size_gb, co_years_gb, sido_gb, 

       venture_yn, innobiz_yn, gs_cnt, iso_cnt, net_cnt, nep_cnt

  from exam_month

 where SUBSTR(yymm_gb, 5, 2) IN ('03','06','09','12')

UNION

select '월' term_gb, yymm_gb, 

       biz_no, comp_nm, ceo_nm, 

       co_size_gb, co_years_gb, sido_gb, 

       venture_yn, innobiz_yn, gs_cnt, iso_cnt, net_cnt, nep_cnt

  from exam_month

ORDER BY term_gb, yymm_gb, co_size_gb, co_years_gb, sido_gb

복제 환경(Replication Environment)에서의 Materialized View

DW 환경에서의 MView는 고비용의 쿼리에 대한 캐쉬로서의 이점에 초점을 맞추고 있다는 것은 전술한 바와 같다. 이 외에 또 하나 오라클이 내세우는 것은 데이터 복제 수단으로서의 MView이다. 우선 데이터 복제를 통해 얻고자 하는 이점이 무엇인지 알아보도록 하자. 오라클 문서는 여러 이유를 얘기하고 있지만 그 중에서 내게 와닿은 것은 다음과 같다.

• 마스터 사이트(Master Site)로 집중되는 네트워크 부하를 분산시킨다.
• 실제로 필요한 데이터만 MView Site로 복제함으로써 네트워크 트래픽을 줄인다.

최근 스마트폰 등의 모바일 기기의 보급이 늘었고 모바일 사용자들의 이용행태에 따라 각 기업들은 모바일 서비스라는 새로운 이슈를 안게 되었다. 동일 사용자가 데스크탑에서뿐만 아니라 모바일 기기를 통해서도 서비스를 이용하다보니 데이터베이스를 이용하는 서비스에 대한 수요도 증가할 수 밖에 없다는 것이다. 단순히 이용자 수가 늘었으니 그 늘어난 수요만큼 데이터베이스의 용량을 증설하는 것이 모범답안은 아니다. 아무래도 이동 중에 사용량이 많은 모바일 기기에서 요구되는 쿼리는 데이터의 조회가 새로운 데이터의 생성 보다 비중이 높은 것이 사실이다. 그리고 작은 화면이라는 하드웨어적 제약 때문에 추려진 정보를 제공하는 것이 보통이다. 이러한 모바일 서비스 요건에 걸맞는 전용 데이터베이스를 기존 서비스를 담당하는 Master Site에서 떼어내 별도의 사이트로 구성하고자 할 때 MView의 사용을 고려할 수 있다. 즉 기존 Master Site의 데이터 중 모바일 서비스를 위한 부분 집합으로 조회 전용의 테이블 유지, 관리에 MView가 적격이라는 것이다.

모바일 사용자에 의해 늘어난 쿼리를 별도의 MView Site로 우회시키는 것으로마스터 사이트로 집중되는 네트워크 부하를 분산시키고 가용성을 높인다. 아울러 MView Site로의 복제 시에는 모바일 서비스에 필요한 데이터만 부분 복제하여 테이블 전체에 대한 복제에 비해 네트워크 트래픽을 줄일 수 있다. 이런 요건을 충족시키는 방법이 MView 말고는 없는 것인가? 라는 의구심이 들어야 정상이다. 그냥 Master Site의 테이블에 조건을 충족하는 쿼리를 실행하고 그 결과셋을 가져와서 복제 사이트의 테이블에 넣어주면 되는 거 아닌가? 라고 말이다. 

MView의 동작 매커니즘, Materialized View Log를 이용하다

물론 그런 방법도 있다. 하지만 MView는 이런 일련의 과정을 효율적으로 무엇 보다도 적은 비용으로 처리해준다. 마스터 사이트에 있는 materialized view log는 원본 테이블에서 변화가 생긴 row를 감시하고 refresh가 되어야 할 데이터의 Primary Key를 관리한다. dbms_mview.refresh() 메쏘드는 별도의 프로그램 없이 MView Log 테이블에 있는 Primary Key를 근거로 변경되거나 추가된 정보를 업데이트 해준다. refresh() 메쏘드는 일정한 주기로 자동 호출되거나 또는 원본테이블에서 commit이 발생할 때 자동으로 호출될 수도 있다. 뿐만 아니라 사용자가 원하는 때에 호출할 수도 있다. 이 메카니즘은 참으로 효율적이고 단순 명료하다. 

이후 글에서 더 구체적으로 살펴보겠지만 미리 엿보자면 Materialized View Log는 다음과 같은 스키마를 갖는다. 

SQL> desc mlog$_course

 이름                                       널?       유형

 ----------------------------------------- -------- ----------------------------

 STUDENTID    NUMBER

 SUBCODE    NUMBER

 SNAPTIME$$    DATE

 DMLTYPE$$    VARCHAR2(1)

 OLD_NEW$$    VARCHAR2(1)

 CHANGE_VECTOR$$    RAW(255)

앞서 등장하는 STUDENTID, SUBCODE 등은 원본 테이블에서 Primary Key로 선언된 필드들이다. 그 뒤를 따르는 필드들은 변동이 발생한 row의 PK가 로그에 기록된 시각, 변경유형(Delete, Insert, Update) 등이다. 이 로그 테이블의 내용을 참고하여  MView는 fast refresh를 수행한다. 

그림 1. 복제환경에서의 Materialized View 구현을 위한 구성요소 및 업데이트 방법

• Master Site      원본 테이블이 있는 데이터베이스를 오라클은 마스터 사이트라 부른다.

• MView Site       복제된 MView가 있는 데이터베이스를 지칭, 마스터 사이트와는 물리적으로 다른 host이다. 

• Database Link    MView Site에서 마스터 사이트에 있는 객체들에 접근하기 위해 오라클에서 제공하는 데이터베이스 연결.

• Source Table     복제 대상이 되는 테이블, 테이블 또는 MView가 될 수도 있다. 모두 마스터 사이트에 존재해야 한다.

• MView Log        복제 대상 테이블의 변동 내역을 기록하는 로그 테이블로 마스터 사이트에 있어야 한다.

• Target Mview     복제되고 업데이트 관리되는 테이블, 구체화된 뷰(Materialized View)이다.

• Refresh          MView를 업데이트하는 오라클의 내부 프로시져 dbms_mview.refresh()

그림 1에서 보여주는 것처럼 Master Site에 있는 원본 테이블의 변동 내역을 기록하는 Materialized View Log를 통해서 MView는 주기적으로 또는 명시적인 요청이 있을 때 변동 내역에 대한 업데이트를 수행하는 것이다. 다음 글에서는 그림 1에서 설명하는 각 구성요소들을 구체적으로 설명하고 그 구현 방법을 알아보겠다.