CREATE [VLDB: OR REPLACE ] [ FORCE | VLDB:NOFORCE ] VIEW view_name
AS subquery
[ WITH CHECK OPTION [VLDB: CONSTRAINT 제약조건 ]]
[VLDB: WITH READ ONLY ]
뷰로 생성된 논지적인 집합은 항상 먼저 수행되는 것이 아닌, 뷰를 사용한 SQL 과 합성되면서 작용된다.
CREATE or replace VIEW EMP1_VIwS ( deptno ,h_yymm, sel_tot, comm_tot )
as select deptno, TO_CHAR(hiredate,'YYMM'),
sum(sal), SUM(comm)
from EMP
where JOB = 'MANAGER'
group by deptno, to_char(hiredate,'YYMM');
select * from EMP1_VIwS x, dept y
where x.deptno = y.deptno
and x.deptno between 10 and 50 ;
Rows Row Source Operation
\--\--\--\- \--\--\
3 SORT GROUP BY (cr=9 pr=0 pw=0 time=330 us)
3 HASH JOIN (cr=9 pr=0 pw=0 time=250 us)
3 TABLE ACCESS BY INDEX ROWID EMP (cr=2 pr=0 pw=0 time=58 us)
14 INDEX RANGE SCAN EMP_DEPTNO (cr=1 pr=0 pw=0 time=34 us)(Object ID 58070)
4 TABLE ACCESS FULL DEPT (cr=7 pr=0 pw=0 time=42 us)
select x.deptno, to_char(hiredate, 'YYMM'),
SUM(sal), SUM(comm)
from emp x, dept y
where x.deptno = y.deptno
and job ='MANAGER'
and x.deptno between 10 and 50
group by x.deptno , to_char(hiredate,'YYMM');
Rows Row Source Operation
\--\--\--\- \--\--\
3 HASH JOIN (cr=10 pr=0 pw=0 time=299 us)
3 VIEW (cr=2 pr=0 pw=0 time=119 us)
3 SORT GROUP BY (cr=2 pr=0 pw=0 time=114 us)
3 TABLE ACCESS BY INDEX ROWID EMP (cr=2 pr=0 pw=0 time=55 us)
14 INDEX RANGE SCAN EMP_DEPTNO (cr=1 pr=0 pw=0 time=35 us)(Object ID 58070)4 TABLE ACCESS FULL DEPT (cr=8 pr=0 pw=0 time=38 us)
뷰의 실행경로 확실히 하는법
뷰를 포함하는 SQL 문장 실행시 실행 계획은 뷰와 나머지 SQL 이 합성을 통해서 생성 되므로,
항상 뷰 내에서 드라이빙 되도록, 혹은 항상 뷰 밖에서 드라이빙 되도록 조치 하는것
뷰를 사용하는 일반적인 목적은 다음과 같이 분류할 수 있다.
(1). 보안 관리을 목적으로 활용 ( 보안성 )
(2). 사용상의 편의를 목적으로 활용 ( 편의성 )
(3). 수행속도의 향상을 목적으로 활용 ( 속도 향상 )
(4). 융통성을 향상시킬 목적으로 활용 ( 융통성 )
(5) 임시적인 작업을 위해 활용한다. ( 임시성 )
(1) 보안 등급에 따라 컴럼 및 범위 선정
CREATE VIEW MERITS ( empno, ename, deptno, job, mgr, merits, grade)
AS SELECT empno, ename, deptno, job, mgr, merits, grade
FROM EMP
WHERE deptno IN ( SELECT deptno
FROM DEPT
WHERE deptname = user )
AND TO_CHAR(sysdate,'HH24MI') BETWEEN '0900' AND '1800';
CREATE ROLE merit_role_1 IDENTIFIED BY password ;
CREATE ROLE merit_role_2 IDENTIFIED BY password ;
GRANT SELECT ON MERITS TO merit_role_1 ;
GRANT all ON MERITS TO merit_role_1 ;
(2) 결과만 제공하고 알고리즘을 쉼기기 위한 뷰
CREATE VIEW BOUNS_TAB
AS SELECT empno, ename,
sal*decode(job, 'MANAGER',1.1,
'SALESMAN',1.12,
1.05) BOUNS
FROM EMP;
(3) 데이터의 변경을 원칙적으로 봉쇄하는 뷰
입력과 수정을 봉쇄되나, 삭제는 막을 수 없는 뷰
SCOTT@TGDOM>create view test_view
2 as select empno, ename, nvl(sal,0) sal
3 from emp ;
View created.
SCOTT@TGDOM>update test_view set sal = 6000 ;
update test_view set sal = 6000
*
ERROR at line 1:
ORA-01733: virtual column not allowed here
SCOTT@TGDOM>delete test_view ;
14 rows deleted.
데이타 변경 자체를 막는 뷰
SCOTT@TGDOM>create or replace view TEST_VIEW
2 as select empno, ename, nvl(sal,0) sal
3 from emp
4 with read only ;
View created.
SCOTT@TGDOM>delete test_view ;
delete test_view
*
ERROR at line 1:
ORA-01752: cannot delete from view without exactly one key-preserved table
특정 데이타에 대해서만 변경 자체를 막는 뷰
SCOTT@TGDOM>create or replace view TEST_VIEW
2 as select empno, ename, deptno, sal
3 from emp
4 where deptno in (10,30)
5 with check option ;
View created.
SCOTT@TGDOM>insert into TEST_VIEW values ( 1999, 'TG', 40, 1000 ) ;
insert into TEST_VIEW values ( 1999, 'TG', 40, 1000 )
*
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation
SCOTT@TGDOM>insert into TEST_VIEW values ( 1999, 'TG', 30, 1000 ) ;
1 row created.
SCOTT@TGDOM>update TEST_VIEW set SAL = 9999 where EMPNO = 7900 ;
1 row updated.
뷰의 실행경로 확실히 하는법
(4) 명칭을 감추기 위한 뷰
Well-Known View 에 대한 Rename을 통한 보안 관리
(1) 검색 조건을 단순화를 위한 뷰
항상 상수값으로 지정되는 컬럼들은 뷰내에 지정함으로써 훨씬 간편하게 사용할 수 있다.
SCOTT@TGDOM>create force view cur_emp
2 as select * from emp
3 where ( retire_date = '000000' or retiredate is null
4 or resume_date = '000000' and resume_date is null )
5 and company = '1';
(2) 컬럼명, 테이블명을 한글화한 뷰
SCOTT@TGDOM>create view "부서"
2 ("부서번호","부서명","위치")
3 AS SELECT deptno, dname, loc FROM DEPT ;
View created.
SCOTT@TGDOM>select * from "부서" ;
부서번호 부서명 위치
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
(3) 조인 문장의 단순화를 위한 뷰
RDBMS의 정규화 과정을 거치면서 불가피하게 조인이 발생 하며, 이것은 조인뷰를 활용하면, 마치 테이블을
정규화하지 않은 형태로 생성한 것처럼 사용할 수 있다.
SCOTT@TGDOM>CREATE FORCE VIEW cus_me1v
2 AS SELECT c.custno, c.custname, m.saleno, m.saledept
3 FROM CUSTOMER c, MECHULT m
4 WHERE c.custno = m.custno
5 AND c.nation = 'KOR'
6 AND m.market ='L';
(4) GROUP BY, SUM(DECODE..) 처리를 위한 뷰
SCOTT@TGDOM>CREATE VIEW dept_year_sale
2 AS SELECT saledept,
3 SUM(DECODE(SUBSTR(saledate,3,2),'01',saleqyt)) m01,
4 SUM(DECODE(SUBSTR(saledate,3,2),'02',saleqyt)) m02,
5 SUM(DECODE(SUBSTR(saledate,3,2),'03',saleqyt)) m03,
6 SUM(DECODE(SUBSTR(saledate,3,2),'04',saleqyt)) m04,
7 SUM(DECODE(SUBSTR(saledate,3,2),'05',saleqyt)) m05,
8 SUM(DECODE(SUBSTR(saledate,3,2),'06',saleqyt)) m06,
9 SUM(DECODE(SUBSTR(saledate,3,2),'07',saleqyt)) m07,
10 SUM(DECODE(SUBSTR(saledate,3,2),'08',saleqyt)) m08,
11 SUM(DECODE(SUBSTR(saledate,3,2),'09',saleqyt)) m09,
12 SUM(DECODE(SUBSTR(saledate,3,2),'10',saleqyt)) m10,
13 SUM(DECODE(SUBSTR(saledate,3,2),'11',saleqyt)) m11,
14 SUM(DECODE(SUBSTR(saledate,3,2),'12',saleqyt)) m12,
15 SUM(saleqty) total,
16 FROM MECHUL2t
17 WHERE saledte like TO_CHAR(sysdate,'yy')||'%'
18 GROUP BY saledept ;
SCOTT@TGDOM>select to_char(sysdate,'YY') from dual ;
TO
--
07
뷰 역시 튜닝의 대상이며, 힌트나 사용제한을 통하여 원하는 액세스 경로의 유도, 부여한 조건이 뷰내에 파고 들어
뷰가 먼저 수행된 후 그 결과를 이용하여 다음 처리를 하도록 유도함으로써 처리할 일량을 줄여 수행속도 향상을
꾀 할 수 있다.
(1) GROUP BY 뷰를 이용한 수행 속도 향상
가) 두 테이블간의 조인
: JOIN 후 GROUP BY - 일량이 많다. - 실행 안됨 \!\!
SCOTT@CRAFT.WORLD>select d.dname, d.loc, sum(s.salqty), avg(s.salqty), max(s.salqty), min(s.salqty)
from DEPT d, SALET s
where d.deptno = s.deptno
AND s.yymm = '9510'
GROUP BY d.dname
order by d.loc ;
no rows selected
==> 실행 오류 발생
SCOTT@CRAFT.WORLD>select d.dname, d.loc, sum(s.salqty), avg(s.salqty), max(s.salqty), min(s.salqty)
from DEPT d, SALET s
where d.deptno = s.deptno
AND s.yymm = '9510'
GROUP BY d.dname, d.loc
order by d.loc ;
Execution Plan
나) 뷰를 이용한 조인
SCOTT@TGDOM>CREATE VIEW DEPT_SAL (deptno, yymm, totqty, avgqty, maxqty, minqty)
AS SELECT deptno, yymm, SUM(salqty), AVG(salqty), MAX(salqty), MIN(salqyt)
FROM SALET
GROUP BY deptno, yymm ;
SCOTT@TGDOM>SELECT d.dname, v.totqty, v.avgqty, v.maxqty, v.minqty
FROM DEPT d, DEPT_SAL v
where d.deptno = v.deptno
AND v.yymm='9510'
ORDER BY loc ;
다) 인라인(lnline) 뷰의 활용
SELECT d.dname, v.totqty, v.avgqty, v.maxqty, v.minqty
FROM ( SELECT deptno, SUM(salqty) as totqty,
AVG(salqty) as avgqty,
MAX(salqty) as maxqty,
MIN(salqty) as minqty
FROM SALET
WHERE yymm='9510'
GROUP BY deptno ) v, DEPT D
WHERE v.deptno = d.deptno
ORDER BY d.loc ;
Execution Plan
\
1 0 SORT (ORDER BY) (Cost=5 Card=1 Bytes=88)
2 1 NESTED LOOPS (Cost=4 Card=1 Bytes=88)
3 2 VIEW (Cost=3 Card=1 Bytes=65)
4 3 SORT (GROUP BY) (Cost=3 Card=1 Bytes=35)
5 4 TABLE ACCESS (FULL) OF 'SALET' (Cost=1 Card=1 Bytes=35)
6 2 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (Cost=1 Card=4 Bytes=92)
7 6 INDEX (RANGE SCAN) OF 'XPK_DEPT' (NON-UNIQUE) (Cost=1 Card=4)
(2) 클라이언트 툴의 문제 해결을 위한 뷰
특정 툴에 따른 실행된 SQL 차가 발생 하고, 이를 뷰를 통해서 FIX
(3) 악성 검색을 방지 하기 위한 뷰
사용자가 아무런 조건을 사용하지 않았다면 기본적으로 뷰내에 삽입된 현재월이 처리범위가 되어 '방호벽'
역할을 하므로 전체 테이블에 액세스하여 'GROUP BY' 하는 악성 검색을 막을 수 있다.
CREATE VIEW ITEM_SALE ( saldate, item, highqty, lowqty, totqty )
AS SELECT x.saldate, x.item,
SUM(DECODE(y.type, 'H', x.salqty)),
SUM(DECODE(y.type, 'L', x.salqty)),
SUM(x.salqty))
FROM SALET x, ITEM y
WHERE x.item = y.item
AND x.saldate like to_char(sysdate,'yymm'))||'%'
GROUP BY x.saldate, item ;
SELECT saldate, item, highqty, lowqty, totqty
FROM ITEM_SALE
WHERE item ='ABCD';
SELECT saldate, item, highqty, lowqty, totqty
FROM ITEM_SALE
WHERE saldate = '951001';
(4) 힌트를 사용한 뷰
CREATE VIEW MAX_NUM( saldept, maxseq)
AS SELECT + INDEX_DESC (A pk_index) */
saldept, ordseq + 1
FROM ORDER A
WHERE saledept > ' '
AND rownum = 1 ;
(1) 데이터 모델의 변경에 대한 독립성을 위한 뷰
Application 의 데이타 모델에 대한 독립성 보장
a. 기존에 사용하던 테이블들의 이름을 다음과 같이 변경
b. 과거 사용하던 오브젝트 명칭으로 뷰를 생성
(2) 업무규칙의 변경에 독립성을 부여하기 위한 뷰
CREATE or REPLACE VIEW overtime
( empno, work_day, work_hour, pay_amt )
AS SELECT empno, work_day, work_hour
decode(sign(2-work_hour), 1,5000,
decode(sign(4-work_hour), 1,12000,
decode(sign(5-work_hour), 1,18000, 30000 )))
FROM EMPWORK ;
;
(3) 상황에 따라 액세스 경로를 변경시키기 위한 뷰
뷰내에 힌트를 삽입하여 상황에 따라 재생성, 혹은 대체시킨다면 애플리케이션의 수정 없이도 우리가 원하는 액세스
경로를 유도할 수 있다.
(4) 명칭을 통일하기 위한 뷰
(1) 불규칙적인 정령을 위한 뷰
CREATE VIEW PROD_VIEW
( sortno, prod, item, pdate, cost, prodqty )
AS SELECT DECODE(prod, 'A',3,'B',1,'C',4,'D',2,'E',6,'F',8,'G',7,'H',5),
prod, item, pdate, cost, nvl(prodqty,0)
FROM PRODUCT ;
SELECT * FROM PROD_VIEW
WHERE pdate like '9501%'
ORDER BY sortno;
(2)GROUP BY 결과를 다시 가공하기 위한 뷰
'GROUP BY' 한 결과를 COUNT,MAX,MIN,AVG 하고자 할 경우
CREATE VIEW SAL_VIEW
( prod, prod_cnt, prod_avg )
AS SELECT prod, COUNT(*), AVG(salqty)
FROM SALET
GROUP BY prod ;
SELECT COUNT(*)
FROM SAL_VIEW
WHERE prod like 'AB%';
(3) GROUP BY 결과를 같은 줄에 ?주기 위한 뷰
CREATE VIEW GROUP_VIEW ( empno, yymmdd, type, cnt )
AS
SELECT empno, accept_date, '1', COUNT(*)
FROM 접수
GROUP BY empno, accept_date
union all
SELECT empno, action_date,'2',COUNT(*)
FROM 처리
GROUP BY empno, action_date
union all
SELECT empno, approv_date, '3', COUNT(*)
FROM 결재
GROUP BY empno, approv_date ;
SELECT empno,
SUM(DECODE(type,'1',cnt)) "접수 횟수",
SUM(DECODE(type,'2',cnt)) "처리 횟수",
SUM(DECODE(type,'3',cnt)) "결재 횟수",
SUM(cnt) "소계"
FROM GROUP_VIEW
WHERE yymmdd like '9510%'
GROUP BY empno ;
(4) 소계와 내역을 하나의 SQL 로 처리하기 위한 뷰
하나의 뷰에서 모든 컬럼을 나타내고자 하면 뷰는 자료 사전에서 오브젝트, 컬럼의 정보, 뷰를 생성한 'SELECT' 의 원문을 저장하고 있어,
파싱시 자료사전에서 가져와야할 정보의 양이 늘어 결과적으로 뷰의 파싱타임이 증가 한다.
따라서, 뷰 생성시 필요한 칼럼만으로 구성된 다수의 뷰를 생성 하도록 한다.