[10장] VIEW(뷰)

 

- View(뷰) : 가상의 테이블

 

- 원래 테이블에는 데이터가 들어있지만 View에는 데이터가 없고 SQL만 저장되어 있다.

- 사용자가 해당 View에 접근하면 그 때 View에 들어있던 SQL이 수행되어 결과를 가져오는 것

 

- View를 사용하는 이유 : 보안, 사용자의 편의성

   : 어떤 테이블에 다른 사용자가 봐서는 안되는 컬럼이 있을 경우 해당 테이블에 직접 접근하게 해 주면

     그 컬럼의 내용까지 다 보여짐 ㅜ.ㅜ

 

 

 

 

 - CTAS라는 방법으로 복사해서 새로운 테이블을 만들어 줄 수 있겠지만, 이럴 경우 원본 테이블의 데이터가 변경되면

    복사 테이블에서는 반영이 안되는 문제점이 발생.

 

 - View는 사용자가 View에 접근하는 순간 원본 테이블에 가서 데이터를 가져오기 때문에 언제나 정확한 데이터를

    가져 올 수 있음 

 - View의 사용목적은 사용자의 편의성
    ex) 어떤 결과를 조회하기 위해 5개의 테이블을 조인해야 할 경우 매번 복잡한 쿼리를 일일이 치는 것이 아니라

          5개의 테이블을 Join해서 데이터를 가져오는 View라는 것을 만들어 놓고 해당 View를 조회

 

1. 단순 View (Simple View)

 - 단순 View : View를 생성할 서브쿼리에 조인 조건이 안 들어가고 1개의 테이블로 만들어지는 간단한 View를 의미

 

 - View 를 생성하기 위해서는 CREATE VIEW 권한이 필요함

 

- SYS계정으로 권한 할당 SCOTT>CONN / AS SYSDBA; SYS>GRANT CREATE VIEW TO scott ;

 

- 생성 문법 CREATE [OR REPLACE] [ FORCE | NOFORCE] VIEW view [ (alias, alias,……)] AS sub-query [ WITH CHECK OPTION [CONSTRAINT 제약조건] ] [ WITH READ ONLY ]

 

* OR REPLACE : 같은 이름의 View가 있을 경우 삭제 후 다시 생성
* FORCE : 기본 테이블의 존재 여부에 상관없이 View 생성
* NOFORCE : 기본 테이블이 존재할 경우에만 View 생성, 기본 값
* ALIAS : 기본 테이블의 칼럼 이름과 다르게 지정한 View의 칼럼 이름 지정
* WITH CHECK OPTION : 주어진 제약조건에 맞는 데이터만 입력 및 수정을 허용
* WITH READ ONLY : SELECT 만 가능한 읽기 전용 뷰를 생성

 

- 생성 예제     ​professor 테이블의 profno, name, email, hpage 컬럼만 사용하는 View 생성     View 이름은 v_prof ​ SCOTT> CREATE OR REPLACE VIEW v_prof      2      AS      3      SELECT profno, name, email, hpage      4      FROM professor ;

 

- View를 생성 한 후 해당 View에 SQL을 수행하면 그때 View내부의 서브쿼리를 수행하게 됨

- 위와 같이 View를 생성하면 오라클은 해당 View 정보를 딕셔너리에 저장해 놓고

   사용자가 이 View를 사용하게 되면 3번 라인부터 있는 서브 쿼리가 수행되어

   원본 테이블 professor에 가서 SELECT 절에 있는 컬럼을 가져오게 되는 것

- 평소에 이 v_prof View에는 아무런 데이터가 없다는 뜻

 

- View에는 제약조건이나 인덱스 등을 생성 할 수가 없음

 

 

2. 복합 View(Complex View)

 

- 복합 View :  Sub Query 부분에 여러 개의 테이블이 조인되어 생성되는 View

 

- 생성 예제     ​professor 테이블과 department 테이블을 조인하여 교수번호와 교수이름과     소속 학과이름을 조회하는 View생성,  View 이름은 v_prof_dept2 ​ SCOTT> CREATE OR REPLACE VIEW v_prof_dept      2      AS      3      SELECT p.profno "교수번호      4           , p.name "교수명      5           , d.dname "소속학과명      6      FROM professor p , department d      7      WHERE p.deptno = d.deptno

 

  - SCOTT> SELECT * FROM v_prof_dept ;        ← 이렇게 조회하면 View 의 서브쿼리 부분이 실행됨

 

 - 뷰를 잘못사용할 경우 성능 저하의 주 원인이 되는 경우도 많음

 

 - 가급적 View안에는 INTERSECT, MINUTS, UNION같은 집합 연산자는 사용하지 말아야 성능 향상에 도움이 됨

 

 

 

3. INLINE View (인라인 뷰)

 - View는 필요할 때 생성한 후 계속 여러 번 반복해서 재사용할 수 있음

 - 그러나 여러 번 사용할 필요 없이 1회만 필요한 View일 경우 SQL 문장의 FROM 절에 View의 서브쿼리 부분을

    바로 적어주면 되며 이런 1회용 View를 INLINE View라고 함

 

- 생성 예제     ​Student 테이블과 department 테이블을 사용하여 학과별로 학생들의 최대 키와     최대 몸무게, 학과이름을 출력하세요. ​ SCOTT> SELECT d.dname "학과명"      2                , s.max_height "최대키"      3                , s.max_weight "최대몸무게"      4      FROM ( SELECT deptno1, MAX(height) max_height, MAX(weight) max_weight      5                FROM student      6                GROUP BY deptno1) s , department d      7      WHERE s.deptno1 = d.deptno ;

 

 

 

4. Materialized View (MVIEW) 

1) MVIEW란?

  : 구체화된 VIEW, 실체화된 VIEW, 데이터를 가지고 있는 뷰

 

 

 

- 일반 뷰일 경우

   * 사용자 A가 View에 SELECT를 수행하면 그때 원본 테이블에 가서 1억건 데이터를 가져온 다음

     사용자에게 반환하고 삭제

   * 다시 사용자 B가 View에 SELECT를 수행하면 다시 원본 테이블에가서 1억 건을 가져와서

   * 사용자 B에게 반환한 후 뷰에서 데이터를 삭제함

 

   * 이런 방식은 원본 테이블에도 많은 부하를 주고 사용자도 오래 기다려야 함

 

- 이러한 문제를 해결하기 위한 뷰가 MVIEW

- 구체화된 View, 실체화 된 View라고도 하며, 데이터를 가지고 있다.

 

- MVIEW일 경우

  * 사용자 A가 최초로 Mview에 SELECT를 수행하면 원본 테이블에서 1억 건을 가져와서 사용자 A에게 반환 후

      Mview 내부에 데이터 저장

  * 사용자 B가 MVIEW에 SELECT를 할 경우 원본 테이블에 가지 않고 MVIEW에 있는 데이터를 바로 반환 함

 

 - 사용자가 많고 데이터가 많을 수록 이 방식이 기본 방식에 비해 아주 효율적이고 성능도 좋다

 - 문제점 : 원본 테이블과 MVIEW간의 데이터 동기화

 

 

 

2) Mview 생성하기

 

 - Mview 를 생성하기 위해서는 QUERY REWRITE 권한과 CREATE MATERIALIZED VIEW  권한이 있어야 함

 

SCOTT> CONN / AS SYSDBA ; SYS> GRANT query rewrite TO scott ; SYS> GRANT create materialized view TO scott ; SYS> CONN scott/tiger ;

 

SCOTT > CREATE MATERIALIZED VIEW mv_prof       2       BUILD IMMEDIATE       3       REFRESH       4       ON DEMAND       5       COMPLETE       6       ENABLE QUERY REWRITE       7       AS       8             SELECT profno , name , pay       9             FROM professor      10            WHERE deptno in (101,102,103) ;;

 

-생성 문법 설명 :
* 2행 : Mview 를 생성하면서 서브쿼리 부분을 수행해서 데이터를 가져 오라는 뜻
* 3행, 4행 : 원본 테이블에 데이터가 변경 되었을 경우 MView 와 언제 어떻게 동기화를 시킬 건지에 대한 옵션

                 ON DEMAND 옵션은 사용자가 수동으로 동기화 명령을 수행해서 동기화 시키는 것이고

                 ON COMMIT 옵션도 쓸 수 있는데 이것은 원본테이블에 데이터 변경 후 Commit 이 발생하면

                 자동으로 동기화 시키라는 의미

* 5행 : REFRESH 하는 방법 4가지

   - COMPLETE : MVIEW 내의 데이터 전체가 원본 테이블과 동기화 되는 방법

                        이 옵션을 사용하려면 ATOMIC_REFRESH=TRUE 와 COMPLETE 로 설정이 되어야합니다. 

   - FAST : 원본 테이블에 새로운 데이터가 입력될 경우 그 부분만 Mview 로 동기화 하는 방법

                이 방법은 Direct Path 나 Mview log 파일 을 사용하여 동기화 하게 됩니다.
   - FORCE : FAST 방법이 가능한지 살펴보고 불가능하면 COMPLETE 방법을 사용하여 동기화 하게 됩니다.
   - NEVER : 동기화를 하지 않습니다

 

 

 - Mview 에는 데이터가 존재하기 때문에 Index 등도 생성할 수 있습니다.

 

SCOTT > CREATE INDEX idx_mv_prof_pay       2       ON mv_prof(pay);

 

 

 

 

3) MView 관리하기

 

- 수동으로 원본 테이블과 Mview 데이터 동기화 하기
   이 작업을 하기 위해 원본 테이블 professor 에 아래 데이터를 먼저 추가하세요.

SCOTT> INSERT INTO professor(profno,name,id,position,pay,hiredate,deptno)       2      VALUES(5000,'나교수','improf','조교수',320,SYSDATE,101) ; SCOTT> COMMIT ;

 

 

- 동기화 전에 원본테이블 (professor)과 Mview 의 데이터 건수를 조회합니다.

SCOTT> SELECT COUNT(*) FROM professor WHERE deptno IN(101,102,103) ;  COUNT(*) ---------            10 SCOTT> SELECT COUNT(*) FROM mv_prof ; COUNT(*) ---------                                 동기화 수행 전이라 원본 테이블의 내용과 Mview 내용이 다름            9

 

 

 - DBMS_MVIEW 패키지로 동기화를 수행 합니다.

SCOTT>BEGIN       2          DBMS_MVIEW.REFRESH('MV_PROF') ;       3     END ;       4     / PL/SQL procedure successfully completed. SCOTT> SELECT COUNT(*) FROM mv_prof;  COUNT(*) ---------            10            ← 동기화 완료

 

 

- 다른 동기화 명령어들

 

SQL> VARIABLE num NUMBER; SQL> EXEC DBMS_MVIEW.REFRESH_DEPENDENT(:num,‘ABC’,'C') ;

 

- 이 명령어는 ABC라는 테이블을 사용하는 모든 MVIEW를 찾아서 한꺼번에 동기화 하라는 의미

  마지막의 C는 Refresh수준으로 Complete를 의미하며 Force인 f를 쓸 수도 있으며 대소문자 구분은 하지 않음

 

DBMS_MVIEW.REFRESH_ALL_MVIEWS ;

 

- 이 명령어는 해당 사용자가 만든 모든 MVIEW를 동기화 하라는 의

 

 

- Mview 조회하기 / 삭제하기
   : 현재 사용자가 생성한 Mview 를 조회하고 싶으면 USER_MVIEWS 딕셔너리 조회

    데이터터베이스 내의 모든 Mview 를 조회하려면 DBA_MVIEWS 조회

 

​ - SCOTT 사용자가 생성한 mv_prof 의 내용을 조회하는 방법 SCOTT> SELECT mview_name,query      2      FROM user_mviews      3      WHERE mview_name='MV_PROF'; SCOTT> DROP MATERIALIZED VIEW mv_prof ; Materialized view dropped.

 

 

 

 

서진수,『오라클 SQL과 PL/SQL』, 생능출판사, 2012, pp.334-345