인덱싱된 뷰 만들기
적용 대상: SQL Server
Azure SQL 데이터베이스
Azure SQL Managed Instance
이 문서에서는 뷰에서 인덱스를 만드는 방법을 설명합니다. 뷰에서 만들어진 첫 번째 인덱스는 고유한 클러스터형 인덱스여야 합니다. 고유 클러스터형 인덱스가 만들어진 후에 비클러스터형 인덱스를 더 만들 수 있습니다. 뷰에 고유한 클러스터형 인덱스를 만들면 클러스터형 인덱스가 있는 테이블을 저장하는 것과 동일한 방식으로 데이터베이스에 뷰가 저장되므로 쿼리 성능이 향상됩니다. 쿼리 최적화 프로그램은 인덱싱된 뷰를 사용하여 쿼리 실행 속도를 높일 수 있습니다. 최적화 프로그램이 해당 뷰를 대체 대상으로 고려하기 위해 쿼리에서 뷰를 참조할 필요는 없습니다.
단계
인덱싱된 뷰를 만들려면 다음 단계가 필요하며 인덱싱된 뷰의 성공적인 구현에 중요합니다.
- 뷰에서 참조되는 모든 기존 테이블에 대한
SET
옵션이 올바른지 확인합니다. - 테이블과 뷰를 만들기 전에 세션에 대한
SET
옵션이 올바르게 설정되었는지 확인합니다. - 뷰 정의가 결정적인지 확인합니다.
- 기본 테이블에 뷰와 동일한 소유자가 있는지 확인합니다.
WITH SCHEMABINDING
옵션을 사용하여 뷰를 만듭니다.- 뷰에서 고유한 클러스터형 인덱스를 만듭니다.
많은 수의 인덱싱된 뷰 또는 더 적은 수의 복잡한 인덱싱된 뷰에서 참조하는 테이블에서 UPDATE
, DELETE
또는 INSERT
연산(데이터 조작 언어, 즉 DML)을 실행할 때 해당 인덱싱된 뷰도 업데이트되어야 합니다. 따라서 DML 쿼리 성능이 크게 저하되거나, 경우에 따라 쿼리 계획을 생성할 수도 없습니다.
이러한 시나리오에서는 프로덕션 사용 전에 DML 쿼리를 테스트하고, 쿼리 계획을 분석하고, DML 문을 조정/단순화합니다.
인덱싱된 뷰에 필요한 SET 옵션
쿼리가 실행될 때 다른 SET
옵션이 활성화되어 있으면 같은 식을 계산해도 데이터베이스 엔진에서 다른 결과가 나올 수 있습니다. 예를 들어 SET
옵션 CONCAT_NULL_YIELDS_NULL
이 ON
으로 설정된 후 식 'abc' + NULL
의 결과로 NULL
값이 반환됩니다. 그러나 CONCAT_NULL_YIELDS_NULL
이 OFF
로 설정된 후에 동일한 식에서 abc
이 생성됩니다.
뷰를 올바르게 유지하고 일관된 결과를 반환할 수 있도록 인덱싱된 뷰에는 여러 SET
옵션에 대한 고정 값이 필요합니다. 다음 조건이 발생할 때마다 다음 테이블의 SET
옵션을 Required value
열에 표시된 값으로 설정해야 합니다.
- 뷰와 뷰의 후속 인덱스가 만들어집니다.
- 뷰를 만들 때 뷰에서 참조되는 기본 테이블입니다.
- 인덱싱된 뷰에 참가하는 테이블에서 삽입, 업데이트 또는 삭제 작업이 수행됩니다. 이 요구 사항에는 대량 복사, 복제 및 분산 쿼리와 같은 작업이 포함됩니다.
- 인덱싱된 인덱스는 쿼리 최적화 프로그램이 쿼리 계획을 작성할 때 사용됩니다.
SET 옵션 | 필수 값 | 기본 서버 값 | 기본값 OLE DB 및 ODBC 값 |
기본값 DB-Library 값 |
---|---|---|---|---|
ANSI_NULLS |
ON |
ON |
ON |
OFF |
ANSI_PADDING |
ON |
ON |
ON |
OFF |
ANSI_WARNINGS 1 |
ON |
ON |
ON |
OFF |
ARITHABORT |
ON |
ON |
OFF |
OFF |
CONCAT_NULL_YIELDS_NULL |
ON |
ON |
ON |
OFF |
NUMERIC_ROUNDABORT |
OFF |
OFF |
OFF |
OFF |
QUOTED_IDENTIFIER |
ON |
ON |
ON |
OFF |
1 ANSI_WARNINGS
를 ON
으로 설정하면 ARITHABORT
가 암시적으로 ON
으로 설정됩니다.
OLE DB 또는 ODBC 서버 연결을 사용하는 경우 수정해야 하는 유일한 값은 ARITHABORT
설정입니다. 모든 DB-라이브러리 값은 서버 수준에서 sp_configure
명령을 사용하거나 애플리케이션에서 SET
명령을 사용하여 올바르게 설정해야 합니다.
Important
계산 열의 첫 번째 인덱싱된 뷰 또는 인덱스가 서버의 모든 데이터베이스에 만들어지는 즉시 ARITHABORT
사용자 옵션을 ON
서버 전체로 설정하는 것이 좋습니다.
결정적 뷰 요구 사항
인덱싱된 뷰의 정의는 결정적이어야 합니다. 선택 목록의 모든 식과 WHERE
및 GROUP BY
절이 결정적이면 뷰가 결정적입니다. 결정적 식은 지정된 입력 집합으로 계산할 때마다 항상 같은 결과 집합을 반환합니다. 결정 함수만 결정적 식에 참여할 수 있습니다. 예를 들어 DATEADD
함수는 세 매개 변수에 지정된 인수 값 집합에 대해 항상 같은 결과를 반환하므로 결정적 함수입니다. GETDATE
는 항상 동일한 인수로 호출되기 때문에 결정 함수는 아니지만 여기서 반환되는 값은 실행될 때마다 변경됩니다.
뷰 열이 결정적인지 여부를 확인하려면 COLUMNPROPERTY 함수의 IsDeterministic
속성을 사용합니다. 스키마 바인딩이 있는 뷰의 결정적 열이 정확한지 확인하려면 COLUMNPROPERTY
함수의 IsPrecise
속성을 사용합니다. COLUMNPROPERTY
는 1
(TRUE
일 경우), 0
(FALSE
일 경우), NULL
(유효하지 않은 입력일 경우)을 반환합니다. 이는 해당 열이 결정적이지 않거나 정확하지 않음을 의미합니다.
식이 결정적이더라도 float 식을 포함하는 경우 정확한 결과는 프로세서 아키텍처 또는 마이크로코드 버전에 따라 달라집니다. 데이터 무결성을 보장하기 위해 이런 식은 인덱싱된 뷰의 키가 아닌 열로만 참여할 수 있습니다. float 식을 포함하지 않는 결정적 식을 정확하다고 합니다. 정확한 결정적 식만 키 열과 인덱싱된 뷰의 WHERE
또는 GROUP BY
절에 참여할 수 있습니다.
추가 요구 사항
SET
옵션 및 결정적 함수 요구 사항 외에 다음 요구 사항도 충족해야 합니다.
CREATE INDEX
를 실행하는 사용자는 뷰의 소유자여야 합니다.인덱스를 만들 때
IGNORE_DUP_KEY
인덱스 옵션은OFF
(기본 설정)로 설정되어야 합니다.테이블은 뷰 정의에서 두 부분으로 구성된 이름(
<schema>.<tablename>
)으로 참조해야 합니다.WITH SCHEMABINDING
옵션을 사용하여 뷰에서 참조되는 사용자 정의 함수를 만들어야 합니다.뷰에서 참조하는 사용자 정의 함수는 두 부분으로 구성된 이름인
<schema>.<function>
으로 참조되어야 합니다.사용자 정의 함수의 데이터 액세스 속성은
NO SQL
여야 하며 외부 액세스 속성은NO
여야 합니다.CLR(공용 언어 런타임) 함수는 뷰의 선택 목록에 표시될 수 있지만 클러스터형 인덱스 키 정의의 일부가 될 수는 없습니다. CLR 함수는 뷰의
WHERE
절이나 뷰에 있는JOIN
연산의ON
절에 나타날 수 없습니다.뷰 정의에 사용되는 CLR 사용자 정의 형식의 CLR 함수 및 메서드에는 다음 테이블과 같은 속성 집합이 있어야 합니다.
속성 참고 항목 결정적 = 참 Microsoft .NET Framework 메서드의 특성으로 명시적으로 선언해야 합니다. 정확 = 참 .NET Framework 메서드의 특성으로 명시적으로 선언해야 합니다. 데이터 액세스 = SQL 없음 DataAccess
특성을DataAccessKind.None
특성으로,SystemDataAccess
특성을SystemDataAccessKind.None
특성으로 설정하여 걸정합니다.외부 액세스 = 아니요 이 속성의 기본값을 CLR 루틴에 대해 기본적으로 아니요로 설정합니다. 뷰는
WITH SCHEMABINDING
옵션을 사용하여 만들어야 합니다.뷰는 뷰와 동일한 데이터베이스에 있는 기본 테이블만 참조해야 합니다. 뷰는 다른 뷰를 참조할 수 없습니다.
GROUP BY
이(가) 있는 경우 VIEW 정의에는COUNT_BIG(*)
이(가) 포함되어야 하며HAVING
이(가) 포함되어서는 안 됩니다. 이러한GROUP BY
제약 조건은 인덱싱된 뷰 정의에만 적용됩니다. 쿼리는 이러한GROUP BY
제약 조건을 충족하지 않는 경우에도 실행 계획에 인덱싱된 뷰를 사용할 수 있습니다.뷰 정의에
GROUP BY
절이 포함된 경우 고유한 클러스터형 인덱스의 키는GROUP BY
절에 지정된 열만 참조할 수 있습니다.뷰 정의의
SELECT
문에는 다음 Transact-SQL 구문이 포함되어서는 안됩니다.Transact-SQL 함수 가능한 대안 COUNT
COUNT_BIG
사용ROWSET
함수(OPENDATASOURCE
,OPENQUERY
,OPENROWSET
,OPENXML
)산술 평균( AVG
)COUNT_BIG
및SUM
함수를 별도의 열로 사용통계 집계 함수( STDEV
,STDEVP
,VAR
,VARP
)Null 허용 식을 참조하는 SUM
함수SUM()
내부에서ISNULL
함수를 사용하여 식을 Null 불허로 설정기타 집계 함수( MIN
,MAX
,CHECKSUM_AGG
,STRING_AGG
)사용자 정의 집계 함수(SQL CLR) SELECT 절 Transact-SQL 요소 가능한 대안 WITH cte AS
CTE(공통 테이블 식) WITH
SELECT
하위 쿼리 SELECT
SELECT [ <table>. ] *
열 이름을 명시적으로 지정 SELECT
SELECT DISTINCT
GROUP BY
사용SELECT
SELECT TOP
SELECT
순위 지정 또는 집계 창 함수가 포함된 OVER
절FROM
LEFT OUTER JOIN
FROM
RIGHT OUTER JOIN
FROM
FULL OUTER JOIN
FROM
OUTER APPLY
FROM
CROSS APPLY
FROM
파생 테이블 식(즉, SELECT
절에서FROM
사용)FROM
셀프 조인 FROM
테이블 변수 FROM
인라인 테이블 반환 함수 FROM
다중 문 테이블 반환 함수 FROM
PIVOT
,UNPIVOT
FROM
TABLESAMPLE
FROM
FOR SYSTEM_TIME
임시 기록 테이블을 직접 쿼리 WHERE
전체 텍스트 조건( CONTAINS
,FREETEXT
,CONTAINSTABLE
,FREETEXTTABLE
)GROUP BY
CUBE
,ROLLUP
또는GROUPING SETS
연산자각 GROUP BY
열 조합에 대해 별도의 인덱싱된 뷰 정의GROUP BY
HAVING
집합 연산자 UNION
,UNION ALL
,EXCEPT
,INTERSECT
WHERE
절에서 각각OR
,AND NOT
및AND
사용ORDER BY
ORDER BY
ORDER BY
OFFSET
원본 열 유형 가능한 대안 사용되지 않는 큰 값 열 유형(text, ntext, image) 각각 varchar(max), nvarchar(max) 및 varbinary(max)로 열을 마이그레이션합니다. xml 또는 FILESTREAM 열 인덱스 키의 float 1 열 스파스 열 집합 1 인덱싱된 뷰는 float 열을 포함할 수 있지만 이러한 열은 클러스터형 인덱스 키에 포함될 수 없습니다.
Important
인덱싱된 뷰는 임시 쿼리를 기반으로 사용할 수 없습니다(
FOR SYSTEM_TIME
절을 사용하는 쿼리).
datetime 및 smalldatetime 권장 사항
인덱싱된 뷰에서 datetime 및 smalldatetime 문자열 리터럴을 참조할 때 결정적 날짜 형식 스타일을 사용하여 리터럴을 원하는 날짜 유형으로 명시적으로 변환하는 것이 좋습니다. 결정적 날짜 형식 스타일 목록은 CAST 및 CONVERT를 참조하세요. 결정적 식과 비결정적 식에 대한 자세한 내용은 이 페이지의 고려 사항 섹션을 참조하세요.
문자열을 datetime 또는 smalldatetime 으로 암시적으로 변환하는 작업과 관련된 식은 비결정적인 것으로 간주됩니다. 자세한 내용은 날짜 값으로 리터럴 날짜 문자열의 비결정적 변환을 참조하세요.
인덱싱된 뷰 관련 성능 고려 사항
많은 수의 인덱싱된 뷰 또는 더 적은 수의 복잡한 인덱싱된 뷰에서 참조하는 테이블에서 DML(UPDATE
, DELETE
또는 INSERT
)을 실행할 때 DML 실행 도중 해당 인덱싱된 뷰도 업데이트되어야 합니다. 따라서 DML 쿼리 성능이 크게 저하되거나, 경우에 따라 쿼리 계획을 생성할 수도 없습니다. 이러한 시나리오에서는 프로덕션 사용 전에 DML 쿼리를 테스트하고, 쿼리 계획을 분석하고, DML 문을 조정/단순화합니다.
데이터베이스 엔진에서 인덱싱된 뷰를 사용하지 않게 하려면 쿼리에 OPTION(EXPAND VIEWS) 힌트를 포함합니다. 또한 나열된 옵션이 하나라도 잘못 설정된 경우 이 옵션으로 최적화 프로그램에서 뷰의 인덱스를 사용하지 못하게 됩니다. OPTION (EXPAND VIEWS)
힌트에 대한 자세한 내용은 SELECT를 참조하세요.
추가 고려 사항
인덱싱된 뷰의 열에 대한
large_value_types_out_of_row
옵션의 설정은 기본 테이블의 해당 열에 대한 설정에서 상속됩니다. 이 값은 sp_tableoption을 사용하여 설정합니다. 이 식에서 형성된 열의 기본 설정은0
입니다. 큰 값 형식은 행 내에 저장된다는 의미입니다.인덱싱된 뷰는 분할된 테이블에 만들 수 있으며 자체 분할될 수 있습니다.
뷰를 삭제하면 해당 뷰의 모든 인덱스가 삭제됩니다. 클러스터형 인덱스를 삭제하면 해당 뷰의 모든 비클러스터형 인덱스와 자동 생성된 통계가 자동으로 삭제됩니다. 해당 뷰에서 사용자가 만든 통계는 유지 관리됩니다. 비클러스터형 인덱스는 개별적으로 삭제할 수 있습니다. 뷰에서 클러스터형 인덱스를 삭제하면 저장된 결과 집합이 제거되고 최적화 프로그램은 뷰를 표준 보기처럼 처리하는 것으로 돌아갑니다.
테이블 및 뷰의 인덱스를 사용하지 않도록 설정할 수 있습니다. 테이블의 클러스터형 인덱스가 비활성화되면 테이블과 관련된 뷰의 인덱스도 비활성화됩니다.
사용 권한
사용자는 뷰를 만들려면 데이터베이스에는 CREATE VIEW
사용 권한이 필요하고 뷰를 만들 스키마에는 ALTER
사용 권한이 필요합니다. 기본 테이블이 서로 다른 스키마 내에 있는 경우 최소한 테이블에 대한 REFERENCES
권한이 필요합니다. 인덱스를 만든 사용자가 뷰를 만든 사용자와 다른 경우 인덱스 만들기에만 뷰에 대한 ALTER
권한이 필요합니다(스키마의 ALTER
가 적용됨).
인덱스는 참조된 테이블과 소유자가 동일한 뷰에서만 만들 수 있습니다. 이 개념을 뷰와 테이블 간의 온전한 소유권 체인이라고도 합니다. 일반적으로 테이블과 뷰가 동일한 스키마 내에 있는 경우 스키마 내의 모든 개체에 동일한 스키마 소유자가 적용됩니다. 따라서 뷰를 만들 수 있으며 뷰의 소유자가 될 수 없습니다. 반면에 스키마 내의 개별 개체에는 다른 명시적 소유자가 있을 수도 있습니다. 소유자가 스키마 소유자와 다른 경우 sys.tables
의 principal_id
열에 값이 포함됩니다.
인덱싱된 뷰 만들기: T-SQL 예시
다음 예에서는 뷰를 만들고 AdventureWorks
데이이터베이스의 이 뷰에 인덱스를 만듭니다.
--Set the options to support indexed views.
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING,
ANSI_WARNINGS,
CONCAT_NULL_YIELDS_NULL,
ARITHABORT,
QUOTED_IDENTIFIER,
ANSI_NULLS ON;
--Create view with SCHEMABINDING.
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_BIG(*) AS COUNT
FROM Sales.SalesOrderDetail AS od,
Sales.SalesOrderHeader AS o
WHERE od.SalesOrderID = o.SalesOrderID
GROUP BY OrderDate,
ProductID;
GO
--Create an index on the view.
CREATE UNIQUE CLUSTERED INDEX IDX_V1 ON Sales.vOrders (
OrderDate,
ProductID
);
GO
다음 두 쿼리는 FROM
절에 뷰가 지정되지 않았더라도 인덱싱된 뷰를 사용하는 방법을 보여줍니다.
--This query can use the indexed view even though the view is
--not specified in the FROM clause.
SELECT SUM(UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)) AS Rev,
OrderDate,
ProductID
FROM Sales.SalesOrderDetail AS od
INNER JOIN Sales.SalesOrderHeader AS o
ON od.SalesOrderID = o.SalesOrderID
AND o.OrderDate >= CONVERT(DATETIME, '05/01/2012', 101)
WHERE od.ProductID BETWEEN 700
AND 800
GROUP BY OrderDate,
ProductID
ORDER BY Rev DESC;
GO
--This query will also use the above indexed view.
SELECT OrderDate,
SUM(UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)) AS Rev
FROM Sales.SalesOrderDetail AS od
INNER JOIN Sales.SalesOrderHeader AS o
ON od.SalesOrderID = o.SalesOrderID
AND o.OrderDate >= CONVERT(DATETIME, '03/01/2012', 101)
AND o.OrderDate < CONVERT(DATETIME, '04/01/2012', 101)
GROUP BY OrderDate
ORDER BY OrderDate ASC;
마지막으로, 이 예시에서는 인덱싱된 뷰에서 직접 쿼리하는 방법을 보여줍니다. SQL Server 2016(13.x) Service Pack 1 이전에는 쿼리 최적화 프로그램에서 인덱싱된 뷰를 자동으로 사용하는 것은 SQL Server의 특정 버전에서만 지원됩니다. SQL Server Standard 버전에서는 NOEXPAND
쿼리 힌트를 사용하여 인덱싱된 뷰를 직접 쿼리해야 합니다. SQL Server 2016(13.x) 서비스 팩 1부터 모든 버전은 인덱싱된 뷰의 자동 사용을 지원합니다. Azure SQL 데이터베이스 및 Azure SQL Managed Instance는 NOEXPAND
힌트를 지정하지 않고도 인덱싱된 뷰를 자동으로 사용할 수 있도록 지원합니다. 자세한 내용은 테이블 힌트(Transact-SQL)를 참조하세요.
--This query uses the indexed view directly, on Enterprise edition.
SELECT OrderDate, Revenue
FROM Sales.vOrders
WHERE OrderDate >= CONVERT(DATETIME, '03/01/2012', 101)
AND OrderDate < CONVERT(DATETIME, '04/01/2012', 101)
ORDER BY OrderDate ASC;
--This query uses the indexed view directly, with the NOEXPAND hint.
SELECT OrderDate, Revenue
FROM Sales.vOrders WITH (NOEXPAND)
WHERE OrderDate >= CONVERT(DATETIME, '03/01/2012', 101)
AND OrderDate < CONVERT(DATETIME, '04/01/2012', 101)
ORDER BY OrderDate ASC;
자세한 내용은 CREATE VIEW를 참조하세요.