[Oracle] Materialized View 정리 및 Advisor 사용하여 쉽게 생성

일반 View의 문제점

View는 복잡한 SELECT 명령문을 간단하게 쿼리하기 위해 SELECT 문을 갖고 있는 오브젝트 입니다. 데이터를 갖고 있지 않고 SELECT문을 저장해서 해당 View를 조회했을 때, 쿼리가 복잡하다면 편할지언정 원래의 복잡한 쿼리문을 조회하는 것과 같습니다. 즉, 사용의 편의성은 있지만 성능적인 측면에서 문제가 되는 상황은 발생합니다.

 

문제가 되는 상황을 예로 들어 보겠습니다. 3개의 테이블을 조인하는 View가 있습니다. 그런데 3개의 테이블 모두 대량의 데이터를 보유하여 쿼리문을 통해 데이터를 조회하는데 너무 오랜 시간이 걸려 성능적인 개선이 필요한 상황이 발생합니다. 이러한 문제를 해결하기 위해 데이터를 보유하는 View인 Materialized View OR Sumary Table을 사용합니다.

 

 

Summary Table 사용 해결법

데이터양으로 인해 조인 작업을 했을 때 오랜 시간이 걸리는 문제는 미리 조인 작업을 한 결과를 테이블로 저장하여 쿼리 작업을 할 때 조인이 아닌 Summary Table을 조회하는 방법입니다. 이때 가장 큰 문제가 되는 것은 Base Table들과 Summary Table의 동기화 작업입니다. 동기화에 대한 문제는 Trigger를 사용해서 DML에 대한 이벤트가 발생했을 때 Summary Table의 변경작업을 같이 해주는 방법입니다.

 

쿼리작업을 했을때 시간을 단축하여 성능작업은 해결할 수 있지만, Application 단에서 추가적인 수정작업을 해주어야하는 문제가 발생합니다.

 

 

Materialized View 사용 해결법

Materialized View는 데이터를 갖고 있는 View 입니다. 마찬가지로 위의 문제를 해결하기 위해 View에 대한 결과를 갖고 있어 쿼리문을 실행했을 때 빠른 속도로 결과를 받을 수 있습니다.  Summary Table을 이용하는 것과는 다르게 View Log라는 오브젝트를 이용해서 동기화 작업을 합니다. 원본데이터의 수정된 부분만 기록하여 동기화 작업을 합니다.

 

Materialized View는 Optimizer가 실행계획을 작성할 때 참조하여 자동으로 빠른 방법을 선택합니다. 실습을 통해서 알아보겠습니다.

 

 

Advisor를 이용한 실습

실습환경

OS : Red Hat Enterprise Linux Server release 6.10
DB : Oracle 12.1.0.2
SID : testdb

 

dbms_advisor.tune_mview

오라클에서 제공해주는 advisor 패키지를 이용해서 Materialized View생성 가이드를 하나 받아서 진행할 예정입니다. 샘플 스키마 SH가 갖고 있는 테이블로 작업을 진행하겠습니다.

[testdb@host01 ~]$ sqlplus / as sysdba

SYS@testdb> desc dbms_advisor

dbms_advisor.tune_mview에서 필요한 파라미터를 확인했습니다. TASK_NAME은 IN/OUT Type으로 파라미터를 하나 지정해줘야하고 MV_CREATE_STMT에는 Materialized View를 생성하는 쿼리를 넣어 가이드를 받겠습니다.

 

 

SYS@testdb> grant advisor to sh;

advisor 패키지를 사용할 수 있는 권한을 sh에게 부여해줬습니다.

 

 

 

 

 

SYS@testdb> conn sh/sh

SH@testdb> 
declare
	tune_mv varchar2(20) := 'mview_task';
begin
dbms_advisor.tune_mview
(
task_name=>tune_mv,
mv_create_stmt=>'create materialized view c_s_mv
enable query rewrite
as
select 
c.cust_id
,s.channel_id
,avg(s.amount_sold)
from customers c, sales s
where c.cust_id = s.cust_id
group by c.cust_id, s.channel_id'
);
end;
/

PL/SQL procedure successfully completed.

dmms_advisor.tune_mview를 성공적으로 실행 후 user_tune_mview 뷰를 통해 분석 결과를 확인할 수 있습니다.

 

 

SH@testdb> set long 10000
SH@testdb> select * from user_tune_mview;

CLOB 타입의 결과를 보여주기 때문에 long 값을 넉넉하게 주고 결과를 조회해봤습니다. 위 내용은 차례대로 VIEW lOG와 Materialized View를 생성하는 쿼리를 알려줍니다. alter에 대한 내용을 내용을 제외하고 create부분의 내용을 알아보기 쉽게 정리하고 실행까지 해보겠습니다.

 

 

Materialized View 생성

SYS@testdb> grant query rewrite to sh;
SYS@testdb> grant create materialized view to sh;

생성작업이 안될 경우 위의 권한을 부여해주세요.

 

SH@testdb> CREATE MATERIALIZED VIEW LOG ON "SH"."SALES" 
WITH ROWID, SEQUENCE ("CUST_ID","CHANNEL_ID","AMOUNT_SOLD")  
INCLUDING NEW VALUES;

Materialized view log created.


SH@testdb> CREATE MATERIALIZED VIEW LOG ON "SH"."CUSTOMERS"
WITH ROWID, SEQUENCE ("CUST_ID")	
INCLUDING NEW VALUES;

Materialized view log created.


SH@testdb> CREATE MATERIALIZED VIEW SH.C_S_MV   
REFRESH FAST WITH ROWID 
ENABLE QUERY REWRITE 
AS 
SELECT 
SH.SALES.CHANNEL_ID C1,
SH.SALES.CUST_ID C2, 
SUM("SH"."SALES"."AMOUNT_SOLD") M1, COUNT("SH"."SALES"."AMOUNT_SOLD") M2,
COUNT(*) M3 FROM SH.CUSTOMERS, 
SH.SALES 
WHERE SH.SALES.CUST_ID = SH.CUSTOMERS.CUST_ID 
GROUP BY SH.SALES.CHANNEL_ID, SH.SALES.CUST_ID;

Materialized view created.

쿼리를 하나씩 살펴보면 조인작업을 하는 테이블에 동기화 작업을 위한 View Log 하나씩 생성하고, Materialized View를 생성하는 쿼리하나가 있습니다.

 

그런데 분석을 요청한 MView 생성 쿼리가 조금 다릅니다. Average를 구하는 함수를 사용했는데, SUM과 COUNT에 대한 컬럼을 이용해서 결과가 작성되었는데 왜 이런 결과를 권했을까요?

 

MView를 더 많은 범위에서 Optimizer가 참조하도록 AVG함수를 SUM과 COUNT로 나눈 것 입니다. 쿼리가 AVG가 아닌 SUM이나 COUNT에 대한 작업이 들어왔을 때도 Optimizer가 참조하도록 할 수 있습니다. AVG함수를 사용해서 MView를 생성할 수 있지만, 그렇게되면 SUM이나 COUNT에 대한 작업이 들어왔을 때 성능을 높이기 위해서 다시 MView를 만드는 불편함이 있기 때문에 분리해서 한번에 MView를 생성하도록 권장하는 것입니다. Optimizer에서 실행 계획을 만들 때 Query의 Re-Write 작업을 진행하여 SUM과 COUNT결과를 갖고 AVG의 값을 산출할 수 있습니다.

 

 

조회 확인 작업

SH@testdb> set autot trace explain

쿼리를 실행했을 때 실행계획만 확인하기 위해 설정합니다.

 

SH@testdb> 
select 
    c.cust_id
    ,s.channel_id
    ,avg(s.amount_sold)
from customers c, sales s
where c.cust_id = s.cust_id
group by c.cust_id, s.channel_id;

뷰의 쿼리로 조회했을 때 MView를 사용해서 조회하는 것을 확인할 수 있습니다.

 

SH@testdb> 
select 
    c.cust_id
    ,sum(s.amount_sold)
from customers c, sales s
where c.cust_id = s.cust_id
group by c.cust_id;

sum으로만 조회했을때도 MView를 조회하는 것을 확인할 수 있습니다. 생성쿼리와 동일하지는 않아, 해당 뷰에서 한번더 걸러내는 작업을 하는 것도 확인 가능합니다.

 

 

SH@testdb> set autot off
SH@testdb> 
select 
    segment_name
    ,segment_type 
from user_segments 
where segment_name = 'C_S_MV';

세그먼트를 조회했을때 VIEW와는 다르게 TABLE Type으로 조회가 되는 것을 확인할 수 있습니다.

 

 

Materialized View 생성 문법

실습은 Advisor를 이용해서 권장되는 생성 쿼리를 받아서 진행했습니다. Materialized View를 생성할때 사용되는 옵션들을 몇가지 알아보겠습니다.

create materialized view <뷰명>
build immediate
refresh [on demand | on commit]
[complete | fast | force | never]
enable query rewrite
as
<서브쿼리>;

build immediate

MView 생성 하면서 해당 데이터를  MView에 저장

 

on demand

dbms_mview 패키지를 이용해서 동기화 작업

 

on commit

commit 명령 사용시 자동 동기화 작업

 

complete

모든 데이터가 동기화

 

fast

수정된 데이터만 동기화 됩니다. 이 방법을 사용하기 위해서 Materialized View Log 오브젝트 생서이 필요합니다.

 

force

fast 기업과 complete 병행 사용

 

never

동기화 하지 않음

 

enable query rewrite

이 옵션은 필수로 사용하는 것을 권장합니다. 쿼리 실행했을때 Optimizer가 해당 MView를 참조하여 Query Re-Write을 할 수 있도록 합니다.