인덱싱된 뷰 생성

인덱싱된 뷰 생성을 위해 거쳐야 하는 단계들은 성공적인 뷰를 구현하는데 있어서도 매우 중요합니다.

1. 뷰에서 참조하게 될 기존의 모든 테이블에서 ANSI_NULLS가 올바르게 설정되었는지 확인합니다.
2. 새로운 테이블을 생성하기 앞서 아래 표에서와 같이 현재 세션에 대해 ANSI_NULLS가 올바르게 설정되었는지 확인합니다.
3. 뷰를 생성하기 앞서 아래 표에서와 같이 현재 세션에 대해 ANSI_NULLS 및 QUOTED_IDENTIFIER가 올바르게 설정되었는지 확인합니다.
4. 뷰 정의가 명확한지 확인합니다.
5. WITH SCHEMABINDING 옵션을 사용해 뷰를 생성합니다.
6. 뷰에서 클러스터된 고유 인덱스를 생성하기 앞서 아래 표에서와 같이 세션의 SET 옵션이 올바르게 설정되었는지 확인합니다.
7. 뷰에서 클러스터된 고유 인덱스를 생성합니다.
8. 기존 테이블이나 뷰에서 OBJECTPROPERTY 함수를 사용해 ANSI_NULLS 및 QUOTED_IDENTIFIER 값을 확인할 수 있습니다.

일관된 결과를 얻기 위한 SET 옵션 사용

쿼리가 실행될 때 현재 세션에서 다양한 SET 옵션이 지원될 경우 동일한 표현식을 평가해도 SQL Server 2005에서 다른 결과값이 나올 수 있습니다. 예를 들어, SET 옵션인 CONCAT_NULL_YIELDS_NULL이 ON으로 설정된 이후에는 표현식‘abc’+ NULL은 NULL 값을 반환합니다. 하지만, CONCAT_NULL_YIEDS_NULL이 OFF로 설정된 후에는 동일한 표현식으로‘abc’가 생성됩니다. 인덱싱된 뷰는 뷰가 올바르게 유지 관리되고 일관된 결과를 반환할 수 있도록 현재 세션에 대한 여러 SET 옵션과 뷰가 참조한 객체에 있어 고정 값을 요구합니다. 현재 세션의 SET 옵션인 ANSI_NULLS과 QUOTED_IDENTIFIER 은 인덱스를 만드려는 뷰가 생성될 때 모두 ON으로 설정되어야 합니다. 그 이유는 이들 두 옵션이 시스템 카탈로그에서 뷰 정의를 통해 저장되기 때문입니다.

다음과 같은 경우가 발생할 때마다 현재 세션의 SET 옵션을 현재 세션의 Required Value 열에 표시된 값으로 설정해야 합니다.

• 뷰에서 인덱스가 생성
• 인덱싱된 뷰에 참여하고 있는 모든 테이블에서 INSERT, UPDATE 또는 DELETE 작업이 수행
• 쿼리 최적화 프로그램이 쿼리 계획을 개발하기 위해 인덱싱된 뷰 사용

인덱싱된 뷰를 생성하기 위해서는있어야 합니다. 하지만, SQL Server 2005의 경우 일단 ANSI_WARNINGS가 ON으로 설정되면 ARITHABORT 옵션도 암묵적으로 ON으로 설정되기 때문에 별도의 명시적 설정이 필요하지 않습니다. .Net SqlClient, OLE DB 또는 ODBC 서버 연결을 사용하면 인덱싱된 뷰의 생성, 사용 및 유지 관리를 위해 기본 설정값에서 SET 옵션을 수정할 필요가 전혀 없습니다. sp_configure를 사용하는 서버 수준에서나 SET 명령어를 사용하는 응용 프로그램에서 모든 DB LIB 값을 올바르게 설정해야 합니다. SET 옵션에 대한 자세한 내용은 SQL Server Books Online의“SQL Server에서의 옵션 사용”을 참조하십시오.

확정적 함수 사용

인덱싱된 뷰에 대한 정의는 명확해야 합니다. WHERE 및 GROUP BY 구문을 비롯해 select 목록에 있는 모든 표현식이 명확하면 뷰도 명확합 니다. 특정한 입력값 세트를 통해 평가되는 모든 확정적 표현식은 항상 동일한 결과를 반환합니다. 확정적 함수만이 확정적 표현식에 참여하게 됩니다. 예를 들어, DATEADD은 3개의 매개변수에 대해 주어진 모든 인수값 세트에서 동일한 결과를 반환한다는 점에서 확정적 함수라 할 수 있습니다. GETDATE는 동일한 인수를 통해 호출되지만 실행시 마다 반환 값이 달라진다는 점에서 확정적이라ne의“확정적 함수 및 비확정적 함수”를 참조하십시오.

표현식이 확정적임에도 불구하고 float 표현식 아키텍처나 마이크로코드 버전에 의해 결정될 수 있습니다. SQL Server 2005에서는 시스템 간의 데이터베이스 이동 시 데이터 무결성을 보장하기 위해 이러한 표현식을 인덱싱된 뷰의 키 가 아닌 열로서만 참여시키고 있습니다. float 표현식을 포함하지 않은 확정적 표현식을 정확한 표현식이라고 합니다. 지속적인 확정적 표현식이나 정확한 표현식만이 인덱싱된 뷰의 키열 및 WHERE 또는 GROUP BY 구문에 포함될 수 있습니다. 지속적 표현식은 일반 열과 PERSISTED라고 표시된 계산된 열을 포함한 저장Y 함수와 IsDeterministic 속성을 사용하여 뷰 열이 확정적인지 확인하십시오. 또한 COLUMNPROPERTY 함수와 IsPrecise 속성을 사용해 SCHEMABINDING에서 뷰의 확정적 열이 정확한지 확인하십시오. COLUMNPROPERTY는 속성이 TRUE면 1을, FALSE나 유효하지 않은 입력을 뜻하는 NULL이면 0을 반환합니다. 예를 들어 이 스크립트에서

CREATE TABLE T(a int, b real, c as getdate(), d as a+b)

CREATE VIEW VT WITH SCHEMABINDING AS SELECT a, b, c, d FROM dbo.T

SELECT object_id‘( VT’), COLUMNPROPERTY(object_id‘( VT’),’b’,’IsPrecise’)

b 열이 real 유형이기 때문에 SELECT는 IsPrecise에 대해 0을 반환합니 정확한지 확인할 수 있습니다.

추가 요구 사항

인덱싱 가능한 뷰 세트는 가능한 뷰 세트의 하위 집합이라 할 수 있습니다. 인덱싱이 가능한 모든 뷰는 인덱스 사용 여부에 관계없이 존재할 수 있습니다.

뷰에서 클러스터된 고유 인덱스를 생성하기 위해서는 설계 지침의“일관성있는 결과를 얻기 위한 SET 옵션 사용”과“확정적 함수 사용”부분 에서 언급한 요구 사항 외에도 아래와 같은 요구 사항을 충족해야 합니다.

기본 테이블 요구 사항

뷰가 참조하는 기본 테이블은 테이블 생성 시 SET 옵션 ANSI_NULLS를 올바른 값으로 설정해야 합니다. OBJECTPROPERTY 함수를 사용해서 기존 테이블의 ANSI_NULLS 값을 확인할 수 있습니다.

함수 요구 사항

뷰가 참조하는 사용자 정의 함수는 WITH SCHEMABINDING 옵션을 사용하여 생성해야 합니다.

뷰 요구 사항

• WITH SCHEMABINDING 옵션을 사용해 뷰를 생성해야 합니다.
• 테이블은 두 부분으로 된 이름(schemaname.tablename)을 사용하는 뷰에 의해 참조되어야 합니다.
• 사용자 정의 함수는 두 부분으로 된 이름(schemaname.functionname)을 사용하는 뷰에 의해 참조되어야 합니다.
• SET 옵션인 ANSI_NULLS와 QUOTED_IDENTIFIER를 올바른 값으로 설정해야 합니다.

뷰 제약

SQL Server 2005의 뷰에서 인덱스를 생성하려면 뷰 정의에 다음이 포함되지 않아야 합니다:

주 인덱싱된 뷰는 float 및 real 열을 포함할 수 있지만, 지속되지 않는 계산된 열일 경우에는 클러스터된 인덱스 키에 포함될 수 없습니다.

GROUP BY 제약

GROUP BY가 존재할 경우 VIEW 정의에는 다음과 같은 제약이 따릅니다.

• COUNT_BIG(*)를 포함해야 합니다.
• HAVING, CUBE, ROLLUP 또는 GROUPING()을 포함하지 않아야 합니다.

이러한 제약은 인덱싱된 뷰 정의에만 적용됩니다. 쿼리는 이러한 GROUP BY 제약을 충족하지 않은 경우에도 실행 계획에서 인덱싱된 뷰를 사용할 수 있습니다.

인덱스 요구 사항

• CREATE INDEX 명령문을 실행하는 사용자는 뷰 소유자일 가능성이 높습니다.
• 뷰 정의에 GROUP BY 구문이 포함되어 있으면 클러스터된 고유 인덱스 키가 GROUP BY 구문에 지정되어 있는 열만 참조할 수 있습니다.
• IGNORE_DUP_KEY 옵션이 활성화되어 있는 경우 인덱스를 생성하면 안됩니다.

예제

여기나와 있는 예제는 집계와 조인이라는 2개의 주요 쿼리 그룹에서의 인덱싱된 뷰 사용을 설명하기 위한 것입니다. 또한 인덱싱된 뷰의 적용 가능 여부를 판단할 때 쿼리 최적화 프로그램이 사용하는 조건을 보여주고 있습니다. 전체 조건 목록에 관한 내용은“쿼리 최적화 프로그램의 인덱싱된 뷰 사용 방법”을 참조하십시오.

쿼리는 SQL Server 2005에서 제공되는 샘플 데이터베이스인 AdventureWorks의 테이블을 토대로 하며 작성과 함께 실행 가능합니다. SQL Server Management Studio의 Display Estimated Execution Plan 도구를 사용해서 뷰 생성 이전이나 이후에 쿼리 최적화 프로그램이 선택한 계획을 보고자 할 수 있습니다. 여기 나와있는 예제들은 최적화 프로그램이 어떻게 비용이 낮은 실행 계획을 선택하는지 보여주기 위한 것이지만, AdventureWorks 샘플 데이터베이스는 성능 이점을 보여주기에는 너무 작습니다.

예제를 살펴보기 앞서 이러한 명령어를 실행하여 세션에 올바른 옵션이 설정되어 있는지 확인하십시오.

설치

SET ANSI_NULLS ON

SET ANSI_PADDING ON

SET ANSI_WARNINGS ON

SET CONCAT_NULL_YIELDS_NULL ON

SET NUMERIC_ROUNDABORT OFF

SET QUOTED_IDENTIFIER ON

SET ARITHABORT ON

아래 쿼리는 Sales.SalesOrderDetail 테이블에서 총 할인율이 가장 큰 5개 제품을 반환하는 2가지 방법을 보여주고 있습니다.

Query 1

SELECT TOP 5 ProductID, Sum(UnitPrice*OrderQty) -

   Sum(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rebate

FROM Sales.SalesOrderDetail

GROUP BY ProductID

ORDER BY Rebate DESC

Query 2

SELECT TOP 5 ProductID,

SUM(UnitPrice*OrderQty*UnitPriceDiscount) AS Rebate

FROM Sales.SalesOrderDetail

GROUP BY ProductID

ORDER BY Rebate DESC

쿼리 최적화 프로그램이 선택한 실행 계획에는 다음이 포함되어 있습니다.

• 행 수가 121,317개로 예측되는 Sales.SalesOrderDetail 테이블의 클러스터된 인덱스 스캔
• GROUP BY 열에 따라 선택된 행을 hash 테이블로 보내고 각각의 행에 대해 SUM 집계를 계산하는 hash 일치/집계 연산자
• ORDER BY 구문을 토대로 하는 상위 5개의 정렬 연산자

View 1

Rebate 열에 필요한 집계를 포함하는 인덱싱된 뷰를 추가하면 Query 1에 대한 쿼리 실행 계획이 변경됩니다. 대규모 테이블(수백만 개의 행)에서 쿼리 성능은 크게 개선됩니다.

CREATE VIEW Vdiscount1 WITH SCHEMABINDING AS

SELECT SUM(UnitPrice*OrderQty) AS SumPrice,

SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS SumDiscountPrice,

COUNT_BIG(*) AS Count, ProductID

FROM Sales.SalesOrderDetail

GROUP BY ProductID

GO

CREATE UNIQUE CLUSTERED INDEX VDiscountInd ON Vdiscount1 (ProductID)

SQL Server 2005의 인덱싱된 뷰를 통한 성능 향상 15

Query 1에 대한 실행 계획은 최적화 프로그램에서 Vdiscount1 뷰가 사용되고 있다는 것을 보여줍니다. 그러나, SUM(UnitPrice*OrderQty*UnitPriceDiscount) 집계를 포함하고 있지 않다는 점에서 이 뷰는 Query 2에서 사용되지 않을 것입니다. 두 쿼리 모두를 지원하는 또 다른 인덱싱된 뷰를 생성할 수 있습니다.

View 2

CREATE VIEW Vdiscount2 WITH SCHEMABINDING AS

SELECT SUM(UnitPrice*OrderQty) AS SumPrice,

SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS SumDiscountPrice,

SUM(UnitPrice*OrderQty*UnitPriceDiscount)AS SumDiscountPrice2,

COUNT_BIG(*) AS Count, ProductID

FROM Sales.SalesOrderDetail

GROUP BY ProductID

GO

CREATE UNIQUE CLUSTERED INDEX VDiscountInd ON Vdiscount2 (ProductID)

이러한 인덱싱된 뷰를 사용할 경우, Vdiscount1 삭제 이후 두 쿼리에 대한 쿼리 실행 계획에는 다음이 포함됩니다.

• 행 수가 266개로 예측되는 Vdiscount2 뷰의 클러스터된 뷰 스캔
• GORDER BY 구문을 기반으로 하는 상위 5개의 정렬 함수

쿼않는 경우에도 최저의 실행 비용을 제공한다는 점에서 뷰를 선택했습니다.

Query 3

Query 3은 이전 쿼리와 유사하지만 ProductID가 뷰 정의에 포함되지 않는 SalesOrderID 열로 교체되었습니다. 이는 쿼리 계획에서 인덱싱된 뷰를 사용하기 위해서는 뷰 정의 시 테이블에 있는 쿼리 select 목록의 모든 표현식은 선택 뷰 목록에서 추론해야 한다는 조건에 위배되는 것입니다.

SELECT TOP 3 SalesOrderID,

SUM(UnitPrice*OrderQty*UnitPriceDiscount) OrderRebate

FROM Sales.SalesOrderDetail

GROUP BY SalesOrderID

ORDER BY OrderRebate DESC

이러한 쿼리를 지원하기 위해서는 별도의 인덱싱된 뷰가 필요합니다. SalesOrderID를 포함하도록 Vdiscount2를 수정할 수 있었지만, 그 결과 뷰에는 원래 테이블 만큼이나 많은 열이 포함되었고 이로 인해 기본 테이블 사용과 비교해 성능 개선을 기대하기 어려웠습니다.

Query 4

각 제품에 대한 평균 가격을 산출하는 쿼리입니다.

SELECT p.Name, od.ProductID,

AVG(od.UnitPrice*(1.00-od.UnitPriceDiscount)) AS AvgPrice,

SUM(od.OrderQty) AS Units

FROM Sales.SalesOrderDetail AS od, Production.Product AS p

WHERE od.ProductID=p.ProductID

GROUP BY p.Name, od.ProductID

복잡한 집계(예: STDEV, VARIANCE, AVG)는 인덱스 뷰의 정의에 포함될 수 없습니다. 하지만, 결합을 통해 복잡한 집계 작업을 수행하는 단순 집계 함수를 포함시킴으로써 AVG를 포함한 쿼리를 실행하기 위해 인덱싱된 뷰를 사용할 수 있습니다.

View 3

이러한 인덱싱된 뷰에는 AVG 함수 실행에 필요한 단순 집계 함수가 포함됩니다. View 3 생성 후 Query 4가 실행되면 실행 계획은 사용 중인 뷰를 표시합니다. 최적화 프로그램은 뷰의 단순 집계 열인 Price와 Count에서 AVG 표현식을 추론할 수 있습니다.

CREATE VIEW View3 WITH SCHEMABINDING AS

SELECT ProductID, SUM(UnitPrice*(1.00-UnitPriceDiscount)) AS Price,

COUNT_BIG(*) AS Count, SUM(OrderQty) AS Units

FROM Sales.SalesOrderDetail

GROUP BY ProductID

GO

CREATE UNIQUE CLUSTERED INDEX iv3 ON View3 (ProductID)

Query 5

이 쿼리는 1가지 검색 조건을 추가로 포함하고 있다는 점을 제외하고는 Query 4와 동일합니다. 추가 검색 조건이 뷰 정의에 포함되지 않은 테이블의 열만 참조함에도 불구하고 View 3은 이 쿼리를 위해 작동하게 됩니다.

SELECT p.Name, od.ProductID,

AVG(od.UnitPrice*(1.00-UnitPriceDiscount)) AS AvgPrice,

SUM(od.OrderQty) AS Units

FROM Sales.SalesOrderDetail AS od, Production.Product AS p

WHERE od.ProductID=p.ProductID AND p.Name like‘ %Red%’

GROUP BY p.Name, od.ProductID

Query 6

쿼리 최적화 프로그램은 이 쿼리에서 View 3을 사용할 수 없습니다. 추가된 검색 조건 od.UnitPrice>10에는 뷰 정의 시 테이블의 열이 포함되지만, 이 열은 GROUP BY 목록에 표시되지 않고 검색 조건자도 뷰 정의에 표시되지 않습니다.

SELECT p.Name, od.ProductID,

AVG(od.UnitPrice*(1.00-UnitPriceDiscount)) AS AvgPrice,

SUM(od.OrderQty) AS Units

FROM Sales.SalesOrderDetail AS od, Production.Product AS p

WHERE od.ProductID = p.ProductID AND od.UnitPrice > 10

GROUP BY p.Name, od.ProductID

Query 7

반대로, 새로운 검색 조건인 od.ProductID between 0 and 995에 정의된 열이 뷰 정의의 GROUP BY 구문에 포함되기 때문에 쿼리 최적화 프로그램은 Query 7에서 View 3을 사용할 수 있습니다.

SELECT p.Name, od.ProductID,

AVG(od.UnitPrice*(1.00-UnitPriceDiscount)) AS AvgPrice,

SUM(od.OrderQty) AS Units

FROM Sales.SalesOrderDetail AS od, Production.Product AS p

WHERE od.ProductID = p.ProductID AND od.ProductID between 0 and 995

GROUP BY p.Name, od.ProductID

View 4

이 뷰는 쿼리의 AVG가 계산되도록 뷰 정의에 SumPrice 및 Count 열을 포함시켜 Query 6에 대한 조건을 충족할 것입니다.

CREATE VIEW View4 WITH SCHEMABINDING AS

SELECT p.Name, od.ProductID,

SUM(od.UnitPrice*(1.00-UnitPriceDiscount)) AS SumPrice,

SUM(od.OrderQty) AS Units, COUNT_BIG(*) AS Count

FROM Sales.SalesOrderDetail AS od, Production.Product AS p

WHERE od.ProductID = p.ProductID AND od.UnitPrice > 10

GROUP BY p.Name, od.ProductID

GO

CREATE UNIQUE CLUSTERED INDEX VdiscountInd on View4 (Name, ProductID)

Query 8

Sales.SalesOrderHeader 테이블에 대한 조인이 추가되는 쿼리에서도 View 4의 동일 인덱스가 사용될 것입니다. 이 쿼리는 쿼리 FROM 구문에 열거된 테이블은 인덱싱된 뷰의 FROM 구문에 있는 테이블의 상위 집합이어야 한다는 조건을 충족합니다.

SELECT p.Name, od.ProductID,

AVG(od.UnitPrice*(1.00-UnitPriceDiscount)) AS AvgPrice,

SUM(od.OrderQty) AS Units

FROM Sales.SalesOrderDetail AS od, Production.Product AS p,

   Sales.SalesOrderHeader AS o

WHERE od.ProductID = p.ProductID AND o.SalesOrderID = od.SalesOrderID

   AND od.UnitPrice > 10

GROUP BY p.Name, od.ProductID

마지막 2개의 쿼리는 Query 8을 변형한 것입니다. 각각의 변형 쿼리는 최적화 프로그램 조건 가운데 하나에 위배되고 Query 8과 달리 View 4 를 사용할 수 없습니다.

Query 8a

뷰 정의의 UnitPrice > 10과 쿼리의 UnitPrice > 25 간의 WHERE 구문 불일치와 UnitPrice가 뷰에 나타나지 않는다는 사실 때문에 Q8a는 인덱싱된 뷰를 사용할 수 없습니다. 이러한 쿼리 검색 조건자는 뷰 정의에 있는 검색 조건자의 상위 집합이 되어야 합니다.

SELECT p.Name, od.ProductID, AVG(od.UnitPrice*(1.00-UnitPriceDiscount))

   AvgPrice, SUM(od.OrderQty) AS Units

FROM Sales.SalesOrderDetail AS od, Production.Product AS p,

   Sales.SalesOrderHeader AS o

WHERE od.ProductID = p.ProductID AND o.SalesOrderID = od.SalesOrderID

   AND od.UnitPrice > 25

GROUP BY p.Name, od.ProductID

Query 8b

Sales.SalesOrderHeader 테이블이 인덱싱된 뷰 V4 정의에 참여하지 않는다는 사실에 유의하십시오. 그럼에도 불구하고 이 테이블에 조건자를 추가하면 인덱싱된 뷰를 사용할 수 없게 됩니다. 추가된 조건자가 아래 Query 8b에 나타난 집계에 참여하는 추가 행을 변경 또는 제거할 수 있기 때문입니다.

SELECT p.Name, od.ProductID, AVG(od.UnitPrice*(1.00-UnitPriceDiscount))

   AS AvgPrice, SUM(od.OrderQty) AS Units

FROM Sales.SalesOrderDetail AS od, Production.Product AS p,

   Sales.SalesOrderHeader AS o

WHERE od.ProductID = p.ProductID AND o.SalesOrderID = od.SalesOrderID

   AND od.UnitPrice > 10 AND o.OrderDate >‘ 20040728’

GROUP BY p.Name, od.ProductID

View 4a

View 4a는 select 목록 및 GROUP BY 구문에 UnitPrice 열을 포함시킴으로써 View 4를 확장합니다. 25 이상의 값만 남겨 두도록 10 이상으로 알려진 UnitPrice 값이 추가 필터링되기 때문에 Query 8a는 View 4a를 사용할

CREATE VIEW View4a WITH SCHEMABINDING AS

SELECT p.Name, od.ProductID, od.UnitPrice,

SUM(od.UnitPrice*(1.00-UnitPriceDiscount)) AS SumPrice,

SUM(od.OrderQty) AS Units, COUNT_BIG(*) AS Count

FROM Sales.SalesOrderDetail AS od, Production.Product AS p

WHERE od.ProductID = p.ProductID AND od.UnitPrice > 10

GROUP BY p.Name, od.ProductID

GO

CREATE UNIQUE CLUSTERED INDEX VdiscountInd

   ON View4a (Name, ProductID, UnitPrice)

View 5

View 5는 select 및 GROUP BY 목록에 표현식을 포함하고 있습니다. LineTotal은 계산된 열이기 때문에 그 자체가 표현식이라는 점에 주목하 십시오. 이 표현식은 FLOOR 함수에 대한 호출 내에서 중첩됩니다.

CREATE VIEW View5 WITH SCHEMABINDING AS

SELECT FLOOR(LineTotal) FloorTotal, COUNT_BIG(*) C

FROM Sales.SalesOrderDetail

GROUP BY FLOOR(LineTotal)

GO

CREATE UNIQUE CLUSTERED INDEX iView5 ON View5(FloorTotal)

Query 9

Query 9는 select 및 GROUP BY 목록에 FLOOR(LineTotal) 표현식을 포함하고 있습니다. SQL Server 2005의 표현식으로 뷰 일치가 확장됨에 따라 이 쿼리는 View 5에서 인덱스를 사용할 수 있습니다.

SELECT TOP 5 FLOOR(LineTotal), Count(*)

FROM Sales.SalesOrderDetail

GROUP BY FLOOR(LineTotal)

ORDER BY COUNT(*) DESC

View 6

View 6은 매월 마지막 3일 간 품목 번호에 대한 정보를 저장합니다. 적은 수의 페이지에 이들 행을 클러스터하기 때문에 이 기간 동안의 Sales.SalesOrderDetail에 대한 쿼리를 신속하게 처리할 수 있습니다.

CREATE VIEW View6 WITH SCHEMABINDING AS

SELECT SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty,

   ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount, rowguid,

   ModifiedDate

FROM Sales.SalesOrderDetail

WHERE ModifiedDate IN ( convert(datetime,‘ 2004-07-31’, 120),

         convert(datetime,‘ 2004-07-30’, 120),

         convert(datetime,‘ 2004-07-29’, 120) )

GO

CREATE UNIQUE CLUSTERED INDEX VEndJulyO4Ind

   ON View6(SalesOrderID, SalesOrderDetailID)

GO

Query 10

아래 쿼리는 View 6와 일치하며 시스템은 Sales.SalesOrderDetail 테이블 전체를 스캔하는 대신 뷰에서 VendJuly04Ind 인덱스를 스캔하는 계획을 생성할 수 있습니다. 또한 이는 표현식 동치(뷰가 아닌 쿼리에서 날짜 순서가 다르고 데이터 형식이 서로 다르기 때문)와 조건자 포함(뷰에 저장된 결과의 하위 집합에 대해 쿼리가 실행되기 때문)을 입증합니다.

SELECT h.*, SalesOrderDetailID, CarrierTrackingNumber, OrderQty,

   ProductID, SpecialOfferID, UnitPrice,UnitPriceDiscount, d.rowguid,

   d.ModifiedDate

FROM Sales.SalesOrderHeader as h, Sales.SalesOrderDetail as d

WHERE (d.ModifiedDate =‘ 20040729’OR d.ModifiedDate =‘ 20040730’)

and d.SalesOrderID=h.SalesOrderID

View 7

또한 개발자들은 때때로 특수한 무결성 제약 조건을 적용하는 데 인덱싱된 뷰를 사용하는 것이 편리하다는 사실을 발견하게 됩니다. 예를 들 어, 인덱싱된 뷰에서는“열에 여러 개의 0 값이 있는 경우를 제외하고 테이블 T의 열 a는 고유하다”는 제약 조건을 적용할 수 있습니다. 아래에 나와있는 인덱싱된 뷰 View7은 이러한 제약 조건을 적용합니다. 다음과 같은 스크립트를 실행하면 View 7은 최종 삽입 작업 이전까지 성공적으로 실행됩니다. 이러한 명령문은 0이 아닌 중복 값을 추가할 수 있기 때문에 허용되지 않습니다.

USE tempdb

GO

CREATE TABLE T(a int)

GO

CREATE VIEW View7 WITH SCHEMABINDING

AS SELECT a

FROM dbo.T

WHERE a <> 0

GO

CREATE UNIQUE CLUSTERED INDEX IV on View7(a)

GO

-- legal:

INSERT INTO T VALUES(1)

INSERT INTO T VALUES(2)

INSERT INTO T VALUES(0)

INSERT INTO T VALUES(0) -- duplicate 0





-- dissalowed:

INSERT INTO T VALUES(2)

인덱싱된 뷰에 대한 FAQ

Q. 인덱스를 생성할 수 있는 뷰 종류에 제약이 있는 이유는 무엇입니까?

A. 뷰 유지 관리를 점진적으로 확대하는 것이 논리적으로 가능하도록 하고 유지 관리에 대한 부담을 증가시키는 뷰 생성 기능을 제한하며 SQL Server 시스템의 복잡성을 줄이기 위한 것입니다. 대형 뷰 세트가 비확정적이고 컨텍스트 종속적이기 때문에 DML 연산에 따라 컨텐트가 ‘변경’됩니다. 이들 뷰는 인덱싱을 실행할 수 없습니다. GETDATE 또는 SUSER_SNAME라고 정의된 모든 뷰를 예로 들 수 있습니다.

Q. 뷰의 첫번째 인덱스가 클러스터되어야 하고 고유해야 하는 이유는 무엇입니까?

A. 인덱싱된 뷰를 유지 관리하는 동안 키 값으로 뷰 레코드를 손쉽게 검색할 수 있도록 하고 유지 관리에 특별 로직이 필요한 복제본이 포함된 뷰가 생성되는 것을 막기 위해서는 고유해야 합니다. 또한 클러스터된 인덱스만이 고유한 값을 갖도록 하고 동시에 여러 행을 저장할 수 있기 때문에 클러스터되어야 합니다.

Q. 쿼리 최적화 프로그램이 쿼리 계획에서의 사용을 위해 인덱싱된 뷰를 선택하지 않는 이유는 무엇입니까?

A. 최적화 프로그램이 인덱싱된 뷰를 선택하지 않는 이유는 크게 3가지로 볼 수 있습니다.

1. Enterprise 또는 Developer 에디션이 아닌 다른 버전의 SQL Server를 사용하고 있습니다. Enterprise 및 Developer 에디션만이 자동 쿼리 및 인덱싱된 뷰 일치 기능을 지원합니다. 쿼리 프로세서가 기타 모든 에디션에서 인덱싱된 뷰를 사용할 수 있도록 이름으로 인덱싱된 뷰를 참조하고 NOEXPAND 힌트를 포함시키십시오.
2. 인덱싱된 뷰를 사용하는 비용이 기본 테이블에서 데이터를 입수하는 비용을 넘어서거나, 쿼리가 간단해 기본 테이블에 대한 쿼리를 신속하고 손쉽게 찾을 수 있습니다. 인덱싱된 뷰가 소형 테이블에 정의될 때 종종 이러한 경우가 나타납니다. 쿼리 프로세서가 인덱싱된 뷰를 사용하도록 하려면 NOEXPAND 힌트를 사용할 수 있습니다. 처음에 뷰를 명시적으로 참조하지 않은 경우에는 쿼리를 재작성해야 합니다. NOEXPAND를 통해 쿼리의 실제 비용을 계산하고 이를 뷰를 참조하지 않은 쿼리 계획의 실제 비용과 비교할 수 있습니다. 두 비용에 큰 차이가 없으면 인덱싱된 뷰 사용 여부를 결정하는 것이 그다지 중요하지 않다고 생각하면 됩니다.
3. 쿼리 최적화 프로그램은 쿼리를 인덱싱된 뷰에 일치시키지 않습니다. 뷰의 정의와 쿼리의 정의를 재확인하여 두 정의가 구조적으로 일치하도록 합니다. CASTS, 변환 및 논리적으로 쿼리를 변경하지 않는 기타 표현식은 이러한 일치에 방해가 될 수 있습니다. 또한 SQL Server가 수행하는 표현식 정규화와 동치(equivalence) 및 포함(subsumption) 관계 테스트에 대한 제한이 있습니다. 몇몇 동치 표현식이 동일하거나 다른 표현식에 의해 논리적으로 포함된 표현식이 실제로 포함되어 있다는 것을 보여 줄 수 없기 때문에 일치에 실패할 수 있습니다.

Q. 일주일에 한 번 데이터 웨어하우스를 업데이트하고 있습니다. 인덱싱된 뷰 때문에 주간에는 쿼리 속도는 향상되었지만 업데이트 속도는 느려졌습니다. 어떻게 해야 합니까?

A. 주 단위의 업데이트에 앞서 인덱싱된 뷰를 삭제하고 이후 이를 다시 생성하십시오.

Q. 뷰 복제본이 있기는 하지만 실제로 이를 유지 관리하고 싶습니다. 어떻게 해야 합니까?

A. 원하는 뷰의 모든 열이나 표현식을 그룹화해서 COUNT_BIG(*) 열을 추가한 뷰를 생성한 다음, 그룹화 열에서 클러스터된 고유 인덱스를 생성하십시오. 그룹화 프로세스는 고유성을 가져야 합니다. 이는 실제 동일한 뷰가 아니지만 사용자의 요구를 충족할 수 있습니다.

Q. 다른 뷰 위에 정의된 뷰를 가지고 있습니다. SQL Server는 상위 레벨 뷰의 인덱싱을 지원하지 않을 것입니다. 어떻게 해야 합니까?

A. 중첩된 뷰에 대한 정의를 상위 레벨 뷰까지 직접 확장하십시오. 그런 다음, 이를 인덱싱하거나 가장 안쪽의 뷰를 인덱싱하거나 뷰를 인덱싱하지 않을 수 있습니다.

Q. 인덱싱된 뷰가 WITH SCHEMABINDING로 정의되어야 하는 이유는 무엇입니까?

A. 사용자의 뷰 액세스에 관계없이

• 뷰가 참조한 모든 객체를 schemaname.objectname을 사용해 명백하게 정의해야 하기 때문입니다.
• 또한 허용되지 않는 방식으로 뷰 정의를 수행하거나 SQL Server가 뷰에서 인덱스를 재생성하도록 강요하는 방법으로 뷰 정의에서 참조된 객체를 변경할 수는 없습니다.

Q. 인덱싱된 뷰에서 OUTER JOIN을 사용할 수 없는 이유는 무엇입니까?

A. 기본 테이블에 데이터를 삽입하면 OUTER JOIN에 따라 인덱싱된 뷰에서 행이 논리적으로 사라질 수 있습니다. 따라서, OUTER JOIN 뷰의 업데이트가 점차 복잡해지고 표준 (INNER) JOIN 기반의 뷰에 비해 업데이트 속도도 느려질 수 있습니다.

자세한 내용

Microsoft SQL Server 2005 Books Online에는 인덱싱된 뷰에 대한 자세한 정보가 포함되어 있습니다. 자세한 내용은 다음을 참조하십시오.

• Microsoft SQL Server 개발자 센터 http://msdn.microsoft.com/sql
• Microsoft SQL Server TechNet 사이트 http://www.microsoft.com/korea/technet/sql
• Microsoft SQL Server 웹사이트 http://www.microsoft.com/korea/sql