데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

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

 


강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

 

No. Subject Author Date Views
1773 DMV를 이용한 캐시된 저장 프로시저 통계 정보 확인 jevida(강성욱) 2016.09.28 991
1772 SQL Server 쿼리 처리 아키텍처_저장 프로시저 및 트리거 실행 jevida(강성욱) 2016.09.27 941
1771 SQL Server 쿼리 처리 아키텍처 _ SQL 문 처리 (4/4) – 분산형 분할 뷰(View) 확인 jevida(강성욱) 2016.09.27 1337
» SQL Server 쿼리 처리 아키텍처 _ SQL 문 처리 (3/4) – 뷰(View)의 인덱스 확인 jevida(강성욱) 2016.09.27 973
1769 SQL Server 쿼리 처리 아키텍처 _ SQL 문 처리 (2/4) – 뷰(View) 확인 jevida(강성욱) 2016.09.27 1447
1768 SQL Server 쿼리 처리 아키텍처 _ SQL 문 처리 (1/4) – SQL 문 최적화 및 Worktables jevida(강성욱) 2016.09.27 934
1767 SQL Server DMV를 이용한 통계 정보 확인 jevida(강성욱) 2016.09.27 1505
1766 DMV를 이용한 플랜 캐시 사용 정보 확인 jevida(강성욱) 2016.09.27 1174
1765 SQL Server 테이블 및 인덱스 구조 아키텍처(4/4) – 비클러스터형 인덱스 구조 jevida(강성욱) 2016.09.27 1066
1764 SQL Server 테이블 및 인덱스 구조 아키텍처(3/4) – 클러스터형 인덱스 구조 jevida(강성욱) 2016.09.27 1371
1763 SQL Server 테이블 및 인덱스 구조 아키텍처(2/4) – 힙 구조 jevida(강성욱) 2016.09.27 1069
1762 SQL Server 테이블 및 인덱스 구조 아키텍처(1/4) – 테이블 및 인덱스 구성 jevida(강성욱) 2016.09.27 1127
1761 SQL Server 트랜잭션 로그 아키텍처(4/4) – 미리 쓰기 트랜잭션 로그 jevida(강성욱) 2016.09.27 1537
1760 SQL Server 트랜잭션 로그 아키텍처(3/4) – 검사점 및 로그의 활성 부분 jevida(강성욱) 2016.09.27 1048
1759 SQL Server 트랜잭션 로그 아키텍처(2/4) – 트랜잭션 로그 물리 아키텍처 jevida(강성욱) 2016.09.27 1096
1758 SQL Server 트랜잭션 로그 아키텍처(1/4) – 트랜잭션 로그 논리 아키텍처 jevida(강성욱) 2016.09.27 1257
1757 파일 및 파일 그룹 아키텍처 jevida(강성욱) 2016.09.27 801
1756 SQL Server 페이지 및 익스텐트 아키텍처(4/4) – 수정된 익스텐트 추적 jevida(강성욱) 2016.09.27 1130
1755 SQL Server 페이지 및 익스텐트 아키텍처(3/4) – 개체에서 사용하는 공간 관리 jevida(강성욱) 2016.09.27 975
1754 SQL Server 페이지 및 익스텐트 아키텍처(2/4) – 익스텐트 할당 및 빈공간 관리 jevida(강성욱) 2016.09.27 1504





XE Login