오라클의 Materialized View 이야기 2) 테이블 복제를 위해 MView 이용하기

구체화된 뷰(Materialized View)는 일반 뷰와 무엇이 다른가?

오라클 SQL*Plus의 한국어 버전에서 Materialized View를 '구체화된 뷰'라 쓰는 것을 볼 수 있다. '구체화된 뷰'라는 것은 무슨 뜻일까? 보통의 뷰를 실행하면 정의된 쿼리의 결과셋이 메모리 상에 생성되고 세션이 끝나면 이 결과셋은 사라지고 만다. 그러나  Materialized View의 경우는 다르다. 그 쿼리의 결과셋이 오라클의 저장소인 테이블스페이스에 테이블로 저장되는 것이다. 이렇게 물리적인 저장소에 그 결과가 저장된다하여 '구체화 되었다'라는 표현을 쓰는 것이다. 사실 구체화라는 우리말과 Materialized라는 단어 사이의 차이를 좁히는 것이 쉽지 않다. 어쨌든 구체화된 뷰는 이미 만들어진 테이블을 통해 데이터를 직접 접근할 수 있게 되었다. 그리고 무엇 보다도 매력적인 것이 이렇게 만들어진 테이블은 적은 비용으로 지속적인 업데이트가 가능하다는 것이다.

구체화된 뷰와 스냅샷(Snapshot)의 다른 점과 같은 점

오라클 7에서 쓰이던  스냅샷의 명맥을 잇는 Materialized View는 dbms_mview 패키지를 이용해 관리할 수 있다. 구체화된 뷰가 이전 스냅샷과 달라진 점은 생성된 테이블에 직접 DML을 사용할 수 있다는 점이다. 즉 SELECT 뿐 아니라 INSERT, UPDATE, DELETE도 가능하다는 얘기다. 그러나 스냅샷에서 구체화된 뷰로 이름이 바꼈을지언정 그 목적은 여전하다. 복잡한 쿼리의 캐쉬 뿐 아니라 특정 테이블을 다른 데이터베이스로 복제하는 수단으로도 쓰인다는 것이다.

구체화된 뷰를 생성하는 간단한 구문

create materialized view mv_target_table_name 

   build immediate

   refresh fast on commit

   as select * from source_table_name;

사실 구체화된 뷰는 일반적인 테이블과 크게 다를 바가 없다. 그 내용이 원본 테이블의 변동내역에 따라 자동적으로 업데이트 된다는 점을 제외한다면 말이다. 그래서 일반적인 테이블과는 구분하기 위해 보통 접두어 'mv'를 붙이는 것이 관례이다. build immediate는 구체화된 뷰를 생성하는 시점에 데이터를 업데이트하라는 구문으로 기본 동작이다. 다음 줄의 refresh fast on commit은  구체화된 뷰의 데이터를 업데이트하는 방법을 설명하는 것으로 fast는 변경된 내용만을 업데이트 하라는 것이고 on commit은 원본 테이블의 변경된 내용이 commit되는 시점에 업데이트를 수행하라는 의미이다. 마지막 줄의 select * from 구문은 따로 설명하지 않아도 되겠다. 다만 한 마디 설명을 붙이자면 * 대신 원하는 필드만을 지정하여 구성할 수도 있다는 점이다. 위 예제에서는 한 개 테이블에 대한 복제를 다루고 있지만 사실 두 개 이상 테이블에 대한 JOIN을 포함하는 복잡한 쿼리도 사용 가능하다.

테이블 복제를 위한 구현 단계

이전 글에서 복제가 필요한 이유에 대해서는 이미 언급하였으므로 그 구체적인 구현 방법을 알아보도록 하자. 우선 원본 테이블을 가지고 있는 데이터베이스를 Master Site라하고 복제된 MView를 갖는 데이터베이스를 MView Site라 부르기로 한다. 다음에 기술한 각 단계들이 마스터 사이트와 구체화된 뷰 사이트 간의 테이블 복제를 구현하는데 필요하다. 각각의 구체적인 내용들을 알아 보도록 하자.

• Master Site와 MView Site에 동일 계정 생성
• 양 쪽 계정에 적절한 권한 부여
• 원본 테이블을 모니터링하는 MView Log 생성
• 데이터베이스 링크 생성
• MView 생성 및 업데이트 방법 설정 
• 필요한 경우 동의어 생성
• 동작 테스트

Master Site와 MView Site에 동일 계정 생성

이미 계정이 있다면 이 과정을 생략한다. 여기서 계정을 동일하게 생성하는 것은 Database Link 접속, 객체 접근 권한 등을 일관성 있게 관리하기 위해서이다. 계정이름을 'likebnb' 그리고 비밀번호를 'likebarnabas'라고 하자.

Master Site와 MView Site 모두 다음과 같이 계정 생성 SQL은 동일하다.

create user likebnb identified by likebarnabas;

양 쪽 계정에 적절한 권한 부여 

Master Site 계정에 권한 부여하기

grant connect, resource, create materialized view to likebnb;

create materialized view 권한은 MView Log 생성을 위해 필요하다. 

MView Site 계정에 권한 부여하기

grant connect, resource, create materialized view, 

      create database link, create synonym to likebnb;

create database link 권한은 말 그대로 Master Site로의 연결을 위해 필요한 것으로 오라클에서는 원격에 있는 데이터베이스로의 연결지원을 위해 database link라는 것을 사용한다. create synonym 권한은 생성된 MView의 이름 대신 원본 테이블의 이름을 쓰고자 할 때 MView의 이름에 대한 동의어로 원본 테이블 이름을 지정할 때 필요한 권한이다.

원본 테이블을 모니터링하는 MView Log 생성

create materialized view log on source_table with primary key;

모니터링을 위한 Mview 로그는 원본 테이블이 위치하는 Master Site에서 만들어야 한다. 아울러 원본 테이블은 반드시 primary key가 정의되어 있어야 한다. source_table은 당연한 얘기지만 복제되어야 할 원본 테이블의 이름을 써야 한다.

데이터베이스 링크 생성

create database link dbmsorg

   connect to likebnb identified by likebarnabas

   using 'dbmsorg';