SQL Server 쿼리 처리 아키텍처 _ SQL 문 처리 (3/4) – 뷰(View)의 인덱스 확인

SQL Server 쿼리 처리 아키텍처 _ SQL 문 처리 (3/4)

– 뷰(View)의 인덱스 확인

 

• Version : SQL Server 2005, 2008, 2008R2, 2012

 

인덱스와 마찬가지로 SQL Server에서는 쿼리 최적화 프로그램에서 쿼리 계획에 인덱싱된 뷰를 사용하는 것이 효과적이라고 판단하는 경우 인덱싱된 뷰를 사용한다.

 

[SQL Server 쿼리 최적화 프로그램에서 인덱싱된 뷰 사용]

• 아래의 세션 옵션이 ON 설정되어 있어야 한다.
  • ANSI_NULLS
  • ANSI_PADDING
  • ANSI_WARNNINGS
  • ARITHABORT
  • CONCAT_NULL_YIELDS_NULL
  • QUOTED_IDENTIFIER

 

• 아래 옵션이 OFF 설정 되어 있어야 한다.
  • NUMERIC_ROUNDABORT

 

• 쿼리의 요소와 뷰 인덱스 열 간의 일치
  • WHERE 절의 검색 조건자
  • 조인작업
  • 집계함수
  • GROUP BY 절
  • 테이블 참조

 

• 인덱스 사용시 예상비용이 쿼리 최적화 프로그램에서 고려하는 액세스 메커니즘의 비용 중 가장 낮은 것.

 

• 인덱싱된 뷰의 테이블 참조에 해당하는 쿼리에서 뷰를 확장하여 기본 테이블에 액세스하는 방식으로 테이블을 참조하거나 직접 테이블을 참조하는 경우 쿼리에서 참조하는 모든 테이블에 같은 힌트 집합이 적용되어 있어야 한다.

 

READCOMMITTED 및 READCOMMITTEDLOCK 힌트는 현재 트랜잭션 격리 수준에 관계없이 항상 이 컨텍스트에서 다르게 고려된다.

 

SET 옵션 및 테이블 힌트에 대한 요구 사항을 제외하고 위의 사항은 쿼리 최적화 프로그램에서 쿼리가 인덱스로 처리할 수 있는지 여부를 확인하는데 사용하는 규칙과 동일하다.

 

쿼리 최적화 프로그램에서 인덱싱된 뷰를 사용하도록 쿼리의 FROM 절에서 인덱싱된 뷰를 명시적으로 참조할 필요가 없다. 쿼리 최적화 프로그램은 FROM절에서 참조하는 인덱싱된 뷰를 표준 뷰로 간주하고 처리 한다. 뷰 정의를 쿼리로 확장하고 인덱싱된 뷰 일치가 수행 된다. 쿼리 최적화 프로그램에서 선택하는 최종 실행계획에 인덱싱된 뷰가 사용될 수 있으며 계획이 뷰에서 참조하는 기본 테이블에서 액세스하여 뷰에서 필요한 데이터를 구체화 할 수 있다. 이중 가장 낮은 비용이 선택된다.

 

[인덱싱된 뷰에 힌트 사용]

EXPAND VIEWS 쿼리 힌트를 사용하여 쿼리에 뷰 인덱스가 사용되지 않도록 하거나 NOEXPAND 테이블 힌트를 사용하여 쿼리의 FROM 절에 지정된 인덱싱된 뷰에 인덱스가 사용되도록 할 수 있다. 그러나 쿼리 최적화 프로그램이 각 쿼리에 사용할 최상의 액세스 방법을 동적으로 결정하도록 해야 한다.

EXPAND와 NOEXPAND는 성능을 크게 향상시키는 것으로 확인된 특정 경우에만 사용한다. EXPAND VIEWS 옵션은 쿼리 최적화 프로그램이 전체 쿼리에 뷰 인덱스를 사용하지 않도록 지정한다. 뷰에 NOEXPAND를 지정하면 쿼리 최적화 프로그램은 뷰에 정의된 인덱스의 사용을 고려 한다.

 

선택적 INDEX()절을 사용하여 NOEXPAND를 지정하는 쿼리 최적화 프로그램은 지정된 인덱스를 사용한다. NOEXPAND는 인덱싱된 뷰에만 지정할 수 있고 인덱싱된지 않은 뷰에는 지정할 수 없다.

 

뷰를 포함하는 쿼리에서 NOEXPAND나 EXPAND VIEWS를 지정하지 않으면 뷰가 확장되어 기본 테이블에 액세스 한다. 뷰를 구성하는 쿼리에 테이블 힌트가 포함된 경우 해당 힌트는 기본 테이블로 전파 된다. 뷰의 기본 테이블에 있는 힌트 집합이 모두 동일하면 쿼리를 인덱싱된 뷰와 일치 시킨다. 대부분의 경우에는 뷰에서 직접 상속되기 때문에 일치 한다. 그러나 쿼리가 뷰 대신 테이블을 참조하고 직접 적용된 힌트가 동일하지 않으면 인덱싱된 뷰와 일치 시킬 수 없다. 뷰 확장 후 쿼리에서 참조하는 테이블에 INDEX, PAGLOCK, ROWLOCK, TABKICKX, UPDLOCK 또는 XLOCK 힌트가 적용되면 쿼리를 인덱싱된 뷰와 일치 시킬 수 없다.

 

INDEX(index_val[…n])형식의 테이블 힌트가 쿼리의 뷰를 참조하는 경우 NOEXPAND 힌트를 지정하지 않으면 인덱스 힌트가 무시 된다. 특정 인덱스를 사용하도록 지정하려면 NOEXPAND를 사용 한다.

 

SQL Server 2008의 인덱싱된 뷰 정의에는 힌트가 허용 되지 않는다. 호환모드 80 이상에서 SQL Server는 인덱싱도니 뷰 정의를 유지관리 할 때나 인덱셍된 뷰를 사용하는 쿼리를 실행할 때 인덱싱된 뷰 정의 내의 힌트를 무시한다. 80 호환 모드에서는 인덱싱된 뷰 정의에 힌트를 사용해도 구문 오류가 발생하지 않지만 무시 된다.

 

 

[인덱싱된 뷰 사용]

다음 스크립트는 뷰를 만들고 생성된 뷰에 인덱스를 만든다.

USE AdventureWorks2008R2; GO   --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; GO   --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 ProductID BETWEEN 700 and 800 AND OrderDate >= CONVERT(datetime,'05/01/2002',101) GROUP BY OrderDate, ProductID ORDER BY Rev DESC; GO

 

 

 

--This query can 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 DATEPART(mm,OrderDate)= 3 AND DATEPART(yy,OrderDate) = 2002 GROUP BY OrderDate ORDER BY OrderDate ASC; GO

 

 

[참고자료]

http://msdn.microsoft.com/ko-kr/library/ms181151(v=sql.105).aspx

http://msdn.microsoft.com/ko-kr/library/ms191432(v=sql.105).aspx

 

 

2013-05-06 / 강성욱 / http://sqlmvp.kr