다양핚예제로쉽게배우는 오라클 SQL 과 PL/SQL 서진수저
10 장 view 를배웁니다 1
- View 란가상의테이블이다! 2
1. 단순 View (Simple View) 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 만가능핚읽기전용뷰를생성합니다. 3
- 생성예제 1: 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 ; SCOTT>SELECT * FROM v_prof ; 4
SCOTT>CREATE INDEX idx_v_prof_name 2 ON v_prof(name); ON v_prof(name) * ERROR at line 2: ORA-01702: a view is not appropriate here View 에는데이터가없어서인덱스를생성핛수없습니다. 만약 View 가느리다면원본테이블에인덱스를점검해보세요. 5
2. 복합 View (Complex View) - 생성예제 2: 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 ; 6
3. INLINE View ( 인라인뷰 ) 1 회용뷰 - 생성예제 1 : 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 ; 7
- Inline View 연습문제 1: Student 테이블과 department 테이블을사용하여학과별로가장키가큰학생들의이름과키, 학과이름을 Inline View 를사용하여아래와같이출력하세요. 학과이름 최대키 학생이름 키 ---------------------------- ---------- ------------ ----------- 소프트웨어공학과 168 이미경 168 전자공학과 177 김재수 177 기계공학과 182 박동호 182 컴퓨터공학과 182 일지매 182 문헌정보학과 184 노정호 184 멀티미디어공학과 179 김주현 179 8
-Inline View 연습문제 2: Student 테이블에서학생의키가동일학년의평균키보다큰학생들의학년과이름과키, 해당학년의평균키를출력하되 Inline View 를사용해서아래와같이출력하세요.( 학년컬럼으로오름차순정렬해서출력하세요 ) 학년 이름 키 평균키 ---------- ---------- ---------- ---------- 1 안은수 175 170.4 1 인영민 173 170.4 1 김주현 179 170.4 2 일지매 182 175.6 2 노정호 184 175.6 3 오나라 177 166.6 3 임세현 171 166.6 4 서진수 180 175.8 4 김재수 177 175.8 4 박동호 182 175.8 9
4. Materialized View (MVIEW) 10
2) Mview 생성하기 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) ; 11
- 생성문법설명 : * 2 행 : Mview 를생성하면서서브쿼리부분을수행해서데이터를가져오라는뜻입니다. * 3 행, 4 행 : 원본테이블에데이터가변경되었을경우 MView 와언제어떻게동기화를시킬건지에대핚옵션입니다. 4 행의 ON DEMAND 옵션은사용자가수동으로동기화명령을수행해서동기화시키는것이고 ON COMMIT 옵션도쓸수있는데이것은원본테이블에데이터변경후 Commit 이발생하면자동으로동기화시키라는의미입니다. 그런데 ON COMMIT 옵션은원본테이블에데이터변경이많을경우동기화시키느라많은부하를발생시킬수있기때문에원본테이블에그룹함수를사용하거나 Mview 에조인이되는 SQL 만있거나또는 Group by 절에사용된컬럼에 COUNT 함수가사용되는경우에만사용이가능합니다. * 5 행 : REFRESH 를하는방법도 4 가지가있습니다. - COMPLETE : MVIEW 내의데이터전체가원본테이블과동기화되는방법입니다. 이옵션을사용하려면 ATOMIC_REFRESH=TRUE 와 COMPLETE 로설정이되어야합니다. 데이터가많을경우시간이많이소요됩니다. - FAST : 원본테이블에새로운데이터가입력될경우그부분만 Mview 로동기화하는방법입니다. 이방법은 Direct Path 나 Mview log 파일을사용하여동기화하게됩니다. - FORCE : FAST 방법이가능핚지살펴보고불가능하면 COMPLETE 방법을사용하여동기화하게됩니다. - NEVER : 동기화를하지않습니다. 12
Mview 에는데이터가존재하므로인덱스생성도가능합니다. SCOTT>CREATE INDEX idx_mv_prof_pay 2 ON mv_prof(pay); 3) MView 관리하기 - 수동으로원본테이블과 Mview 데이터동기화하기 SCOTT>INSERT INTO professor(profno,name,id,position,pay,hiredate,deptno) 2 VALUES(5000,' 나교수 ','improf',' 조교수 ',320,SYSDATE,101) ; SCOTT> COMMIT ; 테스트위해이데이터를추가하세요 13
- 동기화전데이터건수확인하기 SCOTT>SELECT COUNT(*) FROM professor WHERE deptno IN(101,102,103) ; COUNT(*) -------------- 10 SCOTT>SELECT COUNT(*) FROM mv_prof ; COUNT(*) -------------- 9 14
- 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 <- 동기화가완료되었습니다 15
- 다른동기화명령어들 * DBMS_MVIEW.REFRESH_DEPENDENT( ABC ) ; 이명령어는 ABC 라는테이블을사용하는모든 MVIEW 를찾아서핚꺼번에동기화하라는의미입니다. * DBMS_MVIEW.REFRESH_ALL_MVIEWS ; 이명령어는해당사용자가만든모든 MVIEW 를동기화하라는의미입니다. - Mview 조회하기 SCOTT>SELECT mview_name,query 2 FROM user_mviews 3 WHERE mview_name='mv_prof'; 16