View 란 가상의 테이블이다.
CTAS 로 복사해서 만들면 원본 데이터를 수정해도 반영이 되지 않는다.
1. 단순 VIEW (Simple View)
단순 View는 View를 생성할 서브 쿼리에 조인 조건없이 1개의 테이블로 만들어지는 간단한 View를 의미한다.
View를 생성하기 위해서는 CREATE VIEW 권한이 필요하며 실습을 위해서 SYS 계정으로 권한을 할당해야 한다.
CONN / AS SYSDBA ;
GRANT CREATE VIEW TO SCOTT ;
CONN SCOTT/TIGER ;
생성 문법
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 만 가능한 읽기 전용 뷰를 생성
생성 예제1:
professor 테이블의 profno, name, email, hpage 컬럼만 사용하는 View 를 생성하세요.
View 이름은 v_prof 로 하세요.
CREATE OR REPLACE VIEW v_prof
AS
SELECT profno, name, email, hpage
FROM professor ;
SELECT *
FROM v_prof ;
View 를 생성 한 후 해당 View 에 SQL을 수행하면 그 때 View 내부의 서브쿼리를 수행하게 됩니다.
즉, 위와 같이 View 를 생성하면 오라클은 해당 View 정보를 딕셔너리에 저장해 놓고,
사용자가 이 View 를 사용하게 되면 3번 라인부터 있는 서브 쿼리가 수행되어
원본 테이블 professor 에 가서 SELECT 절에 있는 컬럼을 가져오게 되는 것입니다.
이 말의 의미는 평소에 이 v_prof View에는 아무런 데이터가 없다는 뜻입니다.
그래서 이 View에는 제약조건이나 인덱스 등을 생성 할 수가 없습니다.
만약 일반 View에 인덱스를 생성하려고 하면 아래와 같은 에러가 발생합니다.
CREATE INDEX idx_v_prof_name
ON v_prof(name);
ON v_prof(name)
*
ERROR at line 2:
ORA-01702: a view is not appropriate here
※ View를 조회하는 쿼리의 성능이 느릴 경우
View에 인덱스를 생성 하는 것이 아니라 원본 테이블에 인덱스를 점검해야 한다.
2. 복합 View (Complex View)
이 View 는 Sub Query 부분에 여러 개의 테이블이 Join 되어 생성되는 것이다.
생성 문법은 단순 View와 동일하다.
생성 예제2:
Professor 테이블과 department 테이블을 조인하여 교수번호와 교수이름과 소속 학과이름을 조회하는 View 를 생성하세요.
View 이름은 v_prof_dept2 로 하세요.
CREATE OR REPLACE VIEW v_prof_dept
AS
SELECT p.profno "교수번호",
p.name "교수명",
d.dname "소속학과명"
FROM professor p, department d
WHERE p.deptno = d.deptno ;
SELECT * FROM v_prof_dept ;
3. INLINE View (인라인 뷰)
View는 필요할 때 생성한 후 계속 여러 번 반복해서 재사용할 수 있다.
그러나 여러 번 사용할 필요 없이 1회만 필요한 View일 경우 SQL 문장의 FROM 절에 View 의 서브쿼리 부분을 바로 적어주면 된다.
이런 1회용 View를 INLINE View라고 한다.
생성 예제1 :
Student 테이블과 department 테이블을 사용하여 학과별로 학생들의 최대 키와 최대 몸무게, 학과이름을 출력하세요.
SELECT d.dname "학과명",
s.max_height "최대키",
s.max_weight "최대몸무게"
FROM (
SELECT deptno1, MAX(height) max_height, MAX(weight) max_weight
FROM student
GROUP BY deptno1
) s, department d
WHERE s.deptno1 = d.deptno ;
예제2 :
LAG 함수와 INLINE VIEW를 사용해서 중복되는 항목을 제거하고 보여주기
SELECT decode(deptno, ndeptno, null, deptno) deptno,
profno,
name,
pay
FROM (
SELECT LAG(deptno) OVER(order by deptno) ndeptno, -- 이전행 출력
deptno,
profno,
name,
pay
FROM professor
) ;
Inline View 연습문제 1:
Student 테이블과 department 테이블을 사용하여 학과별로 가장 키가 큰 학생들의 이름과 키, 학과이름을 Inline View 를 사용하여 출력하세요.
SELECT d.dname "학과이름",
a.max_height "최대키",
s.name "학생이름",
s.height "키"
FROM (
SELECT deptno1,
MAX(height) max_height
FROM student
GROUP BY deptno1
) a, student s, department d
WHERE s.deptno1 = a.deptno1
AND s.height = a.max_height
AND s.deptno1 = d.deptno ;
Inline View 연습문제 2:
Student 테이블에서 학생의 키가 동일 학년의 평균 키 보다 큰 학생들의 학년과 이름과 키, 해당 학년의 평균 키를 출력하되 Inline View 를 사용해서 출력하세요. (학년 컬럼으로 오름차순 정렬해서 출력하세요)
SELECT s.grade "학년",
s.name "이름",
s.height "키",
a.avg_height "평균키"
FROM (
SELECT grade , AVG(height) avg_height
FROM student
GROUP BY grade
) a, student s
WHERE a.grade = s.grade
AND s.height > a.avg_height
ORDER BY 1 ;
Inline View 연습문제 3:
professor 테이블을 조회하여 아래와 같이 교수들의 급여순위와 이름과 급여를 출력하시오.
단, 급여순위는 급여가 많은 사람부터 1위~5위까지 출력하세요.
SELECT rownum "급여순위",
name "이름",
pay "급여"
FROM (
SELECT name,
pay
FROM professor
ORDER BY 2 DESC
)
WHERE rownum BETWEEN 1 AND 5
Inline View 연습문제 4:
실습을 위해 아래와 같이 테이블을 생성한 후 데이터를 입력하세요.
CREATE TABLE jumsu
(
name varchar2(4) ,
sub varchar2(5) ,
score number(2)
) ;
INSERT INTO jumsu VALUES ('A','국어',78) ;
INSERT INTO jumsu VALUES ('B','수학',90) ;
INSERT INTO jumsu VALUES ('A','수학',63) ;
INSERT INTO jumsu VALUES ('B','국어',85) ;
INSERT INTO jumsu VALUES ('B','역사',88) ;
INSERT INTO jumsu VALUES ('C','역사',96) ;
COMMIT ;
위에서 생성한 jumsu 테이블에서 이름으로 점수를 합산 한 후 점수가 높은 사람 순서대로 정렬해서
합계 점수가 2 위인 사람의 이름과 점수를 출력하되 Rank( ) 함수를 사용하세요.
SELECT *
FROM (
SELECT name 이름,
RANK() OVER (ORDER BY SUM(SCORE)) 순위,
SUM(SCORE) 합계
FROM JUMSU
GROUP BY name
)
WHERE 순위 = 2
Inline View 연습문제 5:
아래 화면과 같이 교수 테이블을 교수 번호로 정렬한 후 출력하되 3 건씩 분리해서 급여 합계와 급여 평균을 출력하세요.
SELECT num "순번",
profno "교수번호",
name "교수명",
pay "급여",
SUM(pay) "급여합계",
ROUND(AVG(pay), 1) "급여평균"
FROM (
SELECT profno,
name,
pay,
rownum num
FROM professor
)
GROUP BY CEIL(num/3), ROLLUP((profno, name, pay, num))
ORDER BY CEIL(num/3) ;
4. Materialized View (MVIEW)
1) MView 란 ?
구체화된 View / 실체화된 View
일반 View와 달리 데이터를 가지고 있기 때문에 사용자가 많고, 데이터가 많을 경우 더 성능이 좋다.
이 방식의 문제점은 동기화이다.
2) Mview 생성하기
Mview 를 생성하기 위해서는 QUERY REWRITE 라는 권한과 CREATE MATERIALIZED VIEW 라는 권한이 있어야 한다.
CONN / AS SYSDBA ;
GRANT query rewrite TO scott ;
GRANT create materialized view TO scott ;
CONN scott/tiger ;
CREATE MATERIALIZED VIEW mv_prof
BUILD IMMEDIATE
REFRESH
ON DEMAND
COMPLETE
ENABLE QUERY REWRITE
AS
SELECT profno, name, pay
FROM professor
WHERE deptno in (101, 102, 103) ;
* 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: 동기화를 하지 않습니다.
Mview 에는 데이터가 존재하기 때문에 Index 등도 생성할 수 있습니다.
CREATE INDEX idx_mv_prof_pay ON mv_prof(pay);
3) MView 관리하기
수동으로 원본 테이블과 Mview 데이터 동기화 하기
이 작업을 하기 위해 원본 테이블 professor 에 아래 데이터를 먼저 추가하세요.
INSERT INTO professor (profno, name, id, position, pay, hiredate, deptno)
VALUES (5000, '나교수', 'improf', '조교수', 320, SYSDATE, 101) ;
COMMIT ;
동기화 전에 원본테이블 (professor)과 Mview 의 데이터 건수를 조회합니다.
SELECT COUNT(*) FROM professor WHERE deptno IN(101,102,103) ;
SELECT COUNT(*) FROM mv_prof ;
DBMS_MVIEW 패키지로 동기화를 수행 합니다.
BEGIN
DBMS_MVIEW.REFRESH('MV_PROF') ;
END ;
/
다른 동기화 명령어들
VARIABLE num NUMBER;
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 의 내용을 조회하는 방법입니다.
SELECT mview_name, query
FROM user_mviews
WHERE mview_name='MV_PROF'
아주 편리하고 유용한 기능이므로 잘 사용하시길 바랍니다.
[출처] 다양한 예제로 쉽게 배우는 오라클 SQL과 PL/SQL 서진수 저