제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 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 VIEW (cr=2 pr=0 pw=0 time=119 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)

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.

(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)

2 1 NESTED LOOPS (Cost=4 Card=1 Bytes=88)
3 2 VIEW (Cost=3 Card=1 Bytes=65)

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' 의 원문을 저장하고 있어,
파싱시 자료사전에서 가져와야할 정보의 양이 늘어 결과적으로 뷰의 파싱타임이 증가 한다.
따라서, 뷰 생성시 필요한 칼럼만으로 구성된 다수의 뷰를 생성 하도록 한다.