Materialized view
query문의 실행결과에서 내부적으로 과도한 sort가 발생하거나 실행결과가 나오기까지 오랜 시간이 걸린다면
이 query문을 한번만 쓰고 버리기 아까울 것이다. 이럴경우 실행결과를 Table 처럼 view로 구성하는 것을
Materialized view라 한다. (9i부터)
* SQL문의 "인스턴스화"
* 고유한 데이터 세그먼트가 있으며, 다음 이점을 제공함
- 공간관리 옵션
- 고유한 인덱스 사용
* 다음 작업에 유용함
- 비용이 많이 드는 복잡한 조인
- 데이터 요약 및 집계
그러나 query 요구가 달라지면 새로운 Materialized view를 다시 생성해야함
-> query 당 하나의 Materialized view 사용
또한, DML 문장 실행시 심각한 Refresh 발생
<< Materialized view 실습 >>
$ sqlplus '/ as sysdba'
SQL> alter system set query_rewrite_enabled=true;
SQL> grant create materialized view to scott;
SQL> grant query rewrite to scott;
※ Materialized view를 사용하려면 기본적으로 Materialized view권한과 query rewrite 권한을 부여 받아야 한다.
query rewrite는 비용 기준 최적화를 사용하는 경우에 사용되며
활성화 및 제엉 방법은
query_rewrite_enabled = {true| false|force} -->Dynamic 인스턴스/세션 Parameter
- true(기본값) : 비용기준 Rewrite(Cost-based Rewrite)
- false : Rewrite 없음
- force : 강제 Rewrite
query_rewrite_integerity = {enforced|trusted|stale_tolerated} -->Dynamic 인스턴스/세션 Parameter
: query rewrite 할때 문제점 발생시 어떻게 처리할지 결정하는 변수
- enforced(기본값) : 옵티마이저가 일관성을 보장할 수 있는 경우에만 query rewrite 활성화
- trusted : 최신의 Materialized view와 활성화 되고 검증된 제약조건에만 query rewrite 활성화
- stale_tolerated : 차이점을 인정하고 그냥 Materialized view 실행
SQL> connect scott/tiger
-- CBO 옵티마이져 사용을 위한 통계 수집
SQL> execute dbms_stats.gather_table_stats('scott','emp');
SQL> execute dbms_stats.gather_table_stats('scott','dept');
SQL> set autot trace explain
SQL> select d.deptno, sum(e.sal) dept_tot
from emp e, dept d
where e.deptno=d.deptno
group by d.deptno;
DEPTNO DEPT_TOT
---------- ----------
10 8750
20 10875
30 9400
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=3 Bytes=30)
1 0 SORT (GROUP BY) (Cost=4 Card=3 Bytes=30)
2 1 NESTED LOOPS (Cost=3 Card=14 Bytes=140)
3 2 TABLE ACCESS (FULL) OF 'EMP' (Cost=3 Card=14 Bytes=98)
4 2 INDEX (UNIQUE SCAN) OF 'DEPT_PK' (UNIQUE) (Cost=1 Card=1 Bytes=3)
SQL> create materialized view mv_test
enable query rewrite
as
select d.deptno, sum(e.sal) dept_tot
from emp e, dept d
where e.deptno=d.deptno
group by d.deptno;
Materialized view created.
SQL> select d.deptno, sum(e.sal) dept_tot
from emp e, dept d
where e.deptno=d.deptno
group by d.deptno;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=41 Bytes=1066)
1 0 TABLE ACCESS (FULL) OF 'MV_TEST' (Cost=3 Card=41 Bytes=1066)
SQL> select sum(e.sal) dept_tot
from emp e, dept d
where e.deptno=d.deptno and e.deptno=10;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=26)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'MV_TEST' (Cost=3 Card=1 Bytes=26)
SQL> select * from mv_test;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'MV_TEST'
<< Materialized view drop >>
SQL> drop materialized view mv_test
1) 테이블 삭제시 같은 mview 이름이 dba_tables에 있으면 안됨
note 148379.1 , 265455.1 Drop Materialized View Gives ORA-12083 and ORA-04020
Problem Description
-------------------
When you issue the following command to drop a table, you receive an ORA-12083,
indicating that there is a materialized view. For example:
SQL> drop table account_ref_x;
drop table account_ref_x
*
ERROR at line 1:
ORA-12083: must use DROP MATERIALIZED VIEW to drop "DTMADMIN"."ACCOUNT_REF_X"
If you try to drop the materialized view, the error that the snapshot does not
exist is returned:
SQL> DROP MATERIALIZED VIEW account_ref_x;
DROP MATERIALIZED VIEW account_ref_x
*
ERROR at line1:
ORA-12003: snapshot "DTMADMIN"."ACCOUNT_REF_X" does not exist
2) 해당 mview에 second materilized view가 있으면 안됨
note 174048.1 OERR: ORA-32300 cannot drop a secondary materialized view %s . %s
Drop the materialized view that contains the nested table column.
This will implicitly drop all secondary materialized views.
3) drop하고자하는 유저에 권한으로 error
note 215462.1 Dropping a Materialized View Log in a different schema fails
with error ORA-01031
You should confirm that user who drops the Materialized view log (snapshot log)
on different schema has the following rights
Drop Any Snapshot (Drop Any Materialized View)
Drop Any Table
4) refrech complete가 되어 있지 않으면 에러
note 221775.1
ORA-00955 When Dropping Materialized View Which Does Not Exist In Data Dictionary Views
5) 그런데.. mview를 count할 때에 drop도 가능하고, base테이블에 트랜잭션이
있어도 mview는 삭제가 되네요...
SQL> alter table dept add constraints con_dept_pk primary key (deptno);
SQL> alter table emp add constraints con_emp_fk
foreign key (deptno) references dept;
SQL> create materialized view mv_dept
tablespace users
build immediate
refresh on commit
enable query rewrite
as select e.empno, e.ename, d.dname from emp e, dept d
where e.deptno=d.deptno;
SQL> insert into dept values (50,'kkk','kkk');
1 row created.
SQL> commit;
Commit complete.
SQL> insert into emp(empno,ename, deptno) values (1234,'KKK',50);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> select * from mv_dept;
EMPNO ENAME DNAME
---------- ---------- --------------
7369 SMITH RESEARCH
7499 ALLEN SALES
7521 WARD SALES
7566 JONES RESEARCH
7654 MARTIN SALES
7698 BLAKE SALES
7782 CLARK ACCOUNTING
7788 SCOTT RESEARCH
7839 KING ACCOUNTING
7844 TURNER SALES
7876 ADAMS RESEARCH
EMPNO ENAME DNAME
---------- ---------- --------------
7900 JAMES SALES
7902 FORD RESEARCH
7934 MILLER ACCOUNTING
1234 KKK kkk
15 rows selected.
그렇다면 YYY를 넣는 세션에 트랜잭션을 일으키고 COMMIT을 안함
SQL> insert into dept values (60,'YYY','YYY');
1 row created.
SQL> commit;
SQL> insert into emp(empno,ename, deptno) values (2345,'YYYY',60);
1 row created.
이 상태에서 mview를 drop하려함
SQL> drop materialized view mv_dept;
Materialized view dropped.
즉 base table에 트랜잭션이 있어도 삭제 가능
그러면 mview에 대해서 select하는 중에 삭제 가능?
- 다른 세션에서 수행
SQL> select count(*) from mv_dept, mv_dept, mv_dept, mv_dept, mv_dept, mv_dept;
- 이후 바로 삭제를 하였음.. prompt가 떨어짐..
SQL> drop materialized view mv_dept;
Materialized view dropped.
- 잠시 시간이 지난 후 다른 세션에서 조회하던 결과값이 도출
COUNT(*)
----------
11390625
참고자료 : http://blog.naver.com/ewnho?Redirect=Log&logNo=20012738376
