다음을 통해 공유

인덱싱된 뷰 만들기

  • 아티클

적용 대상: SQL Server Azure SQL 데이터베이스 Azure SQL Managed Instance

이 문서에서는 뷰에서 인덱스를 만드는 방법을 설명합니다. 뷰에서 만들어진 첫 번째 인덱스는 고유한 클러스터형 인덱스여야 합니다. 고유 클러스터형 인덱스가 만들어진 후에 비클러스터형 인덱스를 더 만들 수 있습니다. 뷰에 고유한 클러스터형 인덱스를 만들면 클러스터형 인덱스가 있는 테이블을 저장하는 것과 동일한 방식으로 데이터베이스에 뷰가 저장되므로 쿼리 성능이 향상됩니다. 쿼리 최적화 프로그램은 인덱싱된 뷰를 사용하여 쿼리 실행 속도를 높일 수 있습니다. 최적화 프로그램이 해당 뷰를 대체 대상으로 고려하기 위해 쿼리에서 뷰를 참조할 필요는 없습니다.

단계

인덱싱된 뷰를 만들려면 다음 단계가 필요하며 인덱싱된 뷰의 성공적인 구현에 중요합니다.

  1. 뷰에서 참조되는 모든 기존 테이블에 대한 SET 옵션이 올바른지 확인합니다.
  2. 테이블과 뷰를 만들기 전에 세션에 대한 SET 옵션이 올바르게 설정되었는지 확인합니다.
  3. 뷰 정의가 결정적인지 확인합니다.
  4. 기본 테이블에 뷰와 동일한 소유자가 있는지 확인합니다.
  5. WITH SCHEMABINDING 옵션을 사용하여 뷰를 만듭니다.
  6. 뷰에서 고유한 클러스터형 인덱스를 만듭니다.

Important

많은 수의 인덱싱된 뷰 또는 더 적은 수의 매우 복잡한 인덱싱된 뷰에서 참조하는 테이블에서 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 옵션을 필수 값 열에 표시된 값으로 설정해야 합니다.

  • 뷰와 뷰의 후속 인덱스가 만들어집니다.
  • 뷰를 만들 때 뷰에서 참조되는 기본 테이블입니다.
  • 인덱싱된 뷰에 참가하는 테이블에서 삽입, 업데이트 또는 삭제 작업이 수행됩니다. 이 요구 사항에는 대량 복사, 복제 및 분산 쿼리와 같은 작업이 포함됩니다.
  • 인덱싱된 인덱스는 쿼리 최적화 프로그램이 쿼리 계획을 작성할 때 사용됩니다.
SET 옵션 필수 값 기본 서버 값 기본값

OLE DB 및 ODBC 값		 기본값

DB-Library 값
ANSI_NULLS 켜기 켜기 켜기 OFF
ANSI_PADDING 켜기 켜기 켜기 OFF
ANSI_WARNINGS 1 켜기 켜기 켜기 OFF
ARITHABORT 켜기 켜기 OFF OFF
CONCAT_NULL_YIELDS_NULL 켜기 켜기 켜기 OFF
NUMERIC_ROUNDABORT OFF OFF OFF OFF
QUOTED_IDENTIFIER 켜기 켜기 켜기 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 사용
    행 집합 함수(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(Transact-SQL)를 참조하세요. 결정적 식과 비결정적 식에 대한 자세한 내용은 이 페이지의 고려 사항 섹션을 참조하세요.

문자열을 datetime 또는 smalldatetime으로 암시적으로 변환하는 작업과 관련된 식은 비결정적인 것으로 간주됩니다. 자세한 내용은 날짜 값으로 리터럴 날짜 문자열의 비결정적 변환을 참조하세요.

인덱싱된 뷰 관련 성능 고려 사항

많은 수의 인덱싱된 뷰 또는 더 적은 수의 복잡한 인덱싱된 뷰에서 참조하는 테이블에서 DML(UPDATE, DELETE 또는 INSERT)을 실행할 때 DML 실행 도중 해당 인덱싱된 뷰도 업데이트되어야 합니다. 따라서 DML 쿼리 성능이 크게 저하되거나, 경우에 따라 쿼리 계획을 생성할 수도 없습니다. 이러한 시나리오에서는 프로덕션 사용 전에 DML 쿼리를 테스트하고, 쿼리 계획을 분석하고, DML 문을 조정/단순화합니다.

데이터베이스 엔진에서 인덱싱된 뷰를 사용하지 않게 하려면 쿼리에 OPTION (EXPAND VIEWS) 힌트를 포함합니다. 또한 나열된 옵션이 하나라도 잘못 설정된 경우 최적화 프로그램에서 뷰의 인덱스를 사용하지 못하게 됩니다. OPTION (EXPAND VIEWS) 힌트에 대한 자세한 내용은 SELECT Transact-SQL을 참조하세요.

다양한 추가 고려 사항

  • 인덱싱된 뷰의 열에 대한 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
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
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) 서비스 팩 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(Transact-SQL)를 참조하세요.

다음 단계