필요한 권한#

  1. 뷰내에서 참조하는 모든 테이블에 대한 접근권한이 필요
  2. create view 권한이 필요하다(grant create view to 사용자명)

! 뷰 종류#

관계뷰(Relational View)#

관계뷰 생성
SQL> create or replace view emp_view as
  2  select empno, ename, deptno
  3  from emp
  4  with read only;

뷰가 생성되었습니다.

SQL> desc emp_view;
 이름                                      널?      유형
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 DEPTNO                                             NUMBER(2)

뷰 생성시 with read only옵션은 이 뷰가 읽기전용임을 알리는 것이다. 선택적인 값이기 때문에 붙이지 않아도 된다.

뷰 정보 보기

SQL> desc user_views;
 이름                                      널?      유형
 ----------------------------------------- -------- ----------------------------
 VIEW_NAME                                 NOT NULL VARCHAR2(30)
 TEXT_LENGTH                                        NUMBER
 TEXT                                               LONG
 TYPE_TEXT_LENGTH                                   NUMBER
 TYPE_TEXT                                          VARCHAR2(4000)
 OID_TEXT_LENGTH                                    NUMBER
 OID_TEXT                                           VARCHAR2(4000)
 VIEW_TYPE_OWNER                                    VARCHAR2(30)
 VIEW_TYPE                                          VARCHAR2(30)
 SUPERVIEW_NAME                                     VARCHAR2(30)

SQL> select view_name, text
  2  from user_views;

VIEW_NAME                      TEXT
------------------------------ -------------------------------------------------------------------------------
EMP_VIEW                        select empno, ename, deptno
                                        from emp

뷰 유효화

다음의 경우에 생성되었던 뷰는 무효화된다.

  1. 기반 테이블이나 뷰의 칼럼 이름을 변경하거나 완전히 삭제
  2. 뷰가 기반을 두고 있는 기반 테이블이나 뷰 삭제
  3. 테이블이나 뷰를 무효화하는 변경은 결국 이에 기반한 뷰 역시 무효화한다.

SQL> select *
  2  from user_updatable_columns
  3  where table_name='EMP_VIEW';

OWNER                          TABLE_NAME                     COLUMN_NAME                UPD INS DEL
------------------------------ ------------------------------ ------------------------------ --- --- ---
SCOTT                          EMP_VIEW                         EMPNO                          NO  NO  NO

SCOTT                          EMP_VIEW                         ENAME                          NO  NO  NO

SCOTT                          EMP_VIEW                         DEPTNO                         NO  NO  NO

SQL> create or replace view emp_view as
  2  select empno, ename, deptno
  3  from emp;

뷰가 생성되었습니다.

SQL> select *
  2  from user_updatable_columns
  3  where table_name='EMP_VIEW';

OWNER                          TABLE_NAME                     COLUMN_NAME                    UPD INS DEL
------------------------------ ------------------------------ ----------------------------- --- --- ---
SCOTT                          EMP_VIEW                         EMPNO                          YES YES YES

SCOTT                          EMP_VIEW                         ENAME                          YES YES YES

SCOTT                          EMP_VIEW                         DEPTNO                         YES YES YES

emp_view를 먼저 with read only 옵션을 사용해서 읽기전용으로 생성했기 때문에 user_updatable_columns 테이블 조회시 모든 칼럼이 update, insert, delete 작업이 가능하지 않도록 되어 있다. 하지만 읽기 전용을 제거하고 다시 보면 YES라는 값이 표시하듯이 update, insert, delete작업이 모두 가능하게 된다. 조인을 통한 결합뷰의 경우 가능여부를 user_updatable_colums 테이블을 조회하는 것으로 확인할수 있다.

instead of 트리거 이용

with check option 제약조건

SQL> create or replace view emp_view_10 as
  2  select *
  3  from emp
  4  where deptno=10
  5  with check option;

뷰가 생성되었습니다.

SQL> select *
  2  from user_updatable_columns
  3  where table_name='EMP_VIEW_10';

OWNER                          TABLE_NAME                     COLUMN_NAME                    UPD INS DEL
------------------------------ ------------------------------ ------------------------------ --- --- ---
SCOTT                          EMP_VIEW_10                    EMPNO                          YES YES YES

SCOTT                          EMP_VIEW_10                    ENAME                          YES YES YES

SCOTT                          EMP_VIEW_10                    JOB                            YES YES YES


OWNER                          TABLE_NAME                     COLUMN_NAME                    UPD INS DEL
------------------------------ ------------------------------ ------------------------------ --- --- ---
SCOTT                          EMP_VIEW_10                    MGR                            YES YES YES

SCOTT                          EMP_VIEW_10                    HIREDATE                       YES YES YES

SCOTT                          EMP_VIEW_10                    SAL                            YES YES YES


OWNER                          TABLE_NAME                     COLUMN_NAME                    UPD INS DEL
------------------------------ ------------------------------ ------------------------------ --- --- ---
SCOTT                          EMP_VIEW_10                    COMM                           YES YES YES

SCOTT                          EMP_VIEW_10                    DEPTNO                         YES YES YES


개의 행이 선택되었습니다.

SQL> select *
  2  from emp_view_10;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM        DEPTNO
---------- ---------- --------- ---------- -------- ---------- ----------  ----------
      7782 CLARK      MANAGER         7839 81/06/09       2450                        10

      7839 KING       PRESIDENT            81/11/17       5000                            10

      7934 MILLER     CLERK           7782 82/01/23       1300                           10

SQL> update emp_view_10
  2  set deptno=20
  3  where empno=7782;
update emp_view_10
       *
1행에 오류:
ORA-01402: 뷰의 WITH CHECK OPTION의 조건에 위배 됩니다

with check option 은 해당 조건으로 생성된 뷰의 데이터를 변경할수 없도록 하는 옵션이다.

인라인 뷰#

SQL> select empno, ename, dname
  2  from (select * from emp where deptno=10emp_10, dept
  3  where emp_10.deptno=dept.deptno;

     EMPNO ENAME      DNAME
---------- ---------- --------------
      7782 CLARK      ACCOUNTING
      7839 KING       ACCOUNTING
      7934 MILLER     ACCOUNTING

여기서는 emp_10 라는 이름의 인라인 뷰를 사용했다.

객체뷰#

SQL> create type emp_type is object(
  2  empno number,
  3  ename varchar2(10),
  4  job varchar2(9),
  5  mgr number);
  6  /

유형이 생성되었습니다.

SQL> create or replace view emp_object_view
  2  of emp_type
  3  with object oid(empnoas
  4  select empno, ename, job, mgr
  5  from emp;

뷰가 생성되었습니다.

SQL> desc emp_object_view;
 이름                                      널?      유형
 ----------------------------------------- -------- ----------------------------
 EMPNO                                              NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)

실체화된 뷰(materialized view)#

관계뷰는 런타임시 결과가 결정되는 일종의 쿼리문의 저장이라고 본다면 실체화된 뷰의 경우는 미리 계산되고 저장된 데이터라는 것이다. 미리 결정되어 있기 때문에 런타임시 추가적인 작업이 거의 없어 관계뷰에 비해 성능상의 잇점이 많다.

SQL> create table my_materialized_view as
  2  select * from all_objects;

테이블이 생성되었습니다.

SQL> select count(*from my_materialized_view;

  COUNT(*)
----------
     49794

SQL> set autotrace on
SQL> set timing on
SQL> select owner, count(object_nametotal_object
  2  from my_materialized_view
  3  group by owner;

OWNER                          TOTAL_OBJECT
------------------------------ ------------
MDSYS                                   859
TSMSYS                                    2
DMSYS                                   189
PUBLIC                                19987
OUTLN                                     7
CTXSYS                                  338
OLAPSYS                                 718
HR                                       34
SYSTEM                                  425
EXFSYS                                  279
SCOTT                                    21

OWNER                          TOTAL_OBJECT
------------------------------ ------------
SH                                      299
OE                                      112
DBSNMP                                   46
ORDSYS                                 1669
ORDPLUGINS                               10
SYSMAN                                 1291
PM                                        9
XDB                                     347
IX                                       46
BI                                        8
SYS                                   22858

OWNER                          TOTAL_OBJECT
------------------------------ ------------
WMSYS                                   232
SI_INFORMTN_SCHEMA                        8

24 개의 행이 선택되었습니다.

경   과: 00:00:00.06

Execution Plan
----------------------------------------------------------
Plan hash value: 2654048818

-------------------------------------------------------------------------------------------

| Id  | Operation          | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------------

|   | SELECT STATEMENT   |                      | 39995 |   663K|   164   (5)00:00:02 |

|   |  HASH GROUP BY     |                      | 39995 |   663K|   164   (5)00:00:02 |

|   |   TABLE ACCESS FULL| MY_MATERIALIZED_VIEW | 39995 |   663K|   158   (2)00:00:02 |

-------------------------------------------------------------------------------------------


Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
         27  recursive calls
          0  db block gets
        748  consistent gets
          0  physical reads
          0  redo size
        962  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         24  rows processed

SQL> create materialized view my_materilized
  2  build immediate
  3  refresh on commit
  4  enable query rewrite
  5  as
  6  select owner, count(*total_object
  7  from my_materialized_view
  8  group by owner;

구체화된 뷰가 생성되었습니다.

경   과: 00:00:01.98
SQL> select *
  2  from my_materilized;

OWNER                          TOTAL_OBJECT
------------------------------ ------------
MDSYS                                   859
TSMSYS                                    2
DMSYS                                   189
PUBLIC                                19987
OUTLN                                     7
CTXSYS                                  338
OLAPSYS                                 718
HR                                       34
SYSTEM                                  425
EXFSYS                                  279
SCOTT                                    21

OWNER                          TOTAL_OBJECT
------------------------------ ------------
SH                                      299
OE                                      112
DBSNMP                                   46
ORDSYS                                 1669
ORDPLUGINS                               10
SYSMAN                                 1291
PM                                        9
XDB                                     347
IX                                       46
BI                                        8
SYS                                   22858

OWNER                          TOTAL_OBJECT
------------------------------ ------------
WMSYS                                   232
SI_INFORMTN_SCHEMA                        8

24 개의 행이 선택되었습니다.

경   과: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1105017901

---------------------------------------------------------------------------------------

| Id  | Operation            | Name           | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------

|   | SELECT STATEMENT     |                |    24 |   720 |     3   (0)00:00:01 |

|   |  MAT_VIEW ACCESS FULL| MY_MATERILIZED |    24 |   720 |     3   (0)00:00:01 |

---------------------------------------------------------------------------------------


Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
          9  consistent gets
          0  physical reads
          0  redo size
        962  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         24  rows processed

앞서 my_materialized_view테이블을 직접 쿼리할 경우 소요시간이 00:00:00.06 이다. 하지만 뷰 생성후 쿼리를 던져보면 같은 데이터를 보는데도 불구하고 00:00:00.01가 소요되었음을 알수 있다. 그럼 여기서 실체화 뷰를 생성하기 위해 사용한 옵션을 하나씩 살펴보자.

  1. build immediate - 실체화뷰를 이루는 실제 데이터를 직접 채울지를 결정하는 옵션이다. build deferred 옵션을 사용하면 나중에 데이터를 채운다. 나중에 채우는 경우는 뷰를 만들 당시 시스템 사용량이 많아 뷰는 일단 만들고 실제 데이터를 나중에 채울때 사용된다.
  2. refresh on commit - 뷰를 만들 당시 데이터를 채우지만 참조하던 테이블의 데이터가 변경되었을 경우 해당 변경을 반영하는 옵션이다. 짐작할수 있듯 참조하는 테이블에 커밋 작업이 이루어질 경우 갱신된다. 하지만 일반 테이블의 갱신 작업보다는 시간이 더 소요되는 단점이 있다. refresh on demand 옵션도 있다.
  3. enable query rewrite - 쿼리를 재작성하는 옵션이다.

Add new attachment

Only authorized users are allowed to upload new attachments.
« This page (revision-2) was last changed on 12-Jan-2008 17:20 by DongGukLee