ORACLE 10g VIEW
from OTN : Creating Materialized view
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_6003.htm#SQLRF01303
from OTN : Creating Materialized view log
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_6002.htm#SQLRF01302
1. VIEW
A.
저장된 쿼리라 할수 있다.
B.
복잡한 쿼리를 단순화 할 수 있으며
VIEW 를 생성하는데 사용된 테이블의 구조 정보를 숨길 수 있어 보안성 측면에서 유리하다.
C.
생성하는데 create view 혹은 create any view 권한이 필요하다.
D.
생성예제
i.
VIEW 의 생성
SCOTT의 EMP와 DEPT 를 조인한 (관계) VIEW를 만들어보고 조회 해 보겠다.
SQL> conn scott/tiger
Connected.
SQL> desc emp
Name
Null? Type
-----------------------------------------
-------- ----------------------------
EMPNO NOT NULL
NUMBER(4)
ENAME
VARCHAR2(10)
JOB
VARCHAR2(9)
MGR
NUMBER(4)
HIREDATE DATE
SAL
NUMBER(7,2)
COMM
NUMBER(7,2)
DEPTNO
NUMBER(2)
SQL> desc dept
Name
Null? Type
-----------------------------------------
-------- ----------------------------
DEPTNO NUMBER(2)
DNAME
VARCHAR2(14)
LOC
VARCHAR2(13)
SQL> create view
emp_view
2 as
3
select e.empno,e.ename,d.dname
4 from
emp e, dept d
5
where e.deptno = d.deptno;
View created.
(AS 뒤에
SUB QUERY 를 정의한다.)
SQL> desc emp_view
Name
Null? Type
-----------------------------------------
-------- ----------------------------
EMPNO NOT NULL
NUMBER(4)
ENAME
VARCHAR2(10)
DNAME
VARCHAR2(14)
SQL> select empno,ename,dname
from emp_view;
EMPNO ENAME DNAME
---------- ----------
--------------
7934 MILLER ACCOUNTING
~~ 중략 ~~
7499 ALLEN SALES
14 rows selected.
ii.
VIEW 의 수정
SQL> create or replace
view emp_view
2 as
3
select e.empno no,e.ename name, d.dname dept_name
4 from
emp e, dept d
5 where e.deptno = d.deptno;
View created.
( PL/SQL 과 같이 CREATE OR REPLACE 절을
사용해 수정한다. 이러한 방법을 사용했을 때의 장점은 기존에 이 객체에 묶여있던 권한들이 초기화 되지
않는다는 점이다.)
SQL> desc emp_view;
Name
Null? Type
-----------------------------------------
-------- ----------------------------
NO NOT
NULL NUMBER(4)
NAME
VARCHAR2(10)
DEPT_NAME
VARCHAR2(14)
SQL> select
no,name,dept_name from emp_view;
NO NAME DEPT_NAME
---------- ----------
--------------
7934 MILLER ACCOUNTING
~~ 중략 ~~
7499 ALLEN SALES
14 rows selected.
2. VIEW 의 정의
조회
A.
USER_VIEW
를 이용한 방법
SQL> select text from user_views
2 where view_name =
3 'EMP_VIEW';
TEXT
--------------------------------------------------------------------------------
select e.empno no,e.ename name, d.dname
dept_name
from emp e, dept d
where e.dep
B.
DBMS_METADATA.GET_DDL
을 이용한 방법
SQL> set long 100000
SQL> select
dbms_metadata.get_ddl('VIEW', view_name)
2 from user_views
3 where view_name = 'EMP_VIEW';
DBMS_METADATA.GET_DDL('VIEW',VIEW_NAME)
--------------------------------------------------------------------------------
CREATE OR
REPLACE FORCE VIEW "SCOTT"."EMP_VIEW" ("NO",
"NAME", "DEPT_NAME") AS
select e.empno no,e.ename
name, d.dname dept_name
from emp e, dept d
where e.deptno = d.deptno
3. MATERIALIZED VIEW
A.
쿼리의 결과를 가지고 있는 VIEW 이다.
B.
일반적인 VIEW와 달리 값을 가지고 있기 때문에 MATERIALIZED VIEW ( 실체화된 VIEW 라 불리운다. )
C.
생성하는데 create materialized view 혹은 create any
materialized view 권한이 필요하며 추가적으로 MVIEW 에 사용할 테이블 ( MASTER TABLE )에 대해 SELECT TABLE 이나 SELECT ANY TABLE 객체 권한이 필요하다.
D.
생성예제
SQL> grant create materialized view to
scott;
Grant succeeded.
(CREATE
MATERIALIZED VIEW 권한을 부여하였다.)
SQL> create table samp as
2 select empno,ename from emp;
Table created.
( 예제로 사용할 테이블 SAMP 를 생성하였다. )
SQL> commit;
Commit complete.
SQL> desc samp;
Name
Null? Type
-----------------------------------------
-------- ----------------------------
EMPNO NOT NULL
NUMBER(4)
ENAME
VARCHAR2(10)
SQL> alter table samp
2 add constraint samp_empno_pk
primary key(empno);
Table altered.
SQL> create materialized view log on
samp
2 with rowid, primary key
3 including new values;
Materialized view log created.
(MVIEW 를 생성하려면 우선 MATERIALIZED VIEW LOG 가 필요하다. )
SQL> create materialized view m_samp
2 refresh fast on commit
3 with primary key
4 as select count(empno) from
samp;
Materialized view created.
SQL> desc m_samp
Name
Null? Type
-----------------------------------------
-------- ----------------------------
COUNT(EMPNO) NUMBER
(MVIEW 의 구성정보를 조회하면 SUB QUERY 에 사용했던 집단함수가 그대로 조회되는 걸 확인 할 수 있다.)
SQL> select * from m_samp;
COUNT(EMPNO)
------------
14
(조회 해 보면 SUB QUERY 의 결과 : COUNT(EMPNO) 를 저장하고
있음을 알 수 있다.)
SQL> insert into samp
2 select empno + 1000, ename from
samp;
14 rows created.
SQL> select * from m_samp;
COUNT(EMPNO)
------------
14
(INSERT 가 수행했음에도 불구하고 MVIEW 의 내용은 그대로이다.)
SQL> commit;
Commit complete.
SQL> select * from m_samp;
COUNT(EMPNO)
------------
28
(COMMIT 을 수행한 후 조회한 결과 결과값이
증가한 걸 알 수 있다. 이는 REFRESH FAST ON
COMMIT 이란 구문에 근거하고 있으며 여러가지 조건을 사용할 수 있는 부분이다.)
/*+ MVIEW 의 종류는
OCP 과정에서 배우는 내용에 비할 수 없을 정도로 다양하고 복잡했다. 추가적인 내용은
추후에 MVIEW 라는 내용으로 다루고자 한다. */
