제5장 뷰의 특징과 활용

1. 뷰의 실제

2. 뷰의 정의 및 특징

테이블과 달리 저장공간을 가지지 않는다.
테이블과 거의 동등하게 취급될 수 있는 논리적인 집합

SELECT - 제한 없음
INSERT,UPDATE,DELETE - 경우에 따라서 다름

각종 권한 부여 가능
인덱스생성, 클러스터링 불가
저장공간을 가지지 않으므로 정규화 규칙을 무시하고 목적에 따라 자유롭게 사용할 수 있음.
하나 이상의 테이블로 생성된 뷰
최종적으로 테이블에 Access
뷰 생성시 변수 사용 불가 - SQL 실행 시 뷰내에 파고들 수 있는 뷰 생성

VIEW SYNCTAX

CREATE [VLDB: OR REPLACE ] [ FORCE | VLDB:NOFORCE ] VIEW view_name
AS subquery
[ WITH CHECK OPTION [VLDB: CONSTRAINT 제약조건 ]]
[VLDB: WITH READ ONLY ]

FORCE : 기본 테이블 유무에 관계없이 VIEW를 생성
WITH CHECK OPTION : VIEW에 의해 엑세스될 수 있는 행만이 입력되거나
변경될 수 있음을 지정 합니다.
WITH READ ONLY : SELECT만 가능한 VIEW의 생성
함수를 사용한 컬럼은 반드시 ALIAS를 지정해야 합니다

3. 뷰의 실행계획 수립 원리

뷰로 생성된 논지적인 집합은 항상 먼저 수행되는 것이 아닌, 뷰를 사용한 SQL 과 합성되면서 작용된다.

View 생성


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

View 사용 - Group by 후에 Join 발생


select * from EMP1_VIwS x, dept y
where x.deptno = y.deptno
and x.deptno between 10 and 50 ;

Rows Row Source Operation
\--\--\--\- \--\--\

--\-
0 STATEMENT

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)

View 사용 - Join 발생 후에 Group by


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
\--\--\--\- \--\--\

--\-
0 STATEMENT

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 이 합성을 통해서 생성 되므로,
항상 뷰 내에서 드라이빙 되도록, 혹은 항상 뷰 밖에서 드라이빙 되도록 조치 하는것

처리 범위를 확실히 줄여줄수 있는 조건을 뷰내에 존재 하게 하여 어떤 방법으로 사용해도 실행 계획이 항상 보장 받도록 조치
뷰내의 조건을 가장 열악한 조건이 되도록 조치 하여 뷰외의 SQL 에서 항상 드라이빙 조건이 도도록 조치

3. 뷰의 활용

뷰를 사용하는 일반적인 목적은 다음과 같이 분류할 수 있다.

(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

특정 데이타에 대해서만 변경 자체를 막는 뷰

DEPTNO 가 '10','30' 이 아닌 경우에는 입력, 수정을 할 수 없다.


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.

뷰의 실행경로 확실히 하는법

특정한 칼럼에 대해서만 입력, 수정을 허용하지 않기를 원한다면 해당 컬럼을 가공 시킨 뷰를 생성 한다.
검색을 제외한 모든 데이터의 처리를 원천적으로 봉쇄하기를 원한다면 'WITH READ ONLY' 옵션을 사용한 뷰를 생성한다.
어떤 컬럼의 특정한 범위에 있는 값에 대해서만 데이터의 처리를 봉쇄하고자 한다면 'WITH CHECK OPTION' 을 사용한 뷰를 생성한다.

(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

--
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=58)
1 0 SORT (GROUP BY) (Cost=4 Card=1 Bytes=58)
2 1 NESTED LOOPS (Cost=2 Card=1 Bytes=58)
3 2 TABLE ACCESS (FULL) OF 'SALET' (Cost=1 Card=1 Bytes=35)
4 2 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (Cost=1 Card=4 Bytes=92)
5 4 INDEX (RANGE SCAN) OF 'XPK_DEPT' (NON-UNIQUE) (Cost=1 Card=4)

나) 뷰를 이용한 조인


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
\

-\-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=1 Bytes=88)

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 로 처리하기 위한 뷰

라. 임시 작업을 위한 뷰

5 뷰 파싱타임의 절약

하나의 뷰에서 모든 컬럼을 나타내고자 하면 뷰는 자료 사전에서 오브젝트, 컬럼의 정보, 뷰를 생성한 'SELECT' 의 원문을 저장하고 있어,
파싱시 자료사전에서 가져와야할 정보의 양이 늘어 결과적으로 뷰의 파싱타임이 증가 한다.
따라서, 뷰 생성시 필요한 칼럼만으로 구성된 다수의 뷰를 생성 하도록 한다.