본문 바로가기
DataBase/MS-SQL

VIEW 인덱스 생성시 스키마바인딩

by Dev. Jkun 2012. 10. 31.
반응형

출처 : http://www.sqler.com/bSQLQA/bColumn/105239


안녕하세요. 비전팀 1기 강동운입니다.


이번에는 뷰를 생성할 때 WITH SCHEMABINDING 옵션에 대해서 얘기를 꺼내볼까 합니다.


BOL에 찾아보면...


SCHEMABINDING

기본 테이블의 스키마에 뷰를 바인딩합니다. SCHEMABINDING을 지정하면 뷰 정의에 영향을 미치는 방법으로 기본 테이블을 수정할 수 없습니다. 뷰 정의 자체를 먼저 수정하거나 삭제하여 수정할 테이블에 대해 종속성을 제거해야 합니다. SCHEMABINDING을 사용하는 경우 select_statement에 참조되는 테이블, 뷰 또는 사용자 정의 함수의 두 부분으로 구성된 이름(schema.object)이 있어야 합니다. 참조된 개체는 모두 같은 데이터베이스에 있어야 합니다.

SCHEMABINDING 절로 만든 뷰에서 사용하는 뷰 또는 테이블은 뷰가 삭제되거나 변경되어 스키마 바인딩이 더 이상 존재하지 않는 경우에만 삭제할 수 있습니다. 그렇지 않으면 데이터베이스 엔진에서 오류가 발생합니다. 또한 ALTER TABLE 문이 뷰 정의에 영향을 미치는 경우에는 스키마 바인딩이 있는 뷰에서 사용하는 테이블에서 이러한 문을 실행할 수 없습니다.

뷰에 별칭 데이터 형식 열이 있는 경우 SCHEMABINDING을 지정할 수 없습니다.



이 글만 읽게 되면... "아... 스키마 바인딩은.. 원래 테이블을 수정할 수 없게 하기 위해서 만드는 거구나" 라는 것만 생각 하게 됩니다.


그래서.. 뷰의 스키마 바인딩에 대해서 좀 더 자세히 알아보기 위해.. 이번 아티클을 작성하게 되었습니다.



뷰는 가상의 테이블입니다... 데이터가 존재하지 않는다고 생각합니다.

하지만 스키마바인딩 옵션으로 뷰에도 데이터를 가질 수 있습니다. 

즉.. 테이블과 뷰에 동시에 같은 데이터를 가질 수 있게 설정할 수 있다는 말입니다.


예제를 통해 보도록 하겠습니다.


일단 스키마 바인딩 되지 않는 뷰!


SET NOCOUNT ON

GO

IF OBJECT_ID('T1','U') IS NOT NULL

       DROP TABLE t1

GO

IF OBJECT_ID('VI_T1','V') IS NOT NULL

       DROP VIEW VI_T1

GO

CREATE TABLE t1

(

       idx                              int    identity

,      ClusteredValue             int    not null

,      ViewClusteredValue  int not null

)

GO

 

--//데이터1000건삽입

INSERT INTO t1(ClusteredValue, ViewClusteredValue)

SELECT CONVERT(INT,RAND()*10000), CONVERT(INT,RAND()*10000)

GO 1000

 

--//ClusteredValue에Clustered Index 생성

CREATE CLUSTERED INDEX CL_T1_ClusteredValue ON T1(ClusteredValue)

GO

 

CREATE VIEW VI_T1

AS

       SELECT * FROM t1

GO

SET NOCOUNT OFF

GO

 

SELECT * FROM t1

GO

1.png 

SELECT * FROM VI_T1

GO

2.png 

SELECT * FROM t1 WHERE ClusteredValue BETWEEN 100 AND 200

GO

3.png 

 

SELECT * FROM VI_T1 WHERE ClusteredValue BETWEEN 100 AND 200

GO

4.png 


4가지 쿼리에서 알 수 있듯이.. view를 조회하든.. table을 조회하든.. 실제 테이블은 t1 테이블을 참조해서 실행 계획을 만듭니다.


즉 .. 이 말은 스키마 바인딩이 안된 view는.. view에 데이터를 저장하는 것이 아니라.. 실제 테이블과 연결되는 하나의 인터페이스가 됩니다.


DBCC IND 명령어로.. 실제 테이블과 뷰에 데이터 페이지가 할당 되었는지 보도록 하겠습니다.


DBCC IND('tempdb','t1',0)

5.png

DBCC IND('tempdb','VI_T1',0)

Msg 5239, Level 16, State 1, Line 1

개체 ID 1755153298(개체 'VI_T1')을(를) 처리할 수 없습니다. 이 DBCC 명령이 이 유형의 개체를 지원하지 않습니다.


역시.. VIEW에는 데이터 페이지가 할당되지 않았습니다.

이제 스키마 바인딩으로 뷰를 생성해보도록 하겠습니다.

CREATE VIEW VI_SCHEMABINDING_T1

WITH SCHEMABINDING

AS

       SELECT * FROM t1

GO

Msg 1054, Level 15, State 6, Procedure VI_SCHEMABINDING_T1, Line 4

스키마 바운드 개체에는 구문 '*'을(를) 사용할 수 없습니다.


스키마 바인딩 된 뷰는 반드시 컬럼명을 명시해야 생성이 가능합니다.


CREATE VIEW VI_SCHEMABINDING_T1

WITH SCHEMABINDING

AS

       SELECT idx, ClusteredValue, ViewClusteredValue FROM t1

GO

Msg 4512, Level 16, State 3, Procedure VI_SCHEMABINDING_T1, Line 4

이름 't1'이(가) 스키마 바인딩에 적합하지 않으므로 뷰 'VI_SCHEMABINDING_T1'을(를) 스키마 바인딩할 수 없습니다. 이름은 두 부분으로 구성되어야 하며 개체는 자체 참조할 수 없습니다.


스키마바인딩 된 뷰를 사용할 때는.. 반드시 테이블에 소유자를 넣도록 해야되는군요 ^^

CREATE VIEW VI_SCHEMABINDING_T1

WITH SCHEMABINDING

AS

       SELECT idx, ClusteredValue, ViewClusteredValue FROM dbo.t1

GO


이제 제대로 생성이 되었습니다. 스키마가 바인딩이 되었으므로, 한번 t1의 idx라는 컬럼을 삭제해보도록 하겠습니다.

(스키마 바인딩이 되었으므로 삭제가 안되는 것이 맞습니다!)


ALTER TABLE t1

DROP COLUMN idx

Msg 5074, Level 16, State 1, Line 1

개체 'VI_SCHEMABINDING_T1'은(는) 열 'idx'에 종속되어 있습니다.

Msg 4922, Level 16, State 9, Line 1

하나 이상의 개체가 이 열에 액세스하므로 ALTER TABLE DROP COLUMN idx이(가) 실패했습니다.


역시.. 스키마 바인딩이 되어있으모로 예상이 맞았습니다.

이제 아까 제가 말씀드린 것 처럼.. 스키마 바인딩이 되었으므로 DBCC IND를 통해 뷰에도 데이터가 있는지 조사해보겠습니다.



DBCC IND('tempdb','t1',0)

5.png

DBCC IND('tempdb','VI_SCHEMABINDING_T1',0)

Msg 5239, Level 16, State 1, Line 1

개체 ID 1915153868(개체 'VI_SCHEMABINDING_T1')을(를) 처리할 수 없습니다. 이 DBCC 명령이 이 유형의 개체를 지원하지 않습니다.


헉?? 왜 데이터가 존재하지 않을까요??.. 아까 날렸던 SELECT 문장을 다시 수행해보도록 하겠습니다.


SELECT * FROM VI_SCHEMABINDING_T1

GO

6.png 

SELECT * FROM VI_SCHEMABINDING_T1 WHERE ClusteredValue BETWEEN 100 AND 200

GO

7.png 


스키마 바인딩이 되었음에도 불구하고.. 아까랑 별반 다를게 없습니다. 왜 이런것 일까요??

그럼 단지 스키마 바인딩은 컬럼을 삭제 못하게끔 하기 위해서 사용하는 것일까요?


혹시 공부하시다가.. 인덱싱된 뷰라고 들어보셨나요?? 바로... 스키마 바인딩된 뷰에는.. 인덱스를 만들 수 있습니다.

이제 뷰에 인덱스를 만들어 보도록 하겠습니다.

참고로.. 스키마 바인딩 된 뷰에는.. 유니크 클러스터드 인덱스를 먼저 만들어야.. 넌 클러스터드 인덱스를 만들 수 있습니다.


CREATE CLUSTERED INDEX CL_VI_SCHEMABINDING_T1_ViewClusteredValue ON VI_SCHEMABINDING_T1(ViewClusteredValue)

GO

Msg 1941, Level 16, State 1, Line 1

고유 클러스터형 인덱스 하나만 허용되므로 뷰 'VI_SCHEMABINDING_T1'에 비고유 클러스터형 인덱스를 만들 수 없습니다. 대신 고유 클러스터형 인덱스를 만드십시오.


유니크 클러스터형 인덱스를 만들라고 합니다.

그럼 그냥 넌클러스터드 인덱스를 만들면??


CREATE INDEX NC_VI_SCHEMABINDING_T1_ViewClusteredValue ON VI_SCHEMABINDING_T1(ViewClusteredValue)

GO


Msg 1940, Level 16, State 1, Line 1

뷰 'VI_SCHEMABINDING_T1'에 인덱스을(를) 만들 수 없습니다. 고유 클러스터형 인덱스가 없습니다.



따라서 유니크한 idx 컬럼에 만들어보도록 하겠습니다.



CREATE UNIQUE CLUSTERED INDEX NC_VI_SCHEMABINDING_T1_idx ON VI_SCHEMABINDING_T1(idx)

GO


이제 만들어진 인덱스에 page가 할당되었는지 확인해보겠습니다.

DBCC IND('tempdb','VI_SCHEMABINDING_T1',1)

8.png


뷰에 있는 인덱스에 페이지가 할당되었습니다!!


그렇다면... 아래 쿼리는 view에 생성한 Clustered Index를 탈까요?

SELECT IDX FROM VI_SCHEMABINDING_T1 WHERE idx between 100 and 200


9.png 

헉!! idx에 인덱스를 만들었는데도 불구하고.. 뷰가 아닌 t1 테이블에 Clustered Index scan(=full scan)을 합니다.

뷰에 있는 Clustered Index를 전혀 이용하지 못했다는 말입니다.


그렇다면.. 뷰에 있는 클러스터드 인덱스를 사용하려면 어떻게 해야 할까요?

바로 NOEXPAND 옵션입니다.



SELECT IDX FROM VI_SCHEMABINDING_T1 WITH(NOEXPAND) WHERE idx between 100 and 200

10.png  



이제 최종 뷰에 있는 Clustered Index Seek 한 것을 확인할 수 있었습니다. ^^


그렇다면.. t1 테이블에 데이터를 넣는다면??? 당근.. view에도 데이터가 들어가야하기 때문에.. I/O가 증가할 것입니다.


수정이 되는 경우도 마찬가지일테고요. 테스트는??? 한번 직접 해보십시요~~ㅎㅎㅎ



정리를 드리면..

WITH SCHEMABINDING 옵션은...

1. 반드시 컬럼명을 기술 해야 만들 수 있다.

2. 생성 된 뷰에 대한 컬럼은 삭제하거나 수정할 수 없다.

3. 테이블  명은 반드시 소유자 명을 지칭해야된다.

4. 인덱스를 만들면 page를 할당해서 데이터를 저장한다.

5. 인덱스가 없다면, 원래 뷰와 동일하게 작동한다.



인덱싱 뷰는..

1. WITH SCHEMABINDING 옵션으로 생성 된 VIEW에만 인덱스를 만들 수 있다.

1. 반드시 첫번째는 유니크한 클러스터드 인덱스를 만들어야 한다.

2. 뷰의 인덱스를 이용하기 위해서는 WITH(NOEXPAND)를 사용해야 한다.


참고로 NOEXPAND는 엔터프라이즈급에서는 인식한다고 합니다만... 힌트를 박아서 사용해야 확실하다고 합니다. ^^ (강산아님 댓글)


NOEXPAND 관련 민석님의 글: http://www.sqler.com/105239


감사합니다.

반응형

댓글