봉봉의 개인 블로그

VIEW 본문

관련 지식

VIEW

봉봉이네 2017. 5. 26. 13:44

VIEW 란?

뷰라는 것은 쉽게 생각하면 테이블을 보기 위한 창문 정도로 생각하면 된다.
테이블을 방으로 생각하고 뷰를 그 방을 보는 창문으로 생각해 보자.
창문을 통해 방안을 보면 방안의 모든 내용을 볼 수 없지만, 내가 보려고 하는 물건이 정해져 있다면, 그것만 창의 시야 안에 가져다 놓으면 다른 것은 볼 필요가 없을 것이다. 
오히려 방에 들어가서 직접 보고자하는 물건을 찾는 것보다 미리 내가 지정한 것만을 창 앞에 가져다 놓고 그것만을 본다면 더 편리할 것이다.

테이블과 뷰도 같은 이치이다.
창문을 통해 본다는 것은 창문이 물건을 가지고 있는 것이 아니고 방에 있는 물건이 창을 통해 비치는 것뿐이다.
내가 그 방을 보고자해서 창문을 두드리면 창문은 그 순간의 방안의 모습을 비춰놓고 창을 닫는다.
그것을 우리는 보는 것이다.
뷰를 통해 데이터를 읽어 오면 SQL이 실행되는 순간에 테이블의 상태 그대로가 읽혀온다.

뷰의 역할이 바로 그것이다.
뷰의 의미는 하나의 select 문과 같다고 생각하면 된다.
물론 뷰를 통해 insert, update, delete가 가능하지만 대개의 경우는 select를 위해 사용한다.

뷰란 한개 이상의 기본 테이블이나 다른 뷰를 이용하여 생성되는 가상 테이블(virtual table)이다.
뷰는 기존에 생성된 테이블 또는 다른 뷰에서 접근할 수 있는 전체 데이터 중에서 일부만 접근할 수 있도록 제한하기 위한 기법이다
뷰를 가상 테이블이라하는 이유는 테이블은 디스크 공간이 할당되어 데이터를 저장할 수 있지만, 뷰는 데이터 딕셔너리 테이블에 뷰에 대한 정의만 저장하고 디스크에 저장 공간이 할당되지 않는다. 하지만 일반 사용자들은 SQL 문을 사용하여 테이블에 저장된 데이터를 검색하고 조작하는 것과 유사하게 뷰를 이용할 수 있다.
뷰는 물리적인 저장 공간과 데이터를 갖지는 않지만 테이블에서 파생된 객체이므로 테이블과 유사하게 사용할 수 있다.

  • 뷰를 이용하면 중요한 데이터를 보호하기 위한 보안(security) 목적과, 사용자에게 편의성(flexibility)를 제공하기 위한 목적을 달성하기 위해서이다.
  • 테이블과 같이 뷰에 대한 DML 연산 즉, 쿼리, update, insert, delete가 가능하며 constraint(제약조건)도 부여할 수 있다.
  • 뷰 자체는 데이터를 갖지 않지만, 기초 테이블의 데이터를 조회하고 수정할 수 있는 창과 같다.
  • 뷰는 데이터 값이 아니라 실제적으로는 질의 문장만을 가진다.
  • 오라클에서 뷰를 구성하는 컬럼의 최대 개수는 254개이다.
  • 뷰에 대한 수정 결과는 뷰를 정의한 기본 테이블에 적용된다.
  • 뷰를 정의한 기본 테이블의 데이터가 변경되면 뷰에도 반영된다.
  • 뷰는 기본 테이블에서 정의된 모든 무결성 제약조건을 상속 받는다.
  • 뷰에 대한 정의는 user_views 데이터 딕셔너리 테이블을 통해서 조회할 수 있다.
뷰의 장단점
장점
  • DB의 선택적인 부분만 보여주므로 접근을 제한
  • 다양한 접근 경로 설정
  • 복잡한 질의를 단순화
  • 데이터의 독립성 제공
  • 동일한 데이터를 또 다른 뷰로 표현
  • 한 개의 뷰에 여러 테이블의 데이터를 검색 가능
  • 한 개의 테이블로부터 여러 뷰를 생성 가능
    • 단점
  • 뷰의 정의를 변경할 수 없고 insert,delete,update에 많은 제한이 있음

    • 뷰의 종류

    뷰의 종류는 뷰를 정의하기 위해 사용된 기본 테이블 수에 따라 단순 뷰(simple view) 또는 복합 뷰(complex view)로 구분된다.

    단순 뷰는 하나의 기본 테이블에 의해 정의한 뷰이다. 단순 뷰는 INSERT, DELETE, UPDATE와 같은 DML문을 실행할 수 있다.
    단순 뷰를 대상으로 실행한 DML 문의 처리 결과는 뷰를 정의한 기본 테이블에 적용된다

    복합 뷰는 2개 이상의 기본 테이블로 구성한 뷰이다. 복합 뷰에 대해서는 무결성 제약조건, 표현식, GROUP BY 절의 유무에 따라 DML문을 제한적으로 사용할 수 있다.
    복합 뷰는 DISTINCT, 그룹함수, GROUP BY, START WITH CONNECT BY, ROWNUM을 포함할 수 없다. 그리고 UNION ALL, INTERSECT 등과 같은 집합 연산을 실행할 수 없다.

    특징simple viewcomplex view
    테이블 개수11 개 이상
    함수없음포함
    data group없음포함
    뷰를 통한 DML가능항상 가능한 것은 아님

    뷰의 생성

    뷰 생성시 컬럼 이름을 명시하지 않으면 뷰를 정의하는 기본 테이블의 컬럼 이름을 상속한다. 하지만, 함수나 표현식에 의해 정의되는 컬럼은 별도로 이름을 명시하지 않으면 오류가 발생한다. 뷰에 대한 검색은 SELECT 문을 이용하여 기본 테이블의 데이터 검색 방법과 동일하게 검색할 수 있다.

    【형식】
	CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW 뷰이름
		[(alias[,alias]...]
	AS subquery
	[WITH CHECK OPTION]
	[WITH READ ONLY];

    여기서 각각의 옵션의 의미는 다음과 같다.

    옵션설 명
    OR REPLACE같은 이름의 뷰가 있을 경우 무시하고 다시 생성
    FORCE기본 테이블의 유무에 상관없이 뷰를 생성
    NOFORCE기본 테이블이 있을 때만 뷰를 생성
    ALIAS기본 테이블의 컬럼이름과 다르게 지정한 뷰의 컬럼명 부여
    WITH CHECK OPTION뷰에 의해 access될 수 있는 행(row)만이 삽입, 수정 가능
    WITH READ ONLYDML 작업을 제한(단지 읽는 것만 가능)

    • 뷰를 정의하는 subquery에는 ORDER BY절을 포함할 수 없다. ORDER BY절은 VIEW에서 데이터를 읽을 때 지정됨
    • WITH CHECK OPTION절에서 제약조건 이름을 표기하지 않으면 system default인 SYS_Cn 형태(예:SYS_C000123)로 지정된다.
    • OR REPLACE 옵션을 사용하여 뷰의 정의를 변경할 수 있다.
    (다른 것처럼 alter 문으로 변경하는 것이아니라 OR REPLACE 옵션으로 변경이 가능함)
    • 뷰를 정의하는 질의어는 JOIN, GROUP, subquery를 포함하는 복잡한 문장으로 구성될 수 있고, 뷰의 연산은 기존의 테이블 연산과 동일하다.

    【예제】
SQL> CREATE VIEW dno10_vw
  2  AS SELECT ename,job,sal,deptno
  3     FROM emp WHERE deptno=10;
 
View created.
 
SQL> select * from dno10_vw;
 
ENAME      JOB              SAL     DEPTNO
---------- --------- ---------- ----------
CLARK      MANAGER         2450         10
KING       PRESIDENT       5000         10
MILLER     CLERK           1300         10
 
SQL> select view_name,text
  2  from user_views;
 
VIEW_NAME      TEXT
-------------  ---------------------------------------
DNO10_VW       select ename,job,sal,deptno
               from emp where deptno=10
 
SQL> desc dno10_vw;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 SAL                                                NUMBER(7,2)
 DEPTNO                                             NUMBER(2)
 
SQL>
    뷰의 수정

    뷰의 수정은 기존 뷰에 대한 정의를 삭제한 후 재생성하거나, CREATE 문에서 OR REPLACE 옵션을 이용하여 재정의할 수 있다.
    뷰의 수정 결과는 user_views 데이터 딕셔너리에 저장된다.

    【형식】
	CREATE OR REPLACE VIEW 뷰이름
	AS subquery;

    【예제】

    변경 후변경 전
    SQL> select * from dno10_vw;
 
     EMPNO ENAME      HIREDATE         DEPTNO
---------- ---------- ------------ ----------
      7782 CLARK      09-JUN-81            10
      7839 KING       17-NOV-81            10
      7934 MILLER     23-JAN-82            10
 
SQL> 
    

    SQL> select * from dno10_vw;
 
ENAME      JOB              SAL     DEPTNO
---------- --------- ---------- ----------
CLARK      MANAGER         2450         10
KING       PRESIDENT       5000         10
MILLER     CLERK           1300         10
 
SQL>
    SQL> create or replace view dno10_vw
  2  as select empno,ename,hiredate,deptno
  3     from emp 
  4     where deptno=10;
 
View created.
SQL>
    SQL> desc dno10_vw;
 Name          Null?    Type
 ------------- -------- ------------------
 EMPNO         NOT NULL NUMBER(4)
 ENAME                  VARCHAR2(10)
 HIREDATE               DATE
 DEPTNO                 NUMBER(2)
 
SQL> 
    

    SQL> desc dno10_vw;
 Name          Null?    Type
 ------------- -------- ------------------
 ENAME                  VARCHAR2(10)
 JOB                    VARCHAR2(9)
 SAL                    NUMBER(7,2)
 DEPTNO                 NUMBER(2)
 
SQL>


    뷰의 삭제

    뷰는 실체가 없는 가상 테이블이므로 뷰의 삭제는 user_views 데이터 딕셔너리에 저장된 뷰의 정의를 삭제하는 것을 의미한다.
    따라서 뷰의 삭제는 뷰를 정의한 기본 테이블의 구조나 데이터에는 전혀 영향을 주지 않는다.
    • 뷰의 삭제는 해당 뷰를 생성한 사용자나, DROP ANY VIEW 권한을 가진자만이 가능하다.

    【형식】
	DROP VIEW 뷰이름;

    【예제】
SQL> drop view dno10_vw;
 
View dropped.
 
SQL> select * from user_views where view_name='DNO10_VW';
 
no rows selected
 
SQL>
    단순 뷰의 DML 연산

    하나의 테이블을 기초로하는 단순 뷰는 DML이 아래의 조건에 따라 수행되지만, 다중 테이블로 구성되는 복합 뷰는 DML 연산이 수행되지 않는다.
    단순 뷰는 아래의 조건을 제외하고는 DELETE, UPDATE, INSERT 연산이 가능하다.

    다음 사항 포함시 삭제 않됨아래 포함시 수정 않됨아래 포함시 추가 안됨
    그룹함수그룹함수그룹함수
    GROUP BY 절GROUP BY 절GROUP BY 절
    DISTINCT 키워드DISTINCT 키워드DISTINCT 키워드
    ROWNUM pseudo 컬럼ROWNUM pseudo 컬럼ROWNUM pseudo 컬럼
    표현식으로 정의된 컬럼표현식으로 정의된 컬럼
    뷰에 의해 선택되지 않은 
    NOT NULL 컬럼이 기본 테이블에 있을 때

    뷰에 대한 데이터 조작이 불가능한 경우는 다음과 같은 3가지 경우이다.
    1) 뷰 정의에 포함되지 않은 기본 테이블의 컬럼이 NOT NULL 제약조건으로 지정된 경우에는 뷰에 데이터 삽입이 않된다.
    2) 뷰 정의시 표현식으로 정의된 컬럼에 대해서는 UPDATE, INSERT 문의 실행이 않된다.
    3) 뷰 정의시 그룹 함수, DISTINCT, GROUP BY 절을 포함한 경우에는 모든 종류의 DML 문을 사용할 수 없다.

    【예제】

    삭제 전삭제 후
    SQL> select * from dno10_vw;
 
     EMPNO ENAME      HIREDATE         DEPTNO
---------- ---------- ------------ ----------
      7369 SMITH      17-DEC-80            20
      7499 ALLEN      20-FEB-81            30
      7521 WARD       22-FEB-81            30
      7566 JONES      02-APR-81            20
      7654 MARTIN     28-SEP-81            30
      7698 BLAKE      01-MAY-81            30
      7788 SCOTT      19-APR-87            20
      7844 TURNER     08-SEP-81            30
      7876 ADAMS      23-MAY-87            20
      7900 JAMES      03-DEC-81            30
      7902 FORD       03-DEC-81            20
 
11 rows selected.
 
SQL>
    

    SQL> select * from dno10_vw;
 
     EMPNO ENAME      HIREDATE         DEPTNO
---------- ---------- ------------ ----------
      7369 SMITH      17-DEC-80            20
      7566 JONES      02-APR-81            20
      7788 SCOTT      19-APR-87            20
      7876 ADAMS      23-MAY-87            20
      7902 FORD       03-DEC-81            20
 
SQL>
    SQL> delete from dno10_vw where deptno=30;
 
6 rows deleted.
 
SQL>
    WITH CHECK OPTION

    WITH CHECK OPTION 절을 사용하면 뷰를 통해 참조 무결성(reference integrity)을 검사할 수 있고 DB 레벨에서의 constraint 적용이 가능하다.
    즉, WITH CHECK OPTION 절을 사용한 뷰에서 INSERT와 UPDATE를 수행하면 Error가 발생한다.

    WITH CHECK OPRION의 기능
    • 뷰를 통한 참조 무결성 검사
    • constraint의 적용
    • 데이터 검증 확인

    【형식】
	CREATE VIEW 뷰이름
	AS subquery
	WITH CHECK OPTION CONSTRAINT constraint명;

    【예제】
SQL> create view test_vw
  2  as select empno,ename,hiredate,deptno
  3  from emp
  4  where deptno=10
  5  with check option constraint test_vw_ck;
 
View created.
 
SQL> select * from test_vw;
 
     EMPNO ENAME      HIREDATE         DEPTNO
---------- ---------- ------------ ----------
      7782 CLARK      09-JUN-81            10
      7839 KING       17-NOV-81            10
      7934 MILLER     23-JAN-82            10
 
SQL> update test_vw set deptno=20 where ename='KING';
update test_vw set deptno=20 where ename='KING'
       *
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation
		with check option을 사용한 뷰는 deptno=10인 데이터만 참조가 가능한데, 
		20으로 변경하면 뷰가 더이상 참조할 수 없으므로 error를 발생한다. 
 
SQL> update test_vw set ename='JOE' where empno='7839';
 
1 row updated.
 
SQL> select * from test_vw;
 
     EMPNO ENAME      HIREDATE         DEPTNO
---------- ---------- ------------ ----------
      7782 CLARK      09-JUN-81            10
      7839 JOE        17-NOV-81            10
      7934 MILLER     23-JAN-82            10
 
SQL>
    WITH READ ONLY

    with read only 옵션을 사용하여 뷰를 생성하면 뷰의 속성이 READ ONLY로 바뀌기 때문에 DML 연산을 못한다.

    【형식】
	CREATE VIEW 뷰이름
	AS subquery
	WITH READ ONLY;

    【예제】
SQL> create view test_vw
  2  as select empno,ename,hiredate,deptno
  3     from emp
  4     where deptno=10
  5  with read only;
 
View created.
 
SQL> select * from test_vw;
 
     EMPNO ENAME      HIREDATE         DEPTNO
---------- ---------- ------------ ----------
      7782 CLARK      09-JUN-81            10
      7839 JOE        17-NOV-81            10
      7934 MILLER     23-JAN-82            10
 
SQL> delete from test_vw
  2  where ename='JOE';
delete from test_vw
            *
ERROR at line 1:
ORA-01752: cannot delete from view without exactly one key-preserved table
 
 
SQL>
    complex View

    두 개 이상의 다중 테이블에서 만들어져 그룹함수를 포함하는 뷰를 의미한다.
    subquery 문장에 JOIN, FUNCTION, DISTINCT 등을 포함한다.
    • complex 뷰는 기본적인 DML 연산이 항상 가능한 것은 아니다. 뷰는 두 개 이상의 다중 테이블로부터 JOIN 연산으로 만들어졌기 때문이다. 뷰를 통해 source 데이터의 변형이 있느냐에 따라 실행 가능 여부가 결정된다.
    • 뷰의 어떤 컬럼이 함수나 표현식에서 유래 되었다면 반드시 alias를 사용해야 한다. 하나의 테이블에서 만들어졌다면 쓸 필요가 없지만 2개 이상의 테이블로부터 만들어졌기 때문이다.

    【예제】
SQL> create view test_sum(name,minsal,maxsal,avgsal)
  2  as select d.dname, min(p.sal), max(p.sal), avg(p.sal)
  3     from dept d, emp p
  4     where d.deptno=p.deptno
  5     group by d.dname;
 
View created.
 
SQL> select * from test_sum;
 
NAME               MINSAL     MAXSAL     AVGSAL
-------------- ---------- ---------- ----------
ACCOUNTING           1300       5000 2916.66667
RESEARCH              800       3000       2175
 
SQL>
    Inline 뷰

    인라인 뷰는 FROM 절에서 서브쿼리를 사용하여 생성한 임시 뷰이다. 인라인 뷰는 SQL 문이 실행되는 동안만 임시적으로 정의된다.
    • inline view에서 ORDER BY 절을 사용할 수 있다.

    【형식】
	SELECT column_list
	FROM (subquery) alias
	WHERE 조건;

    참고로

    서브쿼리가 FROM 절에 있으면 이를 Inline view라하고
    서브쿼리가 WHERE 절에 있으면 이를 Nested subquery라 하며
    Nested subquery중에서 참조하는 테이블이 parent, child관계를 가지면 이를 correlated subquery라 한다.

    【예제】
SQL> select a.empno,a.ename,a.sal,a.hiredate,b.maxdate
  2  from emp a, (select deptno, max(hiredate) maxdate
  3               from emp
  4               group by deptno) b
  5  where a.deptno=b.deptno AND a.hiredate < b.maxdate;
 
     EMPNO ENAME             SAL HIREDATE     MAXDATE
---------- ---------- ---------- ------------ ------------
      7369 SMITH             800 17-DEC-80    23-MAY-87
      7566 JONES            2975 02-APR-81    23-MAY-87
      7782 CLARK            2450 09-JUN-81    23-JAN-82
      7788 SCOTT            3000 19-APR-87    23-MAY-87
      7839 KING             5000 17-NOV-81    23-JAN-82
      7902 FORD             3000 03-DEC-81    23-MAY-87
 
6 rows selected.
 
SQL>

    TOP_N 분석 참조

    뷰에 대한 질의의 내부적인 처리과정

    뷰는 디스크 상에 저장공간이나 데이터가 없는 가상 테이블이므로 실체가 없다. 따라서 뷰에 대한 질의는 내부적으로 뷰를 정의한 기본 테이블에 대한 질의로 변환되어 실행된다.

    뷰에 대한 질의가 내부적으로 처리되는 과정은 다음과 같다.
    1) user_views 데이터 딕셔너리에서 뷰에 대한 정의를 조회한다.
    2) 기본 테이블에 대한 뷰의 접근 권한을 확인한다.
    3) 뷰에 대한 질의를 기본 테이블에 대한 질의로 변환한다.
    4) 기본 테이블에 대한 질의를 통해 데이터를 검색한다.
    5) 검색된 결과를 출력한다.

    뷰에 대한 정보 조회

    사용자가 생성한 모든 뷰에 대한 정의는 user_views 데이터 딕셔너리에 저장된다. user_views 데이터 딕셔너리에는 뷰를 정의한 select 문이 문자열로 저장되어 있다.
    뷰는 디스크상에 공간이나 저장된 데이터가 없이 user_views 데이터 딕셔너리에 뷰를 정의한 select 문만 문자열로 저장되어 있는 가상 테이블이다.

    【예제】
SQL> create view dno10_vw
  2  as select ename,job,sal,deptno
  3     from emp where deptno=10;
 
뷰가 생성되었습니다.
 
SQL> select view_name,text
  2  from user_views;
 
VIEW_NAME      TEXT
-------------  ---------------------------------------
DNO10_VW       select ename,job,sal,deptno
               from emp where deptno=10
 
SQL> 

참조: Query REwrite은 동일 쿼리에 대해서 SQL문을 내부적으로 재작성하여 구체화된 뷰를 통해 데이터를 검색
    저작자표시

    '관련 지식' 카테고리의 다른 글

    Maven 정리  (0) 2017.06.26
    servlet의 생명주기  (0) 2017.06.26
    SQL이 아닌 함수(getGeneratedKeys())를 이용한 AutoIncrement 키값 가져오기  (0) 2017.05.25
    MSSQL between을 이용한 날짜 검색  (0) 2017.05.08
    자바(JAVA) 형 변환(String과 int)  (0) 2017.05.06
    공유하기 링크
    '관련 지식' Related Articles more
    Comments