Materialized View를 생성하기 위해서는 Query Rewrite권한과 CREATE MATERIALIZED VIEW 권한을 MView를 생성하는 유저에게 부여해야 한다. 부여하는 놈은 당연 SYSDBA 유저인 SYS이다.
쿼리 재작성 또는 덮어쓰기 정도로 보면 된다. 이미 생성 된 뷰의 쿼리를 재작성하는 기능이다
1. SYSDBA로 로그인
2. MView를 테스트하는 유저로
// 다음 페이지에서 설명하겠다.
[출처] ORACLE의 Materialized View|작성자 난나다
Chapter 16. Materialized View
뷰의 정의와 정의된 쿼리를 실행한 결과가 저장된다.
처음은 느리지만 두번째부터는 속도가 빨라진다.
Materialized View에 저장된 데이터를 생신하는 방법은 다음과 같다
complete 또는 fast
Force 또는 Never
갱신모드
수동
자동(동기 또는 비동기)
SQL> GRANT CREATE MATERIALIZED VIEW TO scott;
권한이 부여되었습니다.
SQL> CREATE MATERIALIZED VIEW dept_sal_num
2 BUILD IMMEDIATE
3 REFRESH COMPLETE
4 ON DEMAND -- 사용자가 갱신 작업을 요청할 때마다 수동으로 on commit로 하면 자동으로
5 ENABLE QUERY REWRITE --쿼리 재작성 활성화
6 AS
7 SELECT d.dname, SUM(e.sal)
8 FROM dept d, emp e
9 WHERE d.deptno=e.deptno
10 GROUP BY d.dname;
구체화된 뷰가 생성되었습니다.
SQL> select * from dept_sal_num;
DNAME SUM(E.SAL)
-------------- ----------
ACCOUNTING 3750
RESEARCH 6775
SALES 9400
-- 수동으로 갱신
SQL> EXECUTE dbms_mview.refresh('DEPT_SAL_NUM');
PL/SQL 처리가 정상적으로 완료되었습니다.
MV를 GROUP BY, MAX,MIN, JOIN이 들어가야 중첩 MV를 만들 수 있다.
--- 중촙 materialized view 만들기
CREATE MATERIALIZED VIEW LOG ON sh.sales
WITH SEQUENCE,
ROWID (cust_id, time_id, prod_id)
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON sh.customers
WITH SEQUENCE,
ROWID(cust_id)
including new values;
CREATE MATERIALIZED VIEW LOG ON sh.products
WITH SEQUENCE,
ROWID(prod_id, prod_name, prod_list_price)
including new values;
CREATE MATERIALIZED VIEW sales_cust_mv
REFRESH FAST ON DEMAND AS
select c.rowid cid, s.rowid sid,
cust_last_name, time_id, prod_id, amount_sold, quantity_sold
FROM sh.sales s, sh.customers c
WHERE s.cust_id=c.cust_id;
CREATE MATERIALIZED VIEW LOG ON sales_cust_mv
WITH ROWID
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW prod_mv
REFRESH FAST ON COMMIT AS
select prod_id, prod_name, min(prod_list_price) price
FROM sh.products
GROUP BY prod_id, prod_name;
CREATE MATERIALIZED VIEW LOG ON prod_mv
WITH SEQUENCE, ROWID
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW total_sales
REFRESH FAST AS
SELECT p.rowid pid, s.rowid sid2,
cust_last_name, amount_sold,
quantity_sold, prod_name, price
FROM prod_mv p, sales_cust_mv s;
WHERE p.prod_id = s.prod_id
///////////////
쿼리 재작성 개요
Materialized view에 정의된 데이터를 사용할수 있는 쿼리를 사용자가 실행하면
Materialized view를 사용하여 쿼리를 실행한다.
단, CBO에서만 사용 가능하다.
/////////////////////
쿼릴 재작성
QUERY_REWRITE_ENABLED 파라미터를 설정하면 된다.
dbms_olap패키지의 Summary Advisor
Materialized view를 분석하는 패키지이다.
쿼리의 성능을 향상하기 위해서 해당 쿼리를 분석하여 Materialized view를 생성해야하는지 분석해 준다.
SQL> CREATE MATERIALIZED VIEW dept_sal_sum
2 BUILD IMMEDIATE
3 REFRESH COMPLETE
4 ON DEMAND
5 ENABLE QUERY REWRITE
6 AS
7 SELECT d.dname, sum(e.sal)
8 FROM dept d, emp e
9 WHERE d.deptno = e.deptno
10 GROUP BY d.dname;
구체화된 뷰가 생성되었습니다.
SQL> select * from dept_sal_sum;
DNAME SUM(E.SAL)
-------------- ----------
ACCOUNTING 3750
RESEARCH 6775
SALES 9400
-- plan table가 없다면 실행해야 한다.
SQL> @d:\oracle\ora92\rdbms\admin\utlxplan.sql
테이블이 생성되었습니다.
SQL> grant plustrace to scott;
권한이 부여되었습니다.
-- plustrace가 없다면 아래 파일을 실행해야 한다.
-- D:\oracle\ora92\sqlplus\admin\plustrce.sql
-- 실행계획을 보여줌
SQL> SET AUTOTRACE ON EXPLAIN
-- 이때 MATERIALIZED VIEW를 사용하지 않는다.
SQL> SELECT d.dname, sum(e.sal)
2 FROM dept d, emp e
3 WHERE d.deptno = e.deptno
4 GROUP BY d.dname;
DNAME SUM(E.SAL)
-------------- ----------
ACCOUNTING 3750
RESEARCH 6775
SALES 9400
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (GROUP BY)
2 1 NESTED LOOPS
3 2 TABLE ACCESS (FULL) OF 'EMP'
4 2 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
5 4 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)
-- MATERIALIZED VIEW의 쿼리를 재작성 하라는 명령
SQL> ALTER SESSION SET QUERY_REWRITE_ENABLED=TRUE;
세션이 변경되었습니다.
-- 쿼리를 다시 실행한다.
SQL> SELECT d.dname, sum(e.sal)
2 FROM dept d, emp e
3 WHERE d.deptno = e.deptno
4 GROUP BY d.dname;
DNAME SUM(E.SAL)
-------------- ----------
ACCOUNTING 3750
RESEARCH 6775
SALES 9400
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=3 Bytes=39)
1 0 TABLE ACCESS (FULL) OF 'DEPT_SAL_NUM' (Cost=2 Card=3 Bytes
=39)
--이때 MATERIALIZED VIEW를 사용한 것을 확인할 수 있다.
mview는 어떤 것이냐면..예를들어서 엄청나게 큰 emp테이블과 dept테이블이
존재합니다. 그런데 이것을 조인해서 어떤 값을 뽑아 내는 쿼리가 너무나도
자주 사용되고 있습니다. 이 경우에 어떻게 할까요?
일반 view의 경우엔 실제 데이터가 view에 들어가는 것이 아니라는 것을
아실것 입니다. 만약 그렇게 자주 사용하는 emp,dept를 join하는 view를
만든다면, 실제로 이 view를 조회하면 emp,dept 테이블을 조회합니다.
하지만 mview는 다릅니다. mview에는 실제 데이터가 들어가지요.
create materialized view mymview
build immediate
refresh force
enable query rewrite
as
select * from emp, dept where dept.deptno=20 and emp.deptno=dept.deptno;
라고 명령을 내리면 어떻게 될까요?(단 sysdba에서 query rewrite권한을
mview를 만드는 유저에 주어야 합니다.)
위 명령을 하면 select * from emp, dept where dept.deptno=20 and emp.deptno=dept.deptno;
의 쿼리에서 나오는 결과를 myview라는 mview에 저장합니다.
그래서 select * from emp, dept where dept.deptno=20 and emp.deptno=dept.deptno; 이라는
문장을 사용하게 되면 이 때는 emp,dept를 join해서 deptno=20이라는
것을 찾는 것이 아니라 myview이라는 mview에 실제로 데이터가 들어가
있으니 그곳에서 찾는 것이죠.
또한 emp,dept 테이블을 고치면, mview의 데이터까지도 고쳐집니다.
이때는 emp,dept 테이블과 myview를 동기화 해주어야겠죠. 위에서 refresh ~
옵션이 그것을 지정합니다. 자동을 refresh 되도록 했죠.
수동으로 refresh를 하고 싶을 경우엔
execute dbms_mview.refresh(myview,'c'); 명령을 하면 수동으로 refresh가
되겠죠. 또한 가장 중요한 것은 query rewrite가 되어야 합니다.
alter materialized view myview enable query rewrite; 명령을 해야겠죠.
하지만 위에서는 우리는 미리 enable query rewrite로 했으니 할 필요없죠.
mview를 사용할 경우 그 만큼의 데이터를 저장할 곳이 필요하게 되지만
속도는 엄청나게 빠르게 됩니다. 왜냐면 emp,dept테이블을 읽는 것이
아니라 이미 join되고 where절을 거친 mview를 그냥 읽어들이기
때문이지요.
구현하실려면 application user에게 query rewrite권한이 있어야 하고
파라미터 파일에 QUERY_REWRITE_ENABLED=true 이어야 하고,
QUERY_REWRITE_INTEGRITY 값도 적당히 변경해야 합니다.
몇가지 이외에도 mview를 만들 때에 써야하는 조건이 있습니다.
이후에 내가 날린 쿼리가 실제로 mview를 읽는 것인지 확인하는 방법은
application user에서 plan table을 생성하고 set autotrace ~ 명령을
사용해서 쿼리문을 날렸을 경우 쿼리가 어떻게 수행되었는지 정보를
확인하셔야 합니다.
또 한가지 중요한 것이 emp, dept 를 analyze 시키고 나서 create materialized
view 명령을 해야 재대로 생성됩니다.
더 자세한 것은 document를 oracle.com에서 다운로드 받아서
index.html을 열어보고 list of books에서 data warehouse를 보십시오.
제 홈피에 document가 있는데 mview가 있는 data warehouse html 자료는
http://min1004.com/doc/server.920/a96520/toc.htm
에 있습니다..
아.. http://min1004.com 제 홈피에 보시면..
oracel data에 mview 문서가 하나 있습니다. otn에서 구한 문서인데
더 자세히 나와 있습니다.
---------------------------------------------------
MATERIALIZED VIEW 활용방법
1. MATERIALIZED VIEW
Materialized View(이하 MVIEW)는 DW 환경이나, replication 환경에
유용한 기능으로, inner-join, outer-join, equi-join 등 각종 view를
수동으로 갱신하거나, 주기적으로 자동 갱신을 할 수 있게 해 준다.
원격 데이터베이스의 테이블이 포함된 MVIEW는 양방향 replication을
지원한다. 또한 MVIEW는 사용자에게는 투명하게 cost optimization에
기반을 둔 qurey rewrite 기능을 제공한다. Query rewrite 기능을
제공하기 위해 Oracle 에서는 Dimension이라는 객체를 추가 했는데,
Dimension 객체는 대용량 데이터에 대한 질의를 집계성 데이터에 대한
질의로 자동 변환 해 주는 기능을 제공해 준다.
MVIEW는 질의 실행을 할 때마다 매번 대량의 join이나, aggregation
연산을 수행하지 않고, 미리 계산된 값을 질의하기 때문에 성능 향상을
가져올 수 있으며, optimizer는 MVIEW가 어느때 사용되는 것이
적절할지를 판단할 수 있게 설계되었다.
Query rewrite는 사용자에는 투명하다. 만약 환경이 적절히
셋업 되어 있다면, 대량 대이터에 대한 복잡한 질의 응답 속도를
획기적으로 개선할 수 있게 한다.
2. MVIEW 관련 파라미터
MVIEW와 관련된 파라미터 목록은 다음과 같다.
optimizer_mode
query_rewrite_enabled
query_rewrite_integrity
compatible
1) optimizer_mode
Query Rewrite 기능을 사용하기 위해서는 init.ora 파일의 optimizer
mode값은 "ALL_ROWS" 나 "FIRST_ROWS"로 지정하거나, "CHOOSE"인 상태에
서 모든 테이블을 ANALYZE 시켜 두어야 한다.
2) query_rewrite_enabled
파라미터 query_rewrite_enabled 의 값은 "TRUE"로 지정한다.
3) query_rewrite_integrity
파라미터 query_rewrite_integrity 는 선택적으로 지정할 수 있는
파라미터이지만, "STALE_TOLERATED", "TRUSTED", 또는 "ENFORCED"
으로 지정되어야 한다. 이 파라미터는 query rewrite의 정확성을 제어
하는 파라미터이다.
각각의 의미는 다음과 같다
TRUSTED : optimizer에서 MVIEW의 데이터가 정확하다고
간주하고 질의 수행. Integrity 확인을 하지않음.
ENFORCED: query_rewrite_integrity 의 기본값으로,
사용자가 integrity constraint를 확인하여야
한다. MVIEW는 fresh한 데이터를 포함하여야 한다.
STALE_TOLERATED : Optimizer에서 데이터가 stale 상태이거나
fresh 상태인 경우 모두 MVIEW 사용
3. MVIEW 사용에 필요한 권한
MVIEW를 사용하기 위한 권한은 base 테이블에 대한 사용자의 권한에
달려있다. 두개의 중요한 시스템 권한은 다음과 같다.
grant rewrite
grant global rewrite
1) grant rewrite
MVIEW의 base table이 모두 사용자 자신의 테이블일 경우,
자신이 선언한 MVIWE 사용 가능.
2) grant global rewrite
사용자가 어느 schema에 속한 MVIEW라도 사용 가능.
3) MVIEW 사용에 필요한 권한이 충족된 경우 다음 조건을 만족하여야 한
다.
a. 세션에 query rewrite 기능이 enable 되어 있음.
b. MVIWE 자체가 enable 되어 있음.
c. integrity level이 적절히 셋업 되어 있음.
d. MVIEW에 데이터가 존재함.
Example
--------
다음과 같은 테이블이 있을 때,
Dealer (dealer_num, dealer_name, dealer_city, dealer_state)
Automobile (auto_num, auto_name, auto_year)
Shipping (shipping_num, shipping_day, shipping_month, shipping_time)
Summary (dealer_num, auto_num, shipping_num, auto_value)
MVIEW 생성
Create Materialized View test_mv
as
select d.dealer_num, d.dealer_name, v.auto_value, s.shipping_num,
s.shipping_day, v.dealer_num, v.rowed, s.rowid
from summary v, dealer d, shipping s
where v.shipping_num = s.shipping_num
and v.dealer_num = d.dealer_num
4. Query rewrite에서 MVIEW 사용 여부 판단 알고리즘
1) Full SQL Text Match
질의의 select 문장과 MVIEW를 만들때 사용한 select 문장 비교
2) Partial SQL Text Match
Full SQL Text Match가 실패할 경우 select 문장의 from 절 이하의
내용이 MVIEW를 만들때 사용한 내용과 일치하는지 비교
3) Generla Query Rewrite Method
1, 2 항에서 실패할 경우, optimizer에서 MVIEW 사용 가능 여부를 판단.
필요한 데이터가 MVIWE에서 제공하는 것 보다 적거나, 많거나, 변환 가능
한지를 판단하고, MVIWE 데이터가 충분한지 여부를 joing compatibility,
grouping compatibility, aggregate compatibility 등을 확인하여 판단
5. MVIEW와 Integrity Constraints
MVIEW는 DW 환경에서 유용하게 사용될 수 있는데, 대부분의 DW는
integrity constraint를 사용하지 않는다. 즉 DW는 원천 데이터에서
integrity가 보장되었다고 간주한다.
다른 한편으로 integrity constraint는 query rewrite에 유용하다.
이 모순되는 사항은 NOVALIDATE 와 RELY 옵션을 이용해 조율을 맞추어야 한다.
1) query_rewrite_enabled = enforced
데이터베이스의 constarint는 validate 상태로 두어야 한다.
2) query_rewrite_enabled = stale_tolerated | trusted
데이터베이스의 constraint를 nonvalidate, rely로 지정 해 준다.
6. Query Rewrite와 Hint 사용
Index 관련 Hint를 사용하는 것 처럼, query rewite 관련 Hint를 사용하여
제어할 수 있다.
NOREWRITE :
S-lect /-+NOREWRITE*-...
REWRITE:
S-lect /-+REWRITE(mv1)*-...
7. MVIEW 사용 예제
1) Full SQL Text Match
Select d.dealer_name, shiping_month, a.autonum, sum(v.auto_value) as sum_sales,
count(v.auto_value) as count_values
from summary f, dealer d, shippings, automobile a
where v.shipping_num = s.shipping_num
and v.dealer_num = d.dealer_num
and v.auto_num = a.auto_num
group by d.dealer_name, shipping_month, a.auto_num
위 SQL 문은 다음과 같이 미리 생성된 MVIEW를 이용하도록 rewrite 될 수 있다.
select dealer_name, shipping_month, auto_num, sum_value, count_value
from
2) Partial SQL Text Match
Select d.dealer_name, shiping_month, a.autonum, avg(v.auto_value0 as avg_sales,
count(v.auto_value) as count_values
from summary f, dealer d, shippings, automobile a
where v.shipping_num = s.shipping_num
and v.dealer_num = d.dealer_num
and v.auto_num = a.auto_num
group by d.dealer_name, shipping_month, a.auto_num
위 SQL 문장은 다음과 같이 미리 생성된 MVIEW를 사용할 수 있다.
select dealer_name, shipping_month, auto_num, sum_values/count_values as avg_values
from test_mv
Reference Documents
---------------------
--------------------------------------------------------------
추가자료
(V8I) MATERIALIZED VIEW 생성 시 ORA-12054 ERROR 해결 방법
=========================================================
PURPOSE
-------
Oracle 8i부터 제공되는 기능인 Materialized view를 생성할 때
single table에 대해 ON COMMIT refresh 옵션을 사용하여 생성 시
발생할 수 있는 ORA-12054 에러의 해결방법에 대하여 알아보기로 한다.
Problem Description
-------------------
다음과 같이 Materialized view를 생성하려고 시도할 때 ORA-12054
에러가 발생한다.
현재 테이블 test_v에 다음과 같은 데이타가 저장되어 있다고 가정한다.
SQL> select * from test_v;
KEY BONUS SEQ
----- ---------- ----------
aa 120000 1
aa 120000 2
ab 120500 3
ac 620000 4
aa 120000 8
ab 120500 9
ac 620000 10
....................
현재 사용자가 원하는 형태의 출력 format은 다음과 같다.
..... SU S1 S2 S3 ...
..... -- ---------- ---------- ---------- ....
.... a 720777 241000 1240000 .....
................
이와 같은 결과를 얻기 위해 아래와 같이 Materialized view를 생성하였다.
1 create materialized view mv1
2 build immediate
3 refresh fast on commit
4 as
5 select count(*), substr(key, 1, 1),
6 sum(decode(trim(key), 'aa', bonus, 0)) as s1,
7 sum(decode(trim(key), 'ab', bonus, 0)) as s2,
8 sum(decode(trim(key), 'ac', bonus, 0)) as s3,
9 count(bonus)
10 from test_v
11* group by substr(key,1,1)
SQL> /
from test_v
*
ERROR at line 10:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
그런데, 이와 같이 ORA-12054 에러가 발생하면서 생성이 되지 않는다.
Workaround
----------
none
Solution Description
--------------------
이 문제에 대한 해결책을 알아보기로 한다.
GROUP BY 절에 의해서 grouping 대상이 되는 컬럼(예:key)에 대하여
COUNT() 함수가 반드시 기술되어야 한다.
이는 single table에 대하여 ON COMMIT refresh 특성을 갖는
materialized view를 생성 시에 반드시 고려해야 할 제약사항 중 하나이다.
For M.V.'s with Single-Table Aggregates, there are some conditions
on refresh that need to be satisfied -
Single Table Aggregates:
=======================
a) They can only have a single table.
b) The SELECT list must contain all GROUP BY columns.
c) E-pressions are allowed in the GROUP BY and SELECT
clauses provided they are the same.
d) They cannot have a WHERE clause.
e) They cannot have a MIN or MAX function.
f) A materialized view log must exist on the table and must
contain all columns referenced in the materialized view.
The log must have been created with the INCLUDING NEW VALUES clause.
g) If AVG(expr) or SUM(expr) is specified, you must have COUNT(expr).
h) If VARIANCE(expr) or STDDEV(expr) is specified,
you must have COUNT(expr) and SUM(expr).
위의 materialized view 생성 문장이 실패한 이유는 위의 제약 조건 중
g)번을 위배했기 때문이다.
즉, SUM(expr)에 대한 각각의 COUNT(expr) statement가 빠져 있기 때문이다.
각 SUM(expr)에 대하여 다음과 같이 모든 COUNT 함수가 추가되어야 한다.
"COUNT(DECODE(TRIM(key), 'aa ', bonus, 0)) AS c1"
위와 같은 제약 조건에 따라서 사용자의 materialized view 생성 문장은
다음과 같이 수정되어야 한다.
SQL> create materialized view mv1
build immediate
refresh fast on commit
as
select count(*), substr(key,1,1),
sum(decode(trim(key),'aa',bonus,0)) as s1,
count(decode(trim(key),'aa',bonus,0)) as c1,
sum(decode(trim(key),'ab',bonus,0)) as s2,
count(decode(trim(key),'ab',bonus,0))as c2,
sum(decode(trim(key),'ac',bonus,0)) as s3,
count(decode(trim(key),'ac',bonus,0)) as c3
from test_v
group by substr(key,1,1)
Materialized view created
Reference Documents
-------------------
최근 덧글