컴퓨토피아

DBIO를 작성할 때 특히 VIEW나 EXECSQL을 작성할 때 SQL에디터에서 컬럼 줄을 가지런하게 맞추기 위해서는 폰트설정을 해야 한다.  

1. View는 논리적인 가상 Table

View는 한 개 이상의 Table 질의에 대한 가상 Table입니다.

  View는 실제로 data를 포함하거나 저장하지 않는 논리적인 구조로 Base Table의 data를

  조회하거나 변경할 수 있습니다.

 

  (1) View의 문법 및 생성

      create [or replace] [force | noforce]  view  view명

      (column명1, column명2, column명3,…)

as

Select문

[with check option [constraint constraint명]]

[with read only]

      ㄱ. [or replace]는 동일한 view가 존재할 경우 기존 view를 대체(replace)시킨다.

      ㄴ. [force] option은 base table이 존재하지 않을 경우에도 강제로 view를 만든다.

         [noforce] option은 base table이 없는 경우 view가 작성되지 않게 한다.(default)

      ㄷ. [with check option]은 select문의 where절의 조건에 대한 checking을 합니다.

         [with read only]는 view를 통한 data의 변경을 금지하고 조회만 가능하게 한다.

ㄹ. emp Table에서 부서 번호가 20인 Row를 얻는 view를 생성시킨다.

SQL> create view emp_20_vu

  2  as

  3  select empno, ename, sal, deptno

  4  from   emp

  5  where  deptno = 20

  6  order  by sal;

뷰가 생성되었습니다.

ㅁ. view의 구조는 아래와 같다.

SQL> desc emp_20_vu

 이름                                          널?      유형

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

 EMPNO                                           NUMBER(4)

 ENAME                                           VARCHAR2(10)

 SAL                                               NUMBER(7,2)

 DEPTNO                                          NUMBER(2)

ㅂ. view를 이용해 자료를 조회한다.

SQL> select empno, ename, sal, deptno

  2  from   emp_20_vu;

 

    EMPNO ENAME            SAL    DEPTNO

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

     7369   SMITH             800        20

     7876   ADAMS           1100        20

     7566   JONES            2975        20

     7788   SCOTT           3000        20

     7902   FORD             3000        20

ㅅ. column alias를 이용한 view 생성

   emp Table에서 column alias를 이용해 자료를 조회한다.

 SQL> create or replace view emp_20_vu

  2  (eno, ename, salary, deptno)

  3  as

  4  select empno, ename, sal * 1.15, deptno

  5  from   emp

  6  where  deptno = 20

  7  order  by 3;

뷰가 생성되었습니다.

     o. view를 이용해 자료를 조회한다.

 SQL> select eno, ename, salary, deptno

  2  from   emp_20_vu;

 

      ENO ENAME         SALARY    DEPTNO

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

     7369 SMITH               920        20

     7876 ADAMS             1265        20

     7566 JONES           3421.25        20

     7788 SCOTT             3450        20

     7902 FORD               3450        20

  (2) view를 사용하는 장점

     ㄱ. base table을 직접 access하지 않으므로 보안에 유리합니다.

     ㄴ. 복잡한 질의를 간단하게 접근할 수 있다.

     ㄷ. View는 가상의 table이므로 삭제해도 base table에는 전혀 영향이 없다.

(3) View의 원리

   VIEW는 user_view라는 data dictionary에 SELECT문으로서 저장되어 있다.

  

   ㄱ. user_views의 구조를 살펴보자.

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)

   ㄴ. user_views에 대해 부분 질의를 하면 아래와 같다.

SQL> select view_name, text

  2  from   user_views;

 

VIEW_NAME       TEXT

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

EMP_20_VU        select empno, ename, sal * 1.15, deptno

from   emp

where  deptno = 20

order  by 3;

     ㄷ. emp_20_vu에 대한 질의를 하면 oracle은 user_views라는 data dictionary를 통해

emp_20_vu를 작성할 때 사용한 질의를 찾아서 실행합니다.

(4) 복잡한 View의 실습

    ㄱ. s_dept와 s_emp에서 부서명과 평균급여액, 최고급여액을 구하는 view이다.

SQL> create view dept_avgmax_vu

  2  (dept_name, avg_sal, max_sal)

  3  as select d.name, avg(e.salary), max(e.salary)

  4  from s_dept d, s_emp e

  5  where d.id = e.dept_id

  6  group by d.name;

    ㄴ. 생성된 view를 생성하면 아래와 같다.

SQL> select dept_name, avg_sal, max_sal

  2  from   dept_avgmax_vu;

 

DEPT_NAME             AVG_SAL   MAX_SAL

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

Administration                 2025      2500

Finance                        1450      1450

Operations                   1086.8      1450

Sales                     1367.8571      1525

(5) View를 통한 data 변경

   ㄱ. View가 하나의 Table을 사용하고 변경할 권한이 있으면, View를 참조하는

      base Table을 변경할 수 있다.

   ㄴ. View가 여러 개의 Table을 사용한다면 View를 통한 data변경은 힘들다.

   ㄷ. dept Table을 이용해 view를 생성하고, insert문을 이용해 data를 입력한다.

 SQL> create view dept_vu

  2  as

  3  select * from dept;

 

뷰가 생성되었습니다.

 

SQL>  insert into dept_vu (deptno, dname, loc)

  2   values (50, 'MANAGE', 'KOREA');

 

1 개의 행이 만들어졌습니다.

     ㄹ. 실제 dept Table을 확인해 보자.

 SQL> select * from dept;

 

   DEPTNO DNAME          LOC

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

       10 ACCOUNTING     NEW YORK

       20 RESEARCH       DALLAS

       30 SALES          CHICAGO

       40 OPERATIONS     BOSTON

50 MANAGE         KOREA

 

(6) View의 삭제

     drop  view  view명;

 

2. Sequence

  Sequence는 Table에서 유일한 번호를 자동으로 만들어 주는 oracle object입니다.

 

(1) 문법

    create sequence  sequence명

    [increment by n]             è default 1

    [start with n]                è default 1

    [maxvalue n]                è default 10의 27승

    [minvalue n]                 è maxvalue일때 시작값

    [cycle]                      è default nocycle

    [cache n | nocache]         è cache n의 default 20

 

ㄱ. increment by는 sequence를 증가할 때 증가분이다.

ㄴ. start with는 sequence의 최초값이다.

ㄷ. maxvalue는 10의 27승, minvalue는 –10의 26승까지 값을 가진다.

ㄹ. cache 다음의 수는 oracle이 미리 할당하고 유지할 값의 수이다.

ㅁ. cycle은 maxvalue나 minvalue까지 값이 모두 생성되었을 때 계속 sequence가

생성되도록 하는 option이다.

     ㅂ. cycle option은 기본키(PK)로 정의된 column에는 사용하지 않습니다.

(2) sequence의 값 구하기

ㄱ.    현재값 구하기

sequence명.currval

ㄴ.    다음값 구하기

sequence명.nextval

(3) sequence 변경

alter sequence  sequence명

    [increment by n]                 

    [maxvalue n]               

    [minvalue n]                

    [cycle]                     

    [cache n | nocache]        

 

    여기서 주의할 것은 start with는 변경할 수 없다는 것이다.

(4) sequence 삭제

   drop  sequence  sequence명;

(5) sequence에 대한 data 조회

   sequence에 대한 data 조회는 user_sequences data dictionary를 사용합니다.

 

ㄱ. user_sequences의 구조를 살펴보자.

SQL> desc user_sequences

 이름                                                  널?      유형

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

 SEQUENCE_NAME                             NOT NULL VARCHAR2(30)

 MIN_VALUE                                               NUMBER

 MAX_VALUE                                              NUMBER

 INCREMENT_BY                               NOT NULL NUMBER

 CYCLE_FLAG                                             VARCHAR2(1)

 ORDER_FLAG                                             VARCHAR2(1)

 CACHE_SIZE                                  NOT NULL NUMBER

 LAST_NUMBER                               NOT NULL NUMBER

ㄴ. 현재 생성된 sequence를 살펴보자.

SQL> select sequence_name, min_value, max_value, increment_by, last_number

  2  from   user_sequences;

 

SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY LAST_NUMBER

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

S_CUSTOMER_ID            1      9999999                1         216

S_DEPT_ID                  1      9999999                1          51

S_EMP_ID                   1      9999999                1          26

S_ORD_ID                   1      9999999                1         113

S_PRODUCT_ID             1      9999999                1       50537

S_REGION_ID               1      9999999                1            6

S_WAREHOUSE_ID          1      9999999                1       10502

 

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

 

 

 

 

(6) sequence를 이용한 실습

ㄱ. tb_seq라는 table을 생성하자.

SQL> create table tb_seq

  2  (

  3   id   number(7) not null,

  4   name varchar2(10)

  5  );

 

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

ㄴ. tb_seq_id_seq 라는 sequence를 생성하여 tb_seq의 id라는 column에 이용하자.

SQL> create sequence tb_seq_id_seq

  2  increment by 1

  3  start with 1

  4  maxvalue 9999999

  5  cycle;

 

주문번호가 생성되었습니다.

ㄷ. tb_seq table에 insert시 sequence를 이용하자.

SQL> insert into tb_seq (id, name)

  2  values (tb_seq_id_seq.nextval, '박대환');

1 개의 행이 만들어졌습니다.

ㄹ. 실제 data를 확인하자.

SQL> select id, name from tb_seq;

       ID NAME

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

        1   박대환

ㅁ. 현재 sequence의 값을 알아보자.

SQL> select tb_seq_id_seq.currval from dual;

 

  CURRVAL

---------

        1

 

 

 

 

3. Synonym

  Synonym은 DB object(Table, View, Procedure, Function)등에 대한 별명입니다.

 

(1) Synonym 생성 문법

   Create [public] synonym synonym명

   For owner.object명;

 

ㄱ. public은 모든 사용자가 접근 가능하도록 만든 것이다.

ㄴ. owner는 object를 소유한 oracle 사용자명이다.

   ㄷ. emp_20_vu라는 view에 대한 synonym emp20을 생성하자.

 SQL> create synonym emp20

  2  for    emp_20_vu;

 

동의어가 생성되었습니다.

(2) Synonym 삭제 문법

   Drop [public] synonym synonym명;

(3) 현 사용자가 속한 private synonym을 조회하려면 user_synonyms data dictionary를 사용한다.

ㄱ. user_synonyms의 구조를 살펴보자.

SQL> desc user_synonyms

 이름                                         널?      유형

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

 SYNONYM_NAME                        NOT NULL VARCHAR2(30)

 TABLE_OWNER                                     VARCHAR2(30)

 TABLE_NAME                           NOT NULL VARCHAR2(30)

 DB_LINK                                            VARCHAR2(128)

ㄴ. 현 사용자에 속한 synonym을 조회하자.

SQL> select synonym_name, table_owner, table_name

  2  from   user_synonyms;

 

SYNONYM_NAME     TABLE_OWNER     TABLE_NAME

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

EMP20                  SCOTT            EMP_20_VU

 

 

 

ㄷ. synonym을 이용해 data를 조회하자.

SQL> select * from emp20;

      ENO  ENAME       SALARY    DEPTNO

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

     7369  SMITH              920          20

     7876  ADAMS            1265          20

     7566  JONES          3421.25          20

     7788  SCOTT            3450          20

     7902  FORD             3450           20

 

4. Index

Index는 Table의 Row를 더 빨리 access 하기 위해 사용하는 Object이다.

Index를 적절히 생성하면 Oracle은 Application의 속도는 빨라질 것이다.

 

(1) Index 생성

TABLE 생성시 Primary Key나 Unique Constraint를 지정하면 Unique Index가 자동적으로 만들어진다.

 

ㄱ. 문법

CREATE [UNIQUE] INDEX index명

    ON table명(column명[, column명...]) ;

ㄴ. s_emp Table에서 name column을 index로 생성해보자.

SQL> create index s_emp_name_idx

  2  on s_emp (name);

 

인덱스가 생성되었습니다.

 

  (2) Index 삭제

     INDEX는 수정할 수 없으며, 수정하려면 삭제하고 다시 생성한다.

 

ㄱ. 문법

DROP INDEX index명 ;

    ㄴ. index s_emp_name_idx을 삭제해보자.

 SQL> drop index s_emp_name_idx;

 

인덱스가 삭제되었습니다.

  (3) 현 사용자가 속한 index를 조회하려면 user_indexes data dictionary를 사용한다.

ㄱ. user_indexes의 구조를 살펴보자.

 SQL> desc user_indexes

 이름                                           널?      유형

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

 INDEX_NAME                             NOT NULL VARCHAR2(30)

 INDEX_TYPE                                         VARCHAR2(27)

 TABLE_OWNER                           NOT NULL VARCHAR2(30)

 TABLE_NAME                            NOT NULL VARCHAR2(30)

 TABLE_TYPE                                         VARCHAR2(11)

 UNIQUENESS                                         VARCHAR2(9)

 COMPRESSION                                        VARCHAR2(8)

 PREFIX_LENGTH                                      NUMBER

 TABLESPACE_NAME                                  VARCHAR2(30)

 INI_TRANS                                            NUMBER

 MAX_TRANS                                          NUMBER

 INITIAL_EXTENT                                     NUMBER

 NEXT_EXTENT                                       NUMBER

 MIN_EXTENTS                                        NUMBER

 MAX_EXTENTS                                       NUMBER

 PCT_INCREASE                                       NUMBER

 PCT_THRESHOLD                                     NUMBER

 INCLUDE_COLUMN                                    NUMBER

 FREELISTS                                            NUMBER

 FREELIST_GROUPS                                    NUMBER

 PCT_FREE                                             NUMBER

 LOGGING                                              VARCHAR2(3)

 BLEVEL                                               NUMBER

 LEAF_BLOCKS                                         NUMBER

 DISTINCT_KEYS                                       NUMBER

 AVG_LEAF_BLOCKS_PER_KEY                         NUMBER

 AVG_DATA_BLOCKS_PER_KEY                        NUMBER

 CLUSTERING_FACTOR                                 NUMBER

 STATUS                                               VARCHAR2(8)

 NUM_ROWS                                            NUMBER

 SAMPLE_SIZE                                          NUMBER

 LAST_ANALYZED                                      DATE

 DEGREE                                               VARCHAR2(40)

 INSTANCES                                           VARCHAR2(40)

 PARTITIONED                                         VARCHAR2(3)

 TEMPORARY                                          VARCHAR2(1)

 GENERATED                                          VARCHAR2(1)

 SECONDARY                                          VARCHAR2(1)

 BUFFER_POOL                                        VARCHAR2(7)

 USER_STATS                                         VARCHAR2(3)

 DURATION                                            VARCHAR2(15)

 PCT_DIRECT_ACCESS                                 NUMBER

 ITYP_OWNER                                          VARCHAR2(30)

 ITYP_NAME                                            VARCHAR2(30)

 PARAMETERS                                         VARCHAR2(1000)

 GLOBAL_STATS                                       VARCHAR2(3)

 DOMIDX_STATUS                                      VARCHAR2(12)

 DOMIDX_OPSTATUS                                   VARCHAR2(6)

 FUNCIDX_STATUS                                     VARCHAR2(8)

ㄴ. user_indexes의 내용을 살펴보자.

 SQL> select index_name, table_name

  2  from   user_indexes;

 

INDEX_NAME                         TABLE_NAME

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

EMPCOPY_EMPNO_PK                EMP_COPY

PK_DEPT                             DEPT

PK_EMP                              EMP

S_CUSTOMER_ID_PK                 S_CUSTOMER

S_DEPT_ID_PK                       S_DEPT

S_DEPT_NAME_REGION_ID_UK       S_DEPT

S_EMP_ID_PK                        S_EMP

S_EMP_USERID_UK                   S_EMP

S_INVENTORY_PRODID_WARID_PK   S_INVENTORY

S_ITEM_ORDID_ITEMID_PK           S_ITEM

S_ITEM_ORDID_PRODID_UK           S_ITEM

 

S_ORD_ID_PK                         S_ORD

S_PRODUCT_ID_PK                   S_PRODUCT

S_PRODUCT_NAME_UK               S_PRODUCT

S_REGION_ID_PK                     S_REGION

S_REGION_NAME_UK                 S_REGION

S_TITLE_TITLE_PK                   S_TITLE

S_WAREHOUSE_ID_PK                S_WAREHOUSE

 

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

출처 : http://simmys.tistory.com/category/DataBase/Oracle

송혁 : SQL Server 2005 Query optimizer의 변경된 내용 제 2강 : SQL Server 2005의 인덱싱 된 뷰 와 포괄 열 인덱스에 대해서…

이름: 송혁 Email : hyok81@nate.com 넥슨 DSM팀 DBA로 근무

1. 시작 일반적으로 하나의 테이블에는 하나의 클러스터 인덱스만 존재 할 수 있으며, 힙 영역에 클러스터 인덱스를 만들게 되면 해당 데이터 영역은 클러스터 영역으로 변하게 됩니다. 서비스에 따라 데이터 갱신에 대한 비용보다 많은 조회에 의한 비용이 많이 들 수 있으며 이럴 경우 두 개 이상의 클러스터 인덱스가 필요 할 수 있을 것입니다. 기존 SQL Server 2000에서는 인덱싱 된 뷰를 생성 하여 두 개 이상의 클러스터 인덱스가 있는 것처럼 활용이 가능 했으며 2005에서는 인덱싱 된 뷰 와 새롭게 추가된 포괄 열 인덱스로써 구현이 가능 합니다. 인덱싱 된 뷰, 포괄 열 인덱스에 대한 활용방안, 두 가지의 차이점 및 유사점에 대해서 시작 하도록 하겠습니다. 2. 인덱스란? 인덱스를 우리말로 번역을 하면 색인 이란 단어로 번역 될 수 있습니다. 색인을 국어사전에서 찾아보면 이와 같이 정의를 하고 있습니다. “책 속의 내용 중에서 중요한 단어나 항목, 인명 따위를 쉽게 찾아볼 수 있도록 일정한 순서에 따라 별도로 배열하여 놓은 목록” SQL Server에서 불리는 색인 즉 인덱스도 위 단어처럼 책 속이 아닌 테이블에 있는 내용을 보다 쉽게 찾기 위해 따로 일정한 순서로 정리해둔 목록 이라고 볼 수 있습니다. SQL Server또는 다른 DBMS의 경우에도 인덱스가 RDBMS에서 얼마나 중요한 것인지 누구나 알고 있을 것 입니다. 보다 빨리 데이터를 조회하고 갱신하기 위해서는 인덱스라는 것이 필요하고 인덱스가 존재 함으로써 DBMS의 성능의 차이도 발생 할 수 있으며, 잘 사용한다면 성능을 극대화 시킬 수 있지만 잘못 사용한다면 오히려 성능 문제를 일으킬 수 있습니다. SQL Server는 이전부터 클러스터 인덱스와 넌 클러스터 인덱스 두 가지 종류의 인덱스를 제공 하고 있습니다. 두 가지의 가장 큰 차이점은 소스 데이터를 리프레벨에 포함하는 여부 입니다. 다른 일반적인 구조는 두 가지 모두 비슷합니다. 클러스터 인덱스는 소스 데이터를 포함 하고 있기에 기본적으로는 하나밖에 생성을 할 수 없으며, 넌 클러스터 인덱스는 리프 레벨에 소스 데이터가 아닌 키로 정의된 열에 대한 데이터 만을 리프레벨에 가지고 있기에 두 개 이상 생성이 가능 합니다. “그렇다면 소스데이터가 여러 개가 있다면 클러스터 인덱스도 여러 개 만들 수 있지 않을까요?” 이 물음에 대해서는 아래에서 보다 자세히 살펴보도록 하겠습니다. 아래는 BOL에 설명하는 클러스터 인덱스와 넌 클러스터 인덱스의 내부 구조를 보여 주고 있습니다. 두 개 모두 인덱스 이기에 모두 루트와 데이터 페이지가 존재 하며, 클러스터 인덱스의 데이터 페이지는 실질적인 데이터를 가르키며, 넌 클러스터 인덱스의 데이터 페이지(리프레벨)는 인덱스 페이지라고 부르는 것이 더욱 쉽게 이해할 수 있을 것입니다. 여기서는 이 두 개의 구조적 차이와 자세한 내용을 언급하는 것이 아니지만 보다 쉽게 이해하기 위해서 선수조건이 되면 좋을 것 같습니다. 이 부분에 대해 더 자세한 내용은 온라인 설명서 및 SQL관련 서적을 참조하시길 바랍니다. [클러스터형 인덱스 구조] ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.ko/udb9/html/26b28045-c3c2-465a-b564-bf2189e93fdc.htm [비클러스터형 인덱스 구조] ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.ko/udb9/html/1efeba1f-f848-4861-9af3-594e5ab3b597.htm 3. 인덱싱 된 뷰, 포괄 열 인덱스 란 이제 위에서 말한 하나의 테이블에 두 개 이상의 클러스터 인덱스를 생성 할 수 있는 방법(?)에 대해서 살펴보도록 하겠습니다. 인덱싱 된 뷰는 SQL Server 2005이전부터 활용된 기능으로써 인덱싱 된 뷰를 추가적으로 생성하여 조회 쿼리에 대해서 많은 이점을 주었습니다. 기존 테이블에 스키마 바인딩 뷰를 생성 후 해당 뷰에 고유한 클러스터 인덱스를 추가 하게 되면 논리적인 뷰가 아닌 물리적으로 저장이 되는 인덱싱 된 뷰가 생성 되게 됩니다. 물론 하나의 테이블 만이 아닌 조인을 이용하여 여러 개의 테이블을 참조하는 뷰에 대해서도 인덱싱 된 뷰는 활용이 가능하기에 많은 조인, 정열 및 집계 등에 많은 비용이 든다면 충분히 고려해 볼 수 있는 기능 입니다. 그러나 데이터를 중복으로 가짐으로써 성능 상 문제가 될 수 도 있습니다. 그러기에 모든 환경에서의 최적의 솔루션이 아닌, 자기 환경에 정확한 이해를 바탕으로 도입 하는 것이 무엇 보다 중요합니다. 주로 데이터의 갱신작업 이 많이 일어나는 OLTP환경보다, OLAP환경과 같은 DW에 보다 유리하며, OLTP환경도 서비스 마다 틀리겠지만 일반적인 환경에서는 데이터 갱신 보다 데이터 조회의 비용이 더 많이 발생되는 것이 일반 적입니다. SQL Server 2005에서 새롭게 추가된 넌 클러스터 인덱스에 INCLUDE라는 포괄 열 인덱스라는 것이 추가 되었습니다. 포괄 열 인덱스라는 INCLUDE절은 기존 넌클러스터 인덱스에 키와 인덱스 리프레벨에 포함되는 데이터를 따로 설정 할 수 있으며 인덱스 키만이 비리프레벨에 존재 하고, 나머지 INCLUDE절에 포함된 열에 대해서는 리프레벨에만 존재 하게 됩니다. 열 개수 및 데이터 형 제한에 대해서 기존 넌클러스터 인덱스보다 유연하여 인덱싱 된 뷰의 기능을 과 유사하게 사용 될 수 있습니다. 하지만, 만약 소스 테이블의 열이 16개 이하이고 LOB 데이터형도 없는 경우, 넌 클러스터 인덱스로 비슷한 기능을 구현은 가능 합니다. 그러나 비리프레벨에 모든 인덱스 키의 값이 존재 하기에 비리프레벨 페이지에 많은 하위 페이지에 대한 포인터 정보를 넣을 수 없어 인덱스 깊이가 증가 될 것 입니다. 이것으로 충분한 성능의 문제를 초래 할 수 도 있습니다. 포괄 열 인덱스가 포용할 수 있는 기존 넌클러스터 인덱스의 제한    - 인덱스의 열 개수의 제한    - 인덱스 크기(900바이트)의 제한    - 인덱스의 데이터 타입의 제한(LOB형)    - 저장 될 모든 열을 인덱스 키로 설정 함으로서 비 리프레벨에도 모든 열의 값이 존재함. 4. 인덱싱 된 뷰, 포괄 열 인덱스와의 차이점 위에서 알아보았듯이 인덱싱 된 뷰는 기존에도 많은 조회용 쿼리를 사용하는 환경에서 사용되었습니다. 정열 된 순서대로 많은 데이터를 가져와야 하고 클러스터 인덱스는 다른 열에 있어 클러스터 인덱스를 활용할 수 없고 covered Index로 처리도 어려운 경우에 인덱싱 된 뷰를 사용하여 보다 빠른 결과를 가져올 수 있었습니다. 또는 집계 데이터 빈번한 조인이 사용하는 쿼리 등등 사용 예를 볼 수 있습니다. 여기서 설명 하지 못한 내용도 충분히 많을 것 이며, 복합 넌 클러스터 인덱스로 불가능한 부분에 대해서 사용하였습니다. 그러나 SQL Server 2005에서는 포괄 열 인덱스를 지원함으로써 단일 테이블, 집계가 없을 경우에는 포괄 열 인덱스를 사용이 가능하기에 보다 쉽게 구현이 가능 하며 관리상의 이슈도 적어집니다. 아래는 간단히 인덱싱 된 뷰와 포괄 열 인덱스에 대한 차이를 정리하였습니다. [표 1] 포괄 열 인덱스와 인덱싱 된 뷰 비교 인덱싱 된 뷰 포괄 열 인덱스 인덱스 키가 아닌 데이터가 비리프레벨에 존재 여부 아니오 아니오 LOB형 지원(TEXT등 제외) 여부 예 예 열 개수 제한 아니오 아니오(1023 개) 집계 값 포함 여부 예 아니오 크기 제한 아니오 아니오 데이터 변경 시 worktable사용 여부 예 아니오 소스 테이블에 대한 키 존재 여부 아니오 예(RID 또는 클러스터키값) 인덱싱 된 뷰는 기존의 넌 클러스터 인덱스 또는 포괄 열 인덱스와는 다르게 원본 테이블에 대해 연결된 키가 존재 하지 않습니다. 스키마 바인딩 뷰 생성 후 만들게 되는 고유한 클러스터 인덱스 키를 이용하여 소스 테이블의 변경된 내역을 반영하게 됩니다. 이러한 소스테이블에 종속된 키가 존재 하지 않아 데이터 갱신 작업 시 wokrtable을 이용하여 실 테이블을 갱신 작업에 대해 저장 후 이 내용을 바탕으로 인덱싱 된 뷰에서 처리하게 됩니다. 아래는 인덱싱 된 뷰가 존재하는 테이블에 대해서 특정 값을 업데이트 쿼리의 실행 계획이며, Table Spool연산자가 추가 및 출력목록에 인덱싱 된 뷰의 클러스터 키와 변경된 컬럼을 확인 할 수 있습니다. 5. 인덱싱 된 뷰 생성시 필요 조건 및 주의 사항. 인덱싱 된 뷰를 만들기 위해서는 몇 가지 주의 사항이 있습니다. 주의 사항 및 지켜줘야 하는 부분에 대해서 잠시 살펴 보도록 하겠습니다. 주의 사항 CREATE VIEW 문이 실행될 때는 ANSI_NULLS 및 QUOTED_IDENTIFIER 옵션이 ON 뷰를 생성시 참조 되는 원본 테이블은 생성시 ANSI_NULLS옵션이 ON 테이블이 아닌 다른 뷰를 참조해서는 안됨. 뷰 생성시 SCHEMABINDING옵션으로 생성 테이블 및 사용자 함수에 대해서는 스키마명.객체명 사용. 뷰의 식에서 참조되는 모든 함수는 결정적 함수. GROUP BY를 사용하는 경우 SELECT List절 에 COUNT가 아닌 COUNT_BIG을 사용(테스트1 참조) Select List절에 * 같이 사용할 수 없고 컬럼 명을 명시적으로 사용 보다 자세한 내용은 BOL을 참조(인덱싱 된 뷰 만들기) ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.ko/udb9/html/f86dd29f-52dd-44a9-91ac-1eb305c1ca8d.htm 뷰 생성 후 해당 옵션으로 생성된 것인지 확인 하기 위해서 OBJECTPROPERTY함수를 사용하여 확인 SELECT      OBJECTPROPERTY ( OBJECT_ID ( 'Sales.vOrders' , 'view' ) , 'ExecIsAnsiNullsOn' )     ,OBJECTPROPERTY ( OBJECT_ID ( 'Sales.vOrders' , 'view' ) , 'ExecIsQuotedIdentOn' )   1 = True   0 = False [테스트 1 - GROUP BY절을 사용하고 COUNT_BIG함수를 사용하지 않을 경우 오류 메시지] SET NUMERIC_ROUNDABORT OFF ; SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT ,    QUOTED_IDENTIFIER, ANSI_NULLS ON ; GO --스키마 바인딩 뷰 생성 IF OBJECT_ID ('Sales.vOrders', 'view' ) IS NOT NULL DROP VIEW Sales .vOrders ; GO CREATE VIEW Sales .vOrders WITH SCHEMABINDING AS    SELECT SUM (UnitPrice *OrderQty *(1.00 -UnitPriceDiscount )) AS Revenue ,       OrderDate , ProductID , COUNT(*) AS COUNT    FROM Sales .SalesOrderDetail AS od ,Sales .SalesOrderHeader AS o    WHERE od .SalesOrderID = o .SalesOrderID    GROUP BY OrderDate , ProductID ; GO --뷰에 고유한 클러스터 인덱스 생성 CREATE UNIQUE CLUSTERED INDEX IDX_V1    ON Sales .vOrders (OrderDate , ProductID ); GO ' 메시지 10136, 수준16, 상태1, 줄1 뷰 "AdventureWorks.Sales.vOrders"이 (가) 집계 COUNT 를사용하므로이뷰에인덱스을 (를) 만들수없습니다. 대신 COUNT_BIG 을사용하십시오 .' 6. 하나의 테이블에 클러스터 와 힙 두 가지 모두 만들 수 없을까? 그렇다면 두 가지를 모두 가져 서비스에 좋은 영향을 줄 수 있을까? 아시다시피 SQL Server에는 두 가지 데이터 영역이 존재 합니다. 클러스터 인덱스가 있는 테이블은 클러스터 데이터 영역이라고 부르겠으며, 클러스터 인덱스가 존재 하지 않는 테이블은 힙 데이터 영역이라고 부르겠습니다. 클러스터 데이터 영역은 클러스터 키로 논리적으로 정열이 된 데이터를 말하며 힙 영역은 특정 키로 정열 되지 않은 영역으로 말할 수 있습니다. SQL Server에는 어떠한 영역이 유리하다고 보기는 매우 어려우며, 두 개 모두 각각의 환경에서 주는 이점이 있습니다. 클러스터 영역이라면 클러스터 키로 소스데이터가 정열 되었기에 집계 및 정열 등에 좋은 성능을 가질 수 있습니다. 그러나 다른 넌클러스터 인덱스는 소스데이터를 접근 하기 위한 포인터로서 클러스터 키를 가지고 있어, 넌클러스터 영역에서 소스데이터 영역을 접근 하기 위해서는 클러스터 인덱스 루트 레벨부터 데이터를 찾아가야 합니다. 단순히 한 두 개의 행을 = 비교로 가지고 와야 한다면 넌 클러스터 인덱스에서 클러스터 영역으로 데이터를 찾는 방식이 성능상 불리 할 수 도 있습니다. 하지만 이런 점은 SQL Server가 발전되면서 변화된 모습이며 이전의 SQL Server에서는 클러스터 인덱스가 생성된 테이블이라고 하더라도 넌클러스터 인덱스가 소스데이터를 참조하기 위해서 RID를 가지고 있었다고 합니다. 소스데이터의 이동이 발생 하면 RID가 변경 되기에 속하는 넌클러스터 인덱스에 대한 RID값도 변해야 합니다. 페이지 분할 등의 작업이 이루어 진다면 넌클러스터 인덱스의 RID를 변경 하는 작업도 SQLServer에는 많은 영향을 주었을 것 이며, 이러한 문제를 해결 하기 위해 RID대신 클러스터 키 값으로 변경 된 것으로 생각 됩니다. 이러한 변화로 인해 많은 장점을 주고 있지만, 모든 면에서 장점을 주는 것은 아이며, 변화로 인해 클러스터 영역인 경우 넌클러스터에서 접근 시 꼭 클러스터인덱스 루트레벨부터 다시 찾아야 하는 단점이 생겼습니다. 이런 단점을 보완 할 수 있는 방법은 없을까요? “클러스터 영역과 힙 영역을 모두 가지고 있으면 단점이 보완 될 수 있지 않을까?” 라는 생각을 해보았습니다. 물론 데이터의 중복으로 인해 데이터 갱신작업에는 기존보다 오버헤드가 있어 성능에 많은 영향을 줄 수 있습니다. 그러나 특정 서비스인 경우에는 클러스터 영역과 힙 영역을 모두 가짐으로써 좋은 영향을 줄 수 있다고 생각합니다. 하지만 많은 서비스는 이점을 줄 수 없을 것입니다. 이러한 방법이 서비스에 어떠한 영향을 준다 보다는 “이렇게도 가능 하지 않을까” 라는 생각을 해보며 작성 하였습니다. 구현을 위해서 먼저 힙 테이블을 만듭니다. 그리고 힙 테이블에 인덱싱 된 뷰를 생성합니다. 인덱싱 된 뷰를 만들기 위해서는 스키마 바인딩 뷰에 고유한 클러스터 인덱스를 처음에 만들어야 합니다. 그러기에 인덱싱 된 뷰 는 클러스터 인덱스를 가지는 영역이 되며, 소스 테이블에는 힙 상태를 그대로 유지하게 됩니다. 소스테이블에 넌 클러스터 인덱스를 생성 하게 되면 RID를 가지는 넌클러스터 인덱스, 인덱싱 된 뷰에 넌클러스터 인덱스를 만들면 클러스터 키를 포인터로 가지는 넌클러스터 인덱스가 됩니다. 개인적인 생각으로는 특정 환경에서는 충분히 활용 가치가 있을 수 있다고 생각됩니다. 위에서 설명한 하나의 테이블에 두 개의 영역으로 나눈 후 각각의 영역에 동일한 컬럼을 가지고 넌 클러스터 인덱스를 만들어서 테스트를 해보았습니다. 아래는 SQL Server 2005에 있는 샘플데이터베이스인 AdventureWorks에 있는 테이블을 가지고 간단히 구현을 하였습니다. 마지막에 두 가지 쿼리는 몇 개 되지 않는 행을 반환 받는 쿼리입니다. USE AdventureWorks GO -- 샘플 테이블을 생성 SELECT * INTO DBO .SalesOrderDetail FROM Sales .SalesOrderDetail; --스키마 바인딩 뷰를 생성 IF OBJECT_ID ('DBO.VSalesOrderDetail', 'view') IS NOT NULL DROP VIEW DBO .VSalesOrderDetail ; GO CREATE VIEW DBO . VSalesOrderDetail WITH SCHEMABINDING AS SELECT SalesOrderID ,SalesOrderDetailID ,CarrierTrackingNumber ,OrderQty ,ProductID ,SpecialOfferID ,UnitPrice ,UnitPriceDiscount ,LineTotal ,rowguid ,ModifiedDate FROM DBO .SalesOrderDetail --뷰에 고유한 클러스터 인덱스를 생성하여 인덱싱 된 뷰로 생성 Create Unique Clustered Index cl_VSalesOrderDetail on VSalesOrderDetail (SalesOrderDetailID ); --인덱싱 된 뷰, 힙 테이블에 SalesOrderID를 넌 클러스터 인덱스로 만들자 CreateIndex IX_VSalesOrderDetail on VSalesOrderDetail (SalesOrderID ); CreateIndex IX_SalesOrderDetail on SalesOrderDetail (SalesOrderID ); --IO양을 확인하기 위해서. SET STATISTICS IO ON select * from SalesOrderDetail where SalesOrderID = 55277; 테이블 'VSalesOrderDetail'. 검색수1 , 논리적읽기수86 , 물리적읽기수0 , 미리읽기수0 , LOB 논리적읽기수0 , LOB 물리적읽기수0 , LOB 미리읽기수0. select*from SalesOrderDetail with(index(2 ))where SalesOrderID = 55277; 테이블'SalesOrderDetail'. 검색수1 , 논리적읽기수30 , 물리적읽기수0 , 미리읽기수0 , LOB 논리적읽기수0 , LOB 물리적읽기수0 , LOB 미리읽기수0. SET STATISTICS IO OFF 7. 마무리 너무 단편적인 예를 들어 설명한 것은 아닌지 걱정이 앞섭니다. 항상 강조하지만 서비스에 대한 정확한 이해를 바탕으로 한 고려가 필요합니다. 잘못 사용되면 성능적 많은 문제가 발생 할 수 있기 때문입니다. 굳이 제가 하고 싶은 말을 두 가지로 정리해 본다면 아래와 같습니다. “다른 것처럼 보이는 기능이지만 비슷한 기능으로 구현 할 수 없을까?” ”인덱스에 대해 조금 더 생각을 해보자!” 이전 아티클에서 다음 아티클은 괜찮은 아티클로 뵙겠다고 하였는데, 이번에도 그 약속을 지키지 못한 것 같습니다. 다음에는 그 약속을 꼭 지키도록 하겠습니다.^^;; 아참!! 그리고 마지막으로 질문 하나를 하겠습니다. “인덱싱 된 뷰를 이용하여 넌클러스터 인덱스 기능을 대처 할 수 있지 않을까요?” 감사합니다. 혹시 이 글에 대한 궁금증 및 잘못된 내용이 있다면 위에 있는 제 메일로 보내주시길 바랍니다.