인덱싱된 뷰 사용 시 이점

인덱싱된 뷰를 구현하기 앞서 먼저 데이터베이스 워크로드를 분석하고 다양한 도구(SQL Profiler 등과 같은)와 쿼리에 대한 지식을 토대로 인덱싱된 뷰가 효과적인 쿼리를 구별해야 합니다. 인덱싱된 뷰는 빈번하게 실행되는 집계 및 조인 작업에 가장 적합합니다. 질문되는 빈도에 관계없이 답변에 많은 시간이 소요되고 신속한 답변이 무엇보다 중요한 쿼리라면 인덱싱된 뷰를 사용하는 것이 좋습니다. 예를 들어 일부 개발자들은 중역들이 매월 말 실행하는 보고서에서 쿼리에 대한 답변을 사전 계산하여 저장한 인덱싱된 뷰를 만드는 것이 유용하다는 사실을 발견했습니다.

모든 쿼리에서 인덱싱된 뷰가 유용한 것은 아닙니다. 일반 인덱스와 마찬가지로 인덱싱된 뷰를 사용하지 않는다면 개선 효과를 기대할 수 없습니다. 이 경우 성능 향상을 실현할 수 없을 뿐만 아니라 디스크 공간, 유지 관리 및 최적화로 인한 추가 비용이 발생할 수 있습니다. 그러나 인덱싱된 뷰를 사용하면 데이터 액세스가 훨씬 개선(수 배 이상)됩니다. 그 이유는 쿼리 최적화 프로그램이 인덱싱된 뷰에 저장되어 있는 사전 계산된 결과값을 사용하여 쿼리 실행 비용을 크게 줄일 수 있기 때문입니다.

쿼리 최적화 프로그램은 일정 수준 이상의 비용을 발생시키는 쿼리에 대해서만 인덱싱된 뷰를 고려하기 합니다. 따라서 인덱싱된 뷰를 사용하여 실현하는 비용 절감 효과보다 쿼리 최적화 동안 다양한 인덱싱된 뷰를 일치시키는데 더 많은 비용이 발생하는 상황을 피할수 있습니다. 인 덱싱된 뷰는 비용이 1 이하인 쿼리에서는 거의 사용되지 않습니다.

인덱싱된 뷰 구현으로 개선 효과를 거둘 수 있는 응용 프로그램은 다음과 같습니다.

• 의사결정 지원 워크로드
• 데이터 마트
• 데이터 웨어하우스
• OLAP 스토어 및 소스
• 데이터 마이닝 워크로드

쿼리 유형과 패턴 측면에서 다음과 같은 응용 프로그램들이 개선 효과를 거둘 수 있을 것입니다.

대형 테이블의 조인 및 집계

• 반복 패턴의 쿼리
• 동일 또는 중첩된 열 세트의 반복 집계
• 동일 키상에서 동일 테이블의 반복 조인
• 위 항목 모두

반대로 쓰기 작업이 많은 OLTP 시스템이나 업데이트가 잦은 데이터베이스 응용 프로그램은 뷰와 기본 테이블을 업데이트하는 것과 관련한 유지 관리 비용이 증가하기 때문에 인덱싱된 뷰를 사용하는데 따른 개선 효과를 거둘 수 없습니다.

쿼리 최적화 프로그램의 인덱싱된 뷰 사용 방법

SQL Server 쿼리 최적화 프로그램은 주어진 쿼리 실행을 위해 언제 인덱싱된 뷰를 사용할 수 있는지를 자동으로 결정합니다. 최적화 프로그램 에 대한 쿼리에서 직접 뷰를 참조할 필요 없이 쿼리 실행 계획에서 사용할 수 있습니다. 따라서 기존 응용 프로그램을 전혀 변경하지 않고도 윳싱된 뷰를 활용할 수 있으며 인덱싱된 뷰만 만들면 됩니다.

최적화 프로그램 고려 사항

쿼리 최적화 프로그램은 몇 가지 조건을 고려하여 인덱싱된 뷰가 전체 쿼리를 처리할 것인지, 아니면 쿼리 일부만 처리할 것인지를 결정합니다.
쿼리의 단일 FROM 구문에 해당하는 이들 조건은 다음과 같이 구성됩니다.

• 쿼리 FROM 구문의 테이블은 인덱싱된 뷰 FROM 구문에 있는 테이블의 상위 집합이어야 합니다.
• 쿼리의 조인 조건은 뷰 조인 조건의 상위 집합이어야 합니다.
• 쿼리의 집계 행은 뷰 집계 행의 상위 집합에서 추론할 수 있어야 합니다.
• 선택 쿼리 목록의 모든 표현식은 선택 뷰 목록 또는 뷰 정의에 포함되지 않은 테이블에서 추론할 수 있어야 합니다.
• 해당 열의 상위 집합이 다른 것과 일치하는 경우, 하나의 조건자가 다른 것을 포함할 수 있습니다. 예를 들어,“ T.a=10”은“T.a=10 및 T.b=20”을 포함합니다. 모든 조건자는 스스로를 포함합니다. 한 테이블의 값을 제한하는 뷰의 조건자 부분은 동일 테이블을 제한하는 쿼리 의 조건자 부분을 포함해야 합니다. 뿐만 아니라 SQL Server가 확인할 수 있는 방식으로 수행되어야 합니다.
• 뷰 정의의 테이블에 속한 쿼리 검색 조건자의 모든 열은 뷰 정의 내에 다음 중 1개 이상의 형태로 나타나야 합니다.
  a. GROUP BY 목록
  b. 선택 뷰 목록(GROUP BY가 없는 경우)
  c. 뷰 정의와 동일하거나 동급의 조건자
  (1) 과 (2)의 경우, SQL Server는 뷰의 행을 더욱 엄격하게 제한하기 위해 뷰의 행에 쿼리 조건자를 적용할 수 있습니다. (3)은 열에서 필터링이 필요하지 않은 특별한 경우이기 때문에 열을 뷰 내에 표시할 필요가 없습니다.

쿼리에 1개 이상의 FROM 구문(하위 쿼리, 추론된 테이블, UNION)이 포함되어 있으면 최적화 프로그램이 여러 개의 인덱싱된 뷰를 선택해서 쿼리를 처리하고 이를 서로 다른 FROM 구문에 적용할 수 있습니다.

이러한 상황을 보여주는 쿼리의 예제가 본 자료의 마지막 쿼리 실행 계획에서 사용할 인덱스를 결정하도록 하는 것이 가장 좋은 방법입니다.

NOEXPAND 뷰 힌트 사용

SQL Server가 이름으로 뷰를 참조한 쿼리를 처리하면 일반적으로 기본 테이블만 참조할 때까지 뷰의 정의를 확장합니다. 이러한 프로세스를 뷰 확장(view expansion)이라고 부르며 매크로 확장의 한 형식이라고 할 수 있습니다.

NOEXPAND 뷰 힌트는 쿼리 최적화 프로그램이 클러스터된 인덱스를 통해 일반 테이블과 마찬가지로 뷰를 처리하도록 합니다. 이는 뷰 확장을 차단합니다. FROM 구문에서 인덱싱된 뷰가 직접 참조되는 경우에만 NOEXPAND 힌트를 적용할 수 있습니다. 예를 들면 다음과 같습니다.

SELECT Column1, Column2, ... FROM Table1, View1 WITH (NOEXPAND) WHERE ...

기본 테이블에서 데이터 대신 뷰 자체를 읽어 SQL Server가 쿼리를 처리하도록 하려면 NOEXPAND를 사용하십시오. 뷰 사용을 선호하며 몇가지 이유로 SQL Server가 기본 테이블에 대한 쿼리를 처리하는 쿼리 계획을 선택한 경우라면 NOEXPAND 사용을 고려하십시오. SQL Server가 인덱싱된 뷰에 대한 쿼리를 직접 처리하도록 하려면 Developer 및 Enterprise Edition을 제외한 모든 버전의 SQL Server에서 NOEXPAND를 사용해야 합니다. SQL Server Management Studio 도구인 Display Estimated Execution Plan 기능을 사용하면 명령에 대해 SQL Server가 선택한 계획을 그래픽으로 볼 수 있습니다. 그렇지 않고 SHOWPLAN_ALL, SHOWPLAN_TEXT, SHOWPLAN_XML을 사용하면 그래픽 이외의 형태로 볼 수 있습니다. 다양한 버전의 SHOWPLAN에 대한 논의는 SQL Sever Books Online을 참조하십시오.

EXPAND VIEWS 쿼리 힌트 사용

이름으로 뷰를 참조하는 쿼리를 처리할 때 뷰 참조에 NOEXPAND 힌트를 추가하지 않는 한 SQL Server는 항상 뷰를 확장합니다. 또한 쿼리의 마지막 지정하지 않는 한 계속해서 인덱싱된 뷰를 확장된 쿼리에 일치시키려고도 합니다. 예를 들어, 데이터베이스에 인덱싱된 뷰인 View1이 있다고 가정해 봅시다. 다음 쿼리에서 View1은 논리적 정의(CREATE VIEW 명령)에 따라 확장되고 EXPAND VIEWS 옵션은 View1에 대한 인덱싱된 뷰가 쿼리 해결을 위한 계획에서 사용되는 것을 막습니다.

SELECT Column1, Column2, ... FROM Table1, View1 WHERE ...

OPTION (EXPAND VIEWS)

인덱싱된 뷰에 액세스하는 대신 쿼리가 참조한 기본 테이블에서 데이터에 직접 액세스함으로써 SQL Server가 쿼리를 처리하도록 하려면 EXPAND VIEWS를 사용하십시오. EXPAND 뷰는 때때로 인덱싱된 뷰에서 나타날 수 있는 잠금 경합(lock contention)을 제거하는 데 도움이 될 수 있습니다. NOEXPAND와 EXPAND VIEWS 모두 응용 프로그램을 테스트할 때 인덱싱된 뷰의 사용 여부에 따라 성능을 평가할 수 있도록 도와줍니다.