9. VIEW(뷰) _ 연습문제 다시 풀어볼 것.

9장. VIEW(뷰)

 

뷰 : 가상의 테이블로, view에는 데이터가 없고 sql만 저장되어 있다. 사용자가 view에 접근하면 그 때 view에 들어 있던 SQL이 수행되어 결과를 가져오는 것으로, 식당의 메뉴판과 비슷한 개념이다.

 

[ TIP ]

view와 같이 가상테이블을 생성하는 경우,   rownum을 이용해서, rank처럼 테이블의 행수를 지정해서 출력할 수 도 있다. (연습문제3번 참조)

 

 

1. 단순 View (simple 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 만 가능한 읽기 전용 뷰를 생성합니다.

 

: view를 생성한 후 해당 view에 sql을 수행하면 그때 view 내부의 서브쿼리를 수행한다. 즉 view를 생성하면 오라클이 해당 view정보를 딕셔너리에 저장해 놓고 사용자가 이 view를 사용하게 되면 3번 라인부터 있는 서브 쿼리가 수행되어 원본 테이블에가서 select절에 있는 칼럼을 가져오게 되는 것이다.

- view에는 제약조건이나 인덱스 등을 생성할 수 없다.

- view를 조회하는 쿼리의 성능이 느릴경우 view에 인덱스를 생성하는 것이아니라 원본 테이블에 인덱스를 점검해야 한다.

 

 

- 생성예제 2 : view를 통한 데이터 변경하기.

 일반적으로 view는 조회용으로 많이 사용되지만, 아래와 같이 데이터를 변경할 수도 있다.

 

 

      : v3_table뷰를 생성할 때 a=3인 데이터를 가져오면서 with cehck option을 사용했으므로

 a=3인 값을 다른 값으로 변경하려면 계속 에러가 발생한다.

 

: 뷰를 생성할 때 가져온 값은 with check option 사용시에 변경할 수는 없지만 삭제는 가능하다.

 

 

2. 복합View : 서브쿼리 부분에 여러 개의 테이블이 Join되어 생성되는 것입니다.

 - 뷰안에는 가급적 INTERSECT, MINUS, UNION 같은 집합 연산자는 사용하지 말아야 합니다.

 - 뷰를 잘못 사용할 경우 성능저하의 주 원인이 되는 경우가 많습니다.

위와같이 view를 생성한뒤,

scott>select * from v_prof_dept;  이렇게 조회하면 view의 서브쿼리 부분이 실행됩니다.

-view를 잘못 사용할경우 성능저하의 주 원인이 된다.(불필요한 조인 발생)

-view안에는 가급적 intersect, minus, union같은 집합 연산자는 사용하지 말아야 한다.

 

 

3. INLINE View(인라인 뷰)★★ : 다른 쿼리에서 사용할 필요 없이 해당 SQL에서만 필요한 View일 경우 SQL문장의 FROM절에 View의 서브쿼리 부분을 바로 적어서 사용가능합니다.

 

 

 

 : decode ( deptno가 ndeptno와 같으면 null값을 주고, 아니면 deptno값을 준다.)

 

 

 

4. View 조회 및 삭제하기

- 조회하기 : from user_views ;  (사용자가 생성한 view 조회시)

                   from dba_views ; (모든 view를 다 조회하고 싶은 경우.  단, dba권한을 가지고 있어야 함.)

- 삭제하기 : drop view v_emp;

 

 

5. Materialized View(MVIEW) (구체화된 view / 실체화된 view)

 

 

: view가 사용되는 시점에 원본테이블에 가서 데이터를 가지고 온 후 사용자에게 반환하고 데이터를 삭제하는 과정을 거치는데, 이런 특성이 대용량 view일 경우 문제가 된다.

- MVIEW는 데이터를 가지고 있는 테이블로, 사용자A가 최초로 Mview에 select 수행하면 Mview생성시에 가져왔던 1억건을 사용자 A에게 반환하고, 그 데이터를 Mview 내부에 저장하고 있다. 이후에 B사용자가 Mview에 select할 경우 원본테이블에 굳이 가지 않고, Mview에 있는 데이터를 바로 반환한다.

- 사용자가 많고 데이터가 많을수록 효율적이고 성능이 좋다.

 

2)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 ;

 

 

*MVIEW 생성 

SCOTT> CREATE MATERIALIZED VIEW  mv_prof    2  BUILD IMMEDIATE                                     :  Mview 를 생성하면서 서브쿼리 부분을 수행해서 데이터를 가져 오라    3  REFRESH                                                 : 원본 테이블에 데이터 변경되었을 경우 언제 어떻게 동기화시킬건지.    4   ON DEMAND                                            : 사용자가 수동으로 동기화 명령수행 (ON COMMIT : commit발생시 자동 동기화)                                                                         (on commit 옵션은 원본 테이블에 데이터 변경 많을경우 많은 부하 발생함.)    5   COMPLETE                                             : referesh 하는 방법    6  ENABLE QUERY REWRITE    7  AS    8    SELECT profno , name , pay    9    FROM  professor   10     WHERE deptno in (101,102,103) ;

 

    - refresh하는 방법

1) complete : mview내의 데이터 전체가 원본테이블과 동기화.(atomic_refresh=true 와 complete로 설정되어 있어야 사용가능)

                  - 데이터 많을 경우 시간이 많이 소요됨.

2) fast : 새로운 데이터 입력될 경우 그 부분만 Mview로 동기화. (Direct path나 Mview log파일을 사용하여 동기화.)

3) force : fast방법이 가능한지 살펴본 후 불가능하면 complete방법을 사용하여 동기화.

4) never : 동기화 하지 않음.

 

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

 

 

3) Mview 관리하기.

* 수동으로 원본 테이블과 Mview데이터 동기화.

 

- 동기화 전에 원본테이블과 Mview의 데이터건수 조회.

SCOTT> select count(*) from professor where deptno IN(101,102,103);

 

-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   <- 동기화가 완료되었습니다

 

* 다른 동기화 명령어들

- 해당 테이블을 사용하는 모든 Mview 찾아서 한꺼번에 동기화.

  SQL> VARIABLE num  NUMBER;
  SQL> EXEC DBMS_MVIEW.REFRESH_DEPENDENT(:num, ‘PROFESSOR’,'C') ;   
 : 마지막의 C는 Refresh 수준으로 Complete 를 의미하며   Force 인  f 를 쓸 수도 있으며 대소문자 구분은 하지 않습니다. 
- 해당 사용자가 만든 모든 MVIEW를 동기화.

 > DBMS_MVIEW.REFRESH_ALL_MVIEWS ; 

 

* Mview 조회/삭제

- 현재 사용자가 생성한 Mview 조회 : from user_mviews

- 데이터베이스 내의 모든 Mveiw 조회 : from DBA_mviews

- 삭제 : drop materializedd view 뷰명;

 

6. View 연습문제

 

 

select d.dname "학과이름", s.max_height "최대키", st.name "학생이름",
st.height "키"
from ( select deptno1, max(height) max_height
        from student
        group by deptno1 ) s, department d, student st
where d.deptno=s.deptno1
and s.max_height=st.height
and s.deptno1=st.deptno1

 

 

 

 

select s.grade "학년", st.name"이름", st.height "키", s.avg_height "평균키"
from (select grade, avg(height) avg_height
        from student
        group by grade)s, student st
where s.grade=st.grade
and s.avg_height < st.height
order by s.grade

 

 

 

select  rownum "급여순위" ,name "이름", pay "급여"
from (select name, pay
        from professor
        order by pay desc)
where rownum between 1 and 5

 

 

 

답)

 

틀린답	이름으로 점수를 합산해야되니까, sum(score)에서 partition by name으로 정렬하면 안되고, 순위도 합계순이므로, order by score가 아니라, order by sum(score) 이어야 한다. 대신 group by name으로 이름을 묶어 그룹지어서, sum(score)에서 이름끼리 합계 구하도록 한다.
정답	- order by sum(score)에서 desc내림차순 하지 않아도 같은 결과 나온다.

 

 

 

 

답)

 

틀린답	- rollup(profno, name, pay, num)  괄호를 하나씩만써주면 이렇게 중복되는게 나옴.
정답	- rollup((profno, name, pay, num)) 이렇게 괄호를 양쪽에 두개씩 써주면 중복되는거 없어짐. why??

 

 

 

 

 답)????????????