본문 바로가기

카테고리 없음

view에 대하여

VIEW

 

Gravity DBA 이승연

 

(View)? 일종의 가상테이블이라고 생각하면 된다. 개발자 또는 DBA들은 이를 테이블처럼 사용할 수가 있다. View는 테이블과 동일하게 보이지만, 테이블과 같이 실제 Data를 가지고 있지 않고, 테이블에 링크(Link)된 개념이라고 생각하면 된다. 그래서 View Select를 하게 되면 실제 테이블을 조회하는 것과 동일한 결과가 되는 것이다.

 

하지만, 어떤 View들은 Update()도 할 수가 있고, View들을 중첩을 시킬 수 있으며, 테이블과 같이 실제 Data를 소유할 수도 있다.

 

먼저 View에는 어떤 유형의 View들이 존재하는지 알아보자.

 

l  표준 View

l  분활 View

l  인덱스 View

l  시스템 View

 

표준 View

우리가 흔히 알고 쓰고 있는 View는 거의 모두 표준 View에 속한다. 표준 View는 한 개 또는 한 개 이상의 테이블을 이용하여 만든 View를 표준 View라 지칭한다.

 

분활 View

분활 View(partitioned view)? 한대 또는 한대 이상의 서버에 있는 테이블을 결합하여 하나의 테이블처럼 보이도록 만든 View를 분활 View라 지칭한다. 분활 View는 로컬 분활 View(local partitioned view)와 분산형 분활 View(distributed partitioned view)가 있는데, 여러 개의 테이블을 결합하여 하나의 테이블처럼 보이도록 하는 것은 도일하지만 가장 큰 차이점이 로컬 분활 View는 여러 개의 테이블이 한대의 서버 안에 존재하는 것이고, 분산형 분활 View는 물리적으로 다른 서버의 테이블을 말하는 것이다.

 

인덱스 View

우리는 뷰를 가상테이블이라고 부르고 있다. 가상테이블인가? 그 이유는 테이블처럼 동작을 하지만 정작 테이블과 같이 데이터는 가지고 있지 않기 때문에 뷰를 가상테이블이라고 부르고 있는 것이다. 하지만, 위에서 살짝 언급했듯이 뷰에도 data를 가실 수 있는 뷰가 존재하니 그것은 바로 인덱스 뷰(Indexed View)인 것이다.

 

시스템 View

SQL Server SQL Server 상태 및 내부의 정보를 관리하기 위해 시스템 테이블을 가지고 있다. 하지만, 일반 사용자인 우리는 그 시스템테이블에 직접 접근을 하지 못하도록 되여 있다. 접근 하지 못하는 대신 우리에게 시스템 View를 제공을 해주고 있다. 시스템 뷰에는 카탈로그 뷰, 정보 스티마 뷰, 호환성 뷰, 복제 뷰, 동적 관리 뷰 등이 존재 하고 있다.

 

 

우리는 지금까지 뷰가 무엇인지 또 뷰의 종류는 어떤 것들이 있는지에 대하여 간략하게 알아보았다. 그럼 우리는 왜 뷰를 사용해야 하는지 사용하면 무엇이 좋은지 알아보자.

 

l  뷰의 장점
접근 제어를 통한 보안이 제공된다.
복잡한 쿼리를 단순화 시킬 수 있다
.
어플리케이션과 테이블 간의 독립성을 제공해 준다.

 

접근 제어를 통한 보안이 제공된다.” [A]Table G회사의 회원정보 테이블이라고 가정을 하자. [A]Table column에는 [회원이름], [회원주소], [회원연락처], [회원주민등록번호], [회원나이] 등 회원에 대한 기본적인 개인정보가 기록되어 있다고 생각하자. 어플리케이션은 이 테이블에 접근하여 [회원이름], [회원나이]의 정보만을 필요로 하고 있다. 우리는 그 어플리케이션에서 해당 테이블을 검색할 수 있도록 테이블에 Select 권한을 줄 수도 있고, View 만들어서 제공해 줄 수 도 있다. 테이블에 Select 권한을 주었다고 생각해 보자. 어플리케이션에서는 [회원이름], [회원나이]만을 필요로 하고 있는데 그 회원에 대한 모든 정보가 제공 되고 있다. 그래서 우리는 View를 만들어서 제공을 했다.

 

CREATE VIEW v_a

AS

SELECT [회원이름],[회원나이] FROM [A]

 

어플리케이션에서는 [회원이름],[회원나이] 이외의 다른 정보는 알 수가 없다. 심지어 어떤 테이블에서 해당 정보를 가지고 오는지도 모른다. 이처럼 View를 만들어서 제공을 하면 정보에 대하여 근본적으로 차단을 할 수 있어서 보안에 도움이 되는 것이다.

 

복잡한 쿼리를 단순화 시킬 수 있다.“ 쿼리를 작성하다 보면 우리가 예상치 못하게 쿼리가 복잡해 지는 경우가 발생을 하고, 그 쿼리를 자주 사용 해야 한다고 하면, 이를 뷰로 생성해 놓고 사용하면 간단하게 해결이 된다.

 

CREATE VIEW [물품구입회원]

AS

SELECT [회원이름],[회원나이]

FROM [회원정보] INNER JOIN [구매]

ON [회원정보].[회원순번] = [구매].[회원순번]

 

SELECT * FROM [물품구입회원] WHERE [회원이름] = '홍길동'

 

위와 같이, [물품구입회원]이라는 View를 생성하고 어떤 회원이 물품을 구입하였나? 검색을 하고 싶으면 View만 참조를 하면 된다. 만약 View가 없다면 해당 쿼리를 실행시킬 때마다 조인문을 적어야 할 것이다.

 

뷰의 장점 마지막으로, 어플리케이션과 테이블 간의 독립성을 제공해 준다.” 어플리케이션과 테이블간의 독립성이란 무엇인가? ‘[A]Table이 있었다. 어플리케이션은 [A]Table * 로 검색을 하고 있었다. 어느 날 [A]Table column이 하나가 추가가 되었다. 어플리케이션은 그 추가된 column이 필요하지 않아 검색을 계속 * 로 하였다. 하지만, 어플리케이션은 ERROR가 발생하였다.’ 이 시나리오에서 우리가 [A]Table View로 만들어서 어플리케이션에서 필요한 column만 지정하여 제공하였다면 어플리케이션은 ERROR가 발생하지 않았을 것이다. , View로 생성을 하면 테이블의 스키마가 변경이 되어도 어플리케이션에 영향을 미치지 않게 할 수가 있다. 이것이 독립성이다.

 

우리는 지금까지 view가 무엇인지? view에는 어떤 view들이 존재하는지? view를 사용하게 되면 어떤 점이 좋은지에 대하여 알아보았다. 이제 view를 생성하는 방법만 알면 되지만, view를 생성하기 전에 몇 가지 사항을 고려를 해야 한다.

 

l  현재 데이터베이스에서만 view를 만들 수 있습니다.

l  View이름과 해당 스키마에 포함된 테이블의 이름이 같을 수 없습니다.

l  View는 중첩할 수 있습니다. 중첩은 32 수준을 넘을 수 없습니다.

l  AFTER 트리거를 view에 사용할 수 없습니다. INSTEAD OF 트리거만 사용할 수 있습니다.

l  View를 정의하는 Select절에 into, compute, compute by 문을 사용할 수 없습니다.

l  View를 정의하는 Select절에 order by문을 사용할 수 없습니다. TOP절이 있으면 사용할 수 있습니다.

l  View를 정의하는 Select절에 쿼리 힌트를 지정하는 option절을 포함 시킬 수 없습니다.

l  임시 view를 만들 수 없으며 임시 테이블에 대해서 view을 만들 수 없습니다.

 

앞으로 우리가 view를 생성할 때는 위와 같은 고려사항을 생각하면서 생성해야 할 것이다.

 

VIEW 생성

CREATE VIEW [view_name]

[with encryption]

AS

SELECT_statement

[with check option]

 

VIEW 수정

ALTER VIEW [view_name]

[with encryption]

AS

SELECT_statement

[with check option]

 

VIEW 삭제

DROP VIEW [view_name]

 

우리는 Stored Procedure의 내부정의를 감추려고 WITH ENCRYPTION을 사용하여 암호화를 하였었다. VIEW도 마찬가지로 내부정의에 대하여 감추고 싶을 때는 WITH ENCRYTION을 사용하면 된다.

 

WITH ENCRYPTION

위에서 언급했듯이 VIEW의 내부정의를 감추기 위해서는 WITH ENCRYPTION을 사용하면 된다. 왜 이와 같은 옵션을 사용하여 내부정의를 암호화 하려 하는 것일까? VIEW의 오브젝트가 되는 테이블의 내부 구조를 보안하기 위해서 VIEW을 사용하고자 했으면, VIEW또한 암호화를 하여 보안을 해야 할 것이다. 한번 암호화된 VIEW는 그 내부구조를 다시 볼 수 없으므로 반드시 원본을 백업을 하여 관리해야 하는 것을 잊지 말자. 예제를 통하여 확인해보자.

 

-- 뷰생성

USE master

GO

CREATE VIEW view_table

AS

        SELECT 'test' AS test

 

-- 뷰내용보기

sp_helptext view_table

 

위 예제를 실행을 통하여 보면 정상적으로 VIEW의 내부 정의가 출력되는 것을 보았을 것이다. 이번에는 WITH ENCRYPTION을 통하여 암호화를 하고 sp_helptext을 실행하여 보자.

 

-- 기존에만들었던뷰삭제

DROP VIEW view_table

GO

 

-- 뷰생성

CREATE VIEW view_table

WITH ENCRYPTION

AS

        SELECT 'test' AS test

-- 뷰내용보기

sp_helptext view_table

 

VIEW을 통한 데이터 수정과 WITH CHECK OPTION

처음 VIEW에 대하여 설명을 할 때 VIEW을 통해 데이터수정이 가능하다고 하였다. VIEW을 통해 어떻게 데이터가 수정이 될까? VIEW는 단순히 SQL질의로 이루어진 테이블의 형태를 띄 가상테이블일 뿐이다. 여기서 사용자가 VIEW을 통하여 데이터 수정을 요청하면 SQL Server VIEW에 있는 데이터들을 수정하는 것이 아니라 VIEW의 오브젝트 베이스가 되는 실제 테이블에서 데이터가 처리되는 것이기 때문에 VIEW를 통하여 데이터가 수정이 되는 것이다.

 

VIEW을 통하여 데이터 수정을 할 때 몇 가지 주의 할 점이 있다.

 

l  Default 값이 정의되어 있거나 NULL 값이 허용되어 있어야 한다.

l  Identity 이거나 timestamp 속성이어야 한다.

l  집계 함수들 TOP, GROUP BY, UNION, DISTINCT 등은 허용되지 않는다.

l  VIEW을 통하여 파생된 컬럼 (유도 컬럼)에 대해서는 데이터수정 작업을 수행할 수 없다.

l  VIEW을 통하여 하나 이상의 테이블은 수정 작업을 수행할 수 없다.

“Default 값이 정의되어 있거나 NULL 값이 허용되어 있어야 한다.”, “Identity 이거나 timestamp 속성이어야 한다.” VIEW는 테이블의 일부 column들만을 모아서 생성되는 경우가 있다. 물론, 모든 column을 사용해서 VIEW을 생성하였다면, 해당 문제는 발생하지 않을 것이다. 만약, 일부 column만을 사용하여 VIEW을 생성하였다면, ERROR가 발생을 할 것이다. 예제를 통하여 살펴 보자.

 

CREATE TABLE view_test (

                num     int NOT NULL

        ,       text    varchar(10)

)

GO

 

CREATE VIEW view_test_1

AS

        SELECT text FROM view_test

 

INSERT INTO view_test_1 VALUES ('이승연')

 

메시지515, 수준16, 상태2, 1

테이블'master.dbo.view_test', 'num'NULL 값을삽입할수없습니다. 열에는NULL을사용할수없습니다. INSERT() 실패했습니다.

문이종료되었습니다.

 

이와 같이 INSERT num값에 대하여는 어디에서도 언급이 없기 때문에 ERROR가 발생하게 된다. 그럼 위에서 어떻게 바꾸어 주면 ERROR을 해결할 수 있을까?

 

CREATE TABLE view_test (

                num     int IDENTITY -- 또는num int NULL

        ,       text    varchar(10)

)

GO

 

CREATE VIEW view_test_1

AS

        SELECT text FROM view_test

 

INSERT INTO view_test_1 VALUES ('이승연')

 

 

이와 같이 VIEW 정의에서 언급되지 않은 column에 대하여 default값이나 NULL허용을 해주면 ERROR는 더 이상 발생하지 않는다.

 

WITH CHECK OPTION

WITH CHECK OPTION은 일정한 조건에 의하여 만들어진 VIEW에서 데이터 입력, 수정이 있을 경우, VIEW을 생성할 때와 동일한 조건으로 입력, 수정을 해야만 정상적인 수행을 하도록 하게 해주는 OPTION이다. 예제를 통하여 살펴보자.

 

CREATE TABLE view_test (

               num int identity

        ,       address1 varchar(10)

        ,       name varchar(10)

)

GO

 

CREATE VIEW view_test_1

AS

        SELECT address1, name FROM view_test

        WHERE address1 = '서울'

GO

       

INSERT INTO view_test_1 VALUES ('김해','김경진')

INSERT INTO view_test_1 VALUES ('서울','이승연')

 

위와 같이, INSERT을 하면 정상적으로 실행이 된다. 그리고 view_test_1 select하게 되면 김해,김경진 이라는 row는 검색이 되지 않는다. 일정한 조건만을 만족하는 VIEW인데, 조건을 만족하지 않은 데이터가 INSERT가 정상적으로 되었다. 조금 이상하지 않은가? 이럴 때 WITH CHECK OPTION을 사용하면 문제 해결이 된다. 예제를 살펴보자.

 

CREATE TABLE view_test (

               num int identity

        ,       address1 varchar(10)

        ,       name varchar(10)

)

GO

 

CREATE VIEW view_test_1

AS

        SELECT address1, name FROM view_test

        WHERE address1 = '서울'

        WITH CHECK OPTION

GO

       

INSERT INTO view_test_1 VALUES ('김해','김경진')

INSERT INTO view_test_1 VALUES ('서울','이승연')

 

메시지550, 수준16, 상태1, 1

대상뷰가WITH CHECK OPTION을지정하거나WITH CHECK OPTION을지정하는뷰에걸쳐있고해당연산의하나이상의결과행이CHECK OPTION 제약조건을충족하지않았으므로삽입또는업데이트시도가실패했습니다.

문이종료되었습니다.

 

 

위와 같이 입력, 수정을 할 때에도 VIEW 생성조건을 비교하여 조건에 만족해야만 입력, 수정이 되도록 하고 있다.

 

우리는 지금까지 기존적인 VIEW생성 방법과 VIEW을 생성할 때 주의점, VIEW을 통해 수정작업이나 입력작업을 할 때 주의점을 살펴보았다. 이제 인덱스VIEW에 대하여 알아보자.

 

INDEX VIEW

우리는 앞서 VIEW는 실제 데이터를 포함하지 않은 가상테이블이라고 배웠다. 하지만, 예외가 있으니! 그것이 바로 INDEX VIEW인 것이다. 여러 개의 테이블을 연결하여 복잡하게 생성된 VIEW는 아마도 느리게 수행이 될 것이다. 데이터가 많이 있거나 실행계획이 복잡해도 느릴 것이다. 대량의 데이터를 집계연산을 하여도 느리게 수행이 된다. 이런 작업들을 조금 더 빠르게 수행하기 위해서 VIEW INDEX을 걸어버리는 것이다. 예제를 살펴보자.

 

use AdventureWorks

GO

-- 표준VIEW

CREATE VIEW V_IndexVIEW_1

AS

        SELECT  postalcode

        ,       COUNT(*) as users

        FROM person.address

        group by postalcode

GO

--INDEX VIEW

CREATE VIEW V_IndexVIEW_2

WITH SCHEMABINDING

AS

        SELECT  postalcode

        ,       COUNT_BIG(*) as users

        FROM person.address

        group by postalcode

GO

-- VIEWINDEX 생성

CREATE UNIQUE CLUSTERED INDEX CX_V_indexIVEW_2 ON V_IndexVIEW_2(postalcode)

GO

 

SELECT * FROM V_IndexVIEW_1

SELECT * FROM V_IndexVIEW_2

 

위 예제를 보면 V_IndexVIEW_1는 실행할 때 마다 person.address테이블을 집계해서 데이터를 가지고 오지만, V_IndexVIEW_2 person.address의 집계된 데이터를 VIEW에서 직접 소유하고 있기 때문에 V_IndexVIEW_1보다 V_IndexVIEW_2가 빠르다.

 

INDEX VIEW는 필요한 데이터를 실제로 소유하고 있기 때문에 표준VIEW보다는 빠른 성능을 자랑하지만, 표준VIEW에서는 필요가 없었던 디스크공간을 필요로 하고, 또한, 데이터가 수정이 될 경우 INDEX VIEW도 수정이 되어야 하기 때문에, 데이터 변경이 잦을 경우 시스템 성능에 나쁜 영향을 줄 수 있으니, INDEX VIEW에 대하여 정확히 숙지 후 사용해야 할 것이다.

 

INDEX VIEW CREATE INDEX을 할 때 CREATE INDEX문에 필요한 요구 사항이 존재를 한다.

 

l  CREATE INDEX 문을 실행하는 사용자는 VIEW의 소유자여야 한다.

l  CREAET INDEX 문이 실행될 때 다음 SET 옵션은 ON으로 설정되어야 한다.
ANSI_NULLS
ANSI_PADDING
ANSI_WARNINGS
CONCAT_NULL_YIELDS_NULL
QUOTED_IDENTIFIER

l  NUMERIC_ROUNDABORT 옵션은 OFF로 설정되어야 한다.

l  데이터베이스가 80 호환성 모드 이전에서 실행되는 경우 ARITHABORT 옵션을 ON으로 설정해야 한다.

 

위와 같은 요구사항을 충족해야 CREATE INDEX문이 실행이 된다.

참고로 위의 SET 옵션에 대하여 간단하게 설명을 하자.


----------------------------------------------------------
DBA 이승연
GRAVITY co., Ltd 
Global Business Dept / Tech Support

http://www.gravity.co.kr
http://www.ddoung2.com
----------------------------------------------------------