SQL Server 2005의 분할된 테이블 및 인덱스
Kimberly L. Tripp
SQLskills.com 설립자
적용 대상
SQL Server 2005
요약 : SQL Server 2005의 테이블 기반 분할 기능은 분할된 테이블을 간단하게 만들고 유지 관리할 수 있도록 하는 융통성 및 성능을 제공합니다. 논리적이고 수동적인 테이블 분할 작업에서 최신 분할 기능에 이르기까지 여러 기능들의 발전 사항을 살펴보고, SQL Server 2005를 사용하여 분할된 테이블을 디자인, 구현 및 유지 관리하는 이유, 시기 및 방법에 대해 알아봅니다.
백서 정보 이 문서에서 설명하는 기능 및 계획은 다음 버전의 SQL Server에 대한 현재 방침으로서, 이 제품의 사양이 아니며 변경될 수 있습니다. 이러한 기능이 최종 제품 릴리스에 포함된다는 묵시적 또는 기타 보장은 없습니다(이 기사에는 영문 페이지 링크가 포함되어 있습니다).
이 문서에서는 일부 기능의 경우 독자가 SQL Server 2000 기능 및 서비스에 익숙하다고 가정하고 설명합니다. 배경 정보에 대해서는 SQL Server 웹 사이트 또는 SQL Server 2000 Resource Kit를 참조하십시오.
이는 제품 사양이 아닙니다.
관련 코드 샘플 SQL2005PartitioningScripts.exe.gif "ms345146.tous(ko-kr,SQL.90).gif")
를 다운로드하십시오.
목차
분할을 사용하는 이유
분할의 역사
SQL Server 7.0 이전 릴리스에서의 개체 분할
SQL Server 7.0의 분할된 뷰
SQL Server 2000의 분할된 뷰
SQL Server 2005의 분할된 테이블
정의 및 용어
범위 분할
분할 키 정의
인덱스 분할
분할의 특수 조건: 분할(split), 병합 및 전환
분할된 테이블을 만드는 단계
개체 분할 여부 결정
분할 키 및 분할 수 결정
다중 파일 그룹 사용 여부 결정
파일 그룹 만들기
범위 분할을 위한 분할 함수 만들기
분할 스키마 만들기
분할된 테이블 만들기
인덱스 만들기: 분할 여부 지정
전체 통합: 사례 연구
범위 분할: 판매 데이터
분할된 테이블 조인
슬라이딩 윈도우(Sliding-Window) 시나리오
목록 분할: 지역 데이터
요약
이 백서의 스크립트
분할을 사용하는 이유
분할은 무엇이며, 분할을 사용하는 이유는 무엇일까요 간단하게 답하자면, 대형 테이블 및 다양한 액세스 패턴을 가진 테이블의 확장성 및 관리 용이성을 향상시키기 위해서입니다. 일반적으로 고객이나 판매 등 엔터티에 대한 정보를 저장하기 위해 테이블을 만들며, 각 테이블에는 해당 엔터티만을 설명하는 특성이 있습니다. 각 엔터티마다 하나의 테이블을 만들면 가장 쉽게 디자인하고 이해할 수 있지만, 이러한 테이블은 성능, 확장성 및 관리 용이성에 대해 최적화되어 있지 않습니다. 특히 테이블의 크기가 커질수록 더욱 그러합니다.
큰 테이블을 구성하는 것은 무엇일까요 VLDB(초대형 데이터베이스)의 크기는 수백 기가바이트, 심지어는 테라바이트에 달하기도 하지만 이 'VLDB'라는 용어가 반드시 데이터베이스 내의 개별 테이블 크기를 가리키는 것은 아닙니다. 대형 데이터베이스는 원하는 동작을 수행하지 않는 데이터베이스 또는 작업이나 유지 관리 비용이 미리 정의한 유지 관리 또는 예산 요구 사항을 초과하는 데이터베이스를 말합니다. 이러한 요구 사항은 테이블에도 적용됩니다. 다른 사용자의 작업 또는 유지 관리 작업으로 인해 가용성이 제한되는 경우 테이블은 대형으로 간주됩니다. 예를 들어, 성능이 크게 저하되거나 매일, 매주 또는 매달 두 시간의 유지 관리 시간 동안 테이블에 액세스할 수 없는 경우 판매 테이블은 대형으로 간주됩니다. 일부 경우 주기적인 가동 중지 시간은 허용되지만, 이는 보다 뛰어난 디자인 및 분할 구현을 통해 피하거나 최소화할 수 있습니다. VLDB라는 용어는 데이터베이스에만 적용되는 반면, 분할에 있어서는 테이블 크기에 더 유의해야 합니다.
크기 외에도, 테이블 내 여러 행 집합이 서로 다른 사용 패턴을 사용하는 경우 액세스 패턴이 다양한 테이블 또한 성능 및 가용성 면에 영향을 줄 수 있습니다. 사용 패턴이 항상 여러 가지인 것은 아니지만(분할의 요구 사항이 아님), 사용 패턴이 다양한 경우 분할을 수행하면 관리, 성능 및 가용성을 추가로 향상시킬 수 있습니다. 또한 판매 테이블 예제를 사용할 수 있도록 당월의 데이터는 읽기/쓰기가 가능한 반면 보통 테이블의 대부분을 차지하는 전월의 데이터는 읽기 전용입니다. 이와 같이 데이터가 여러 가지로 사용되는 경우나 데이터가 테이블 안팎으로 이동할 때의 유지 관리 부담이 매우 큰 경우, 테이블이 사용자 요청에 응답하는 기능에 영향이 있을 수 있습니다. 그 결과 서버의 가용성 및 확장성이 모두 제한됩니다.
또한 대형 데이터 집합을 여러 가지 방식으로 사용하는 경우 빈번한 유지 관리 작업이 정적 데이터에서 수행됩니다. 이로 인해 성능 문제, 차단 문제, 백업(공간, 시간 및 작업 부담) 등의 문제가 초래될 수 있을 뿐 아니라 서버의 전체 확장성에도 부정적인 영향을 줍니다.
분할이 이 문제를 어떻게 해결할 수 있을까요 테이블과 인덱스가 매우 커지면 분할 기능을 통해 데이터를 크기가 작고 관리가 보다 용이한 섹션으로 분할할 수 있습니다. 이 백서에서는 대형 행 그룹이 여러 개별 분할에 저장되는 수평 분할을 주로 다룹니다. 분할된 집합 정의를 필요에 따라 사용자 지정, 정의 및 관리할 수 있습니다. Microsoft SQL Server 2005에서는 정의된 범위 또는 목록을 사용하여 특정 데이터 사용 패턴에 따라 테이블을 분할할 수 있습니다. SQL Server 2005에는 또한 새 테이블 및 인덱스 구조에 따라 디자인된 기능이 추가되어 있기 때문에 분할된 테이블 및 인덱스의 장기적인 관리를 위한 다양한 옵션이 제공됩니다.
또한, 여러 개의 CPU가 있는 시스템에 대형 테이블이 있는 경우 해당 테이블을 분할하면 병렬 작업을 통해 성능을 향상시킬 수 있습니다. 개별 하위 집합에 대해 여러 작업을 병렬로 수행하면 수백만 개의 행과 같이 매우 큰 데이터 집합에 걸쳐 수행하는 대규모 작업 성능을 향상시킬 수 있습니다. 분할로 인한 성능 향상의 예로는 이전 릴리스에서의 집계를 들 수 있습니다. 예를 들어, 단일 대형 테이블을 집계하는 대신 SQL Server는 분할에서 독립적으로 작업을 수행한 다음에 집계를 집계할 수 있습니다. SQL Server 2005에서는 대형 데이터 집합을 조인하는 쿼리가 분할의 이점을 직접적으로 활용할 수 있습니다. SQL Server 2000에서도 하위 집합에서의 병렬 조인 작업을 지원했지만, 이 경우 하위 집합을 즉석에서 만들어야 했습니다. SQL Server 2005에서 동일한 분할 키 및 분할 함수로 분할되는 관련 테이블(예: Order 및 OrderDetails 테이블)은 '맞춰진다'고 합니다. 최적화 프로그램에서 두 개의 분할 및 맞춰진 테이블이 조인되었음을 감지하면 SQL Server 2005는 같은 분할에 있는 데이터를 먼저 조인한 다음 결과를 결합할 수 있습니다. 이로 인해 SQL Server 2005는 여러 개의 CPU가 있는 컴퓨터를 보다 효율적으로 사용할 수 있습니다.
분할의 역사
분할이라는 개념은 SQL Server에서는 새로운 것이 아닙니다. 사실 SQL Server 제품의 모든 릴리스에서 여러 가지 형태의 분할이 가능했습니다. 그러나 분할 스키마를 만들고 유지 관리하기 위해 특수하게 디자인된 기능이 없었기 때문에 분할은 번거로웠으며 활용도가 낮았습니다. 또한 사용자 및 개발자는 보다 복잡한 데이터베이스 디자인으로 인해 스키마를 잘못 이해했기 때문에 이점이 줄어들었습니다. 그러나 이 개념은 기본적으로 상당한 성능상의 이점을 제공하므로 SQL Server 7.0에서는 분할된 뷰를 통해 분할의 형태를 사용 가능하도록 함으로써 이 기능을 향상시켰습니다. 그리고 이제 SQL Server 2005는 분할된 테이블을 통해 대형 데이터 집합을 분할하기 위한 매우 뛰어난 고급 기능을 제공합니다.
SQL Server 7.0 이전 릴리스에서의 개체 분할
SQL Server 6.5 이전 버전에서 분할은 디자인의 일부여야 했으며 모든 데이터 액세스 코딩 및 쿼리 방법에서 기본적으로 제공되어야 했습니다. 여러 개의 테이블을 만든 다음 저장된 프로시저, 뷰 또는 클라이언트 응용 프로그램을 통해 올바른 테이블에 대한 액세스를 관리함으로써 일부 작업에 대한 성능을 향상시킬 수는 있었지만, 이렇게 하면 디자인이 복잡해지는 단점이 있었습니다. 각 사용자 및 개발자는 올바른 테이블을 파악하고 있어야 하며 제대로 참조해야 했습니다. 각 분할은 별도로 만들어져 관리되었으며 뷰는 액세스를 단순화하는 데 사용되었습니다. 그러나 이 솔루션을 사용해도 성능은 거의 향상되지 않았습니다. 사용자 및 응용 프로그램 액세스를 단순화하기 위해 UNION이 지정된 뷰가 있으면 쿼리 프로세서는 모든 원본 테이블에 액세스하여 결과 집합에 필요한 데이터를 결정해야 했습니다. 원본 테이블의 제한된 하위 집합만 필요한 경우 각 사용자 및 개발자는 적합한 테이블만을 참조하기 위해 디자인을 파악하고 있어야 했습니다.
SQL Server 7.0의 분할된 뷰
SQL Server 7.0 이전 릴리스에서 수동으로 분할을 만드는 작업의 문제점은 주로 성능과 관련되어 있었습니다. 뷰로 인해 응용 프로그램 디자인, 사용자 액세스 및 쿼리 작성은 단순화되었지만 성능은 향상되지 않았습니다. SQL Server 7.0이 릴리스되면서 뷰는 제약 조건과 결합되어 쿼리 최적화 프로그램이 쿼리 계획에서 관련이 없는 테이블을 제거(분할 제거)할 수 있도록 했으며 UNION이 지정된 뷰가 여러 테이블에 액세스할 때 전체 계획 부담을 크게 줄였습니다.
그림 1에서 YearlySales 뷰를 살펴보십시오. 모든 판매 정보를 하나의 대형 테이블에 포함하는 대신 12개의 개별 테이블(SalesJanuary, SalesFebruary 등)을 정의한 다음 각 분기의 뷰를 비롯하여 전체 연도의 뷰인 YearlySales를 정의할 수 있습니다.
.gif "ms345146.sql2k5partition_01(ko-kr,SQL.90).gif")
그림 1. SQL Server 7.0/2000 의 분할된 뷰
다음 쿼리로 YearlySales 뷰에 액세스하는 사용자는 SalesJanuary2003 테이블로만 이동됩니다.
SELECT ys.* FROM dbo.YearlySales AS ys WHERE ys.SalesDate = '20030113'
제약 조건이 트러스트되고 뷰에 대한 쿼리가 WHERE 절을 사용하여 분할 키(제약 조건이 정의된 열)를 기반으로 결과를 제한하는 한, SQL Server는 필요한 기본 테이블에만 액세스합니다. 트러스트된 제약 조건은 모든 데이터가 제약 조건에 의해 정의된 속성을 준수함을 SQL Server가 보장할 수 있는 제약 조건입니다. 제약 조건을 만들 때의 기본 동작은 해당 제약 조건에 WITH CHECK를 지정하는 것입니다. 이 설정에 의해 테이블에서 스키마 잠금이 수행되어 데이터를 제약 조건에 대해 확인할 수 있습니다. 확인 작업에 의해 기존 데이터의 유효성이 검사되면 제약 조건이 추가됩니다. 스키마 잠금이 제거된 후의 추가 삽입, 업데이트 및 삭제는 적용되는 제약 조건을 따라야 합니다. 이 프로시저를 사용하여 트러스트된 제약 조건을 만들면 개발자는 뷰를 사용하여 디자인을 훨씬 단순하게 만들 수 있습니다. 이때 원하는 테이블에 직접적으로 액세스하지 않아도 되며, 심지어 해당 테이블이 있는지조차 몰라도 됩니다. 트러스트된 제약 조건을 통해 SQL Server는 실행 계획에서 불필요한 테이블을 제거함으로써 성능을 향상시킵니다.
참고 제약 조건이 "트러스트되지 않은" 상태가 되는 경우는 다양합니다. 예를 들면 CHECK_CONSTRAINTS 인수를 지정하지 않고 대량 삽입 작업을 수행하거나 NOCHECK를 통해 제약 조건을 만드는 경우가 있습니다. 제약 조건이 트러스트되지 않는 상태가 되면 쿼리 프로세서는 요청된 데이터가 실제로 올바른 기본 테이블에 있는지를 확인할 수 있는 방법이 없으므로 모든 기본 테이블을 검색하는 방식으로 되돌아갑니다.
SQL Server 2000의 분할된 뷰
SQL Server 7.0은 디자인을 크게 단순화하고 SELECT 문에 대해서는 성능을 향상시켰지만, 데이터 수정 문에 대해서는 이점을 제공하지 않습니다. INSERT, UPDATE 및 DELETE 문이 기본 테이블에 대해서만 지원되었으며 테이블에 UNION을 지정하는 뷰에 대해 직접 지원되지는 않았기 때문입니다. SQL Server 2000에서는 데이터 수정 문 또한 SQL Server 7.0에서 도입되었던 분할된 뷰 기능의 이점을 활용합니다. 데이터 수정 문은 동일한 분할된 뷰 구조를 사용할 수 있으므로, SQL Server는 뷰를 통해 수정 사항을 적합한 기본 테이블로 이동시킬 수 있습니다. 이를 올바르게 구성하려면 분할 키 및 분할 키 만들기에 대해 추가 제한 사항이 필요합니다. 그러나 SELECT 쿼리뿐 아니라 수정 내용도 적절한 기본 테이블로 직접 전송된다는 점에서, 기본적인 원칙은 동일합니다. SQL Server 2000에서의 분할 작업 제한 사항, 설정, 구성 및 가장 효율적인 분할 방법에 대한 자세한 내용은 Microsoft SQL Server 2000 데이터 웨어하우스에서 파티션 사용을 참조하십시오.
SQL Server 2005의 분할된 테이블
SQL Server 7.0 및 SQL Server 2000의 향상된 기능으로 인해 분할된 뷰를 사용할 때의 성능이 크게 향상되기는 했지만 분할된 데이터 집합의 관리, 디자인 또는 개발은 단순화되지 않았습니다. 분할된 보기를 사용할 때는 뷰가 정의되는 기본 테이블을 모두 만들어 개별적으로 관리해야 합니다. 응용 프로그램 디자인이 보다 쉬워졌으며 사용자는 직접 액세스할 기본 테이블이 무엇인지 몰라도 된다는 이점이 있지만, 관리할 테이블이 많으며 데이터 무결성 제약 조건을 각 테이블에 대해 관리해야 하므로 관리가 복잡해집니다. 관리 문제점 때문에 분할된 뷰는 종종 데이터를 보관하거나 로드해야 했을 때만 개별 테이블에 사용되었습니다. 데이터가 읽기 전용 테이블로 이동되거나 읽기 전용 테이블에서 데이터가 삭제되면 작업은 매우 힘들어집니다. 시간이 길어지고, 로그 공간이 필요하며, 종종 차단이 생깁니다.
또한, 이전 버전의 분할 전략을 사용하는 경우 개발자가 개별 테이블 및 인덱스를 만든 다음 뷰를 통해 여기에 UNION을 지정해야 했으므로 최적화 프로그램은 각 분할에 대해 계획의 유효성을 검사하고 확인해야 했습니다(인덱스가 바뀌었을 수 있기 때문임). 그러므로 SQL Server 2000의 쿼리 최적화 시간은 처리된 분할 수가 증가함에 따라 길어지는 경우가 많았습니다.
SQL Server 2005에서 각 분할에는 동일한 인덱스가 정의되어 있습니다. 예를 들어, 당월의 OLTP(Online Transaction Processing) 데이터를 매달 말에 분석 테이블로 옮겨야 하는 시나리오를 가정해 봅시다. 읽기 전용 쿼리에 사용되는 분석 테이블은 클러스터된 인덱스 하나와 클러스터되지 않은 인덱스 두 개가 있는 단일 테이블입니다. 이미 인덱싱된 활성 단일 테이블로 1GB의 데이터를 대량 로드하면 테이블 및/또는 인덱스가 조각으로 분리되거나 잠겨 현재 사용자와의 차단이 발생합니다. 또한 각 행이 들어올 때 테이블 및 인덱스를 유지 관리해야 하므로 로딩 프로세스에 시간이 매우 많이 걸립니다. 대량 로드의 속도를 높이는 방법에는 여러 가지가 있지만 이러한 방법을 사용하면 다른 모든 사용자에 직접적으로 영향을 주게 되며 속도는 높아지는 대신 동시성이 떨어집니다.
이 데이터가 새로 만든(빈) 인덱싱되지 않은 [heap] 테이블에 격리된 경우 로드를 먼저 수행한 다음 데이터의 로드가 끝난 후에 인덱스를 만들 수 있습니다. 이 스키마를 사용하면 성능이 10배 이상 향상되는 경우가 많습니다. 사실 인덱싱되지 않은 테이블로 로드하면 여러 데이터 파일을 병렬로 로드하거나 동일한 파일(시작 및 끝 행 위치에 의해 정의됨)에서 여러 청크를 로드함으로써 복수 CPU의 이점을 활용할 수 있습니다. 두 작업 모두 병렬 처리의 이점을 활용하므로 성능을 더욱 향상시킬 수 있습니다.
SQL Server의 모든 릴리스에서 분할을 통해 이 작업을 보다 자세히 제어할 수 있으며, 모든 데이터를 한 위치에 배치할 필요가 없습니다. 그러나 이 경우 많은 개체를 만들고 관리해야 합니다. 기능적인 분할 전략은 동적으로 테이블을 만들거나 삭제하고 UNION이 지정된 뷰를 수정함으로써 이전 릴리스에서도 수행할 수 있었습니다. 그러나 SQL Server 2005에서 이 솔루션은 보다 향상되었습니다. 새로 채운 분할을 기존 분할 스키마의 추가 분할로 바꿔 넣고 이전 분할은 빼면 됩니다. 전체 과정에서 이 프로세스에 걸리는 시간은 아주 짧으며, 병렬 대량 로드 및 병렬 인덱스 만들기를 통해 작업을 보다 효과적으로 수행할 수 있습니다. 더욱 중요한 것은, 분할이 테이블 범위 외부에서 조정되므로 분할을 추가할 때까지 쿼리한 테이블에는 아무런 영향이 없다는 것입니다. 그 결과 일반적으로 분할을 추가하는 데 몇 초밖에 걸리지 않습니다.
성능은 데이터를 제거해야 할 때도 크게 향상됩니다. 한 데이터베이스에서 새 데이터(당월)가 마이그레이션되는 슬라이딩 윈도우(Sliding-Window) 데이터 집합을 필요로 하고 가장 오래된 데이터(전년도의 같은 달)는 제거하는 경우, 이 데이터 마이그레이션의 성능은 분할 사용을 통해 몇 배 향상시킬 수 있습니다. 분할을 사용하지 않을 때와 비교해 보면 그 차이는 더욱 큽니다. 모든 데이터가 하나의 테이블에 있는 경우 1GB의 이전 데이터를 삭제하려면 행 단위로 테이블을 조정해야 할 뿐 아니라 관련 인덱스도 조정해야 합니다. 데이터 삭제 프로세스에서는 매우 많은 양의 로그 작업이 발생하며, 삭제 작업 동안에는 로그를 자를 수 없으므로 훨씬 큰 로그가 필요할 수도 있습니다. 단, 삭제 작업은 단일 자동 커밋 트랜잭션이지만 가능한 경우에는 여러 번 삭제를 수행하여 트랜잭션 크기를 제어할 수는 있습니다. 그러나 분할을 사용하면 그와 같은 데이터를 제거할 때는 분할된 테이블에서 특정 분할을 제거(메타데이터 작업임)한 다음 독립 실행형 테이블을 삭제하거나 자르면 됩니다.
또한, 분할을 가장 효율적으로 디자인하는 방법을 모르면 분할과 함께 파일 그룹을 사용하는 것이 분할 구현에 이상적이라는 사실을 모를 수도 있습니다. 파일 그룹을 통해 서로 다른 실제 디스크에 개별 테이블을 배치할 수 있습니다. 단일 테이블이 파일 그룹을 사용해 여러 파일로 범위가 확장되는 경우에는 실제 데이터 위치를 예측할 수 없습니다. 병렬 처리를 사용할 수 없는 시스템에 대해 SQL Server는 파일 그룹을 통해 모든 디스크를 균등하게 사용하여 특정 데이터 배치의 중요도를 낮춤으로써 성능을 향상시킵니다.
참고 그림 2에는 단일 파일 그룹에 3개의 파일이 있습니다. Orders 및 OrderDetails의 두 테이블이 이 파일 그룹에 배치되었습니다. 테이블이 파일 그룹에 배치되면 파일 그룹 내의 개체에 대해 공간이 필요하므로, SQL Server는 각 파일로부터 범위 할당(8개의 8KB 페이지와 같은 64KB 청크)을 가져와 파일 그룹 내에 파일을 균등하게 채웁니다. Orders 및 OrderDetails 테이블이 만들어지면 파일 그룹은 비게 됩니다. 주문이 들어오면 Orders 테이블에 주문당 한 행씩 데이터가 입력되고 OrderDetails 테이블에는 라인 항목당 한 행씩 데이터가 입력됩니다. SQL Server는 File1에서 Orders 테이블에 범위를 할당한 다음 File2에서 OrderDetails 테이블에 다른 범위를 할당합니다. OrderDetails 테이블은 Orders 테이블보다 크기가 더 빨리 커지며, 다음 번의 할당은 공간이 필요한 다음 테이블에 대해 수행됩니다. OrderDetails의 크기가 커지면 File3에서 다음 범위를 할당받으며, SQL Server는 파일 그룹 내의 파일을 통해 이 작업을 "계속(round robin)"합니다. 그림 2에서 각 테이블의 해당 범위와 각 범위에서 해당 파일 그룹까지의 흐름을 확인할 수 있습니다. 범위는 공간이 필요할 때 할당되며 각각 흐름을 기반으로 번호가 지정됩니다.
.gif "ms345146.sql2k5partition_02(ko-kr,SQL.90).gif")
그림 2. 파일 그룹을 통한 비례 채우기
SQL Server는 해당 파일 그룹 내의 모든 개체에 대해 할당의 균형을 계속 조정합니다. 지정된 작업에 대해 더 많은 디스크를 사용하면 SQL Server는 보다 효과적으로 실행되는 반면, 관리 또는 유지 면에서는 더 많은 디스크를 사용하는 것이 좋지 않습니다. 특히 사용 패턴을 예측하기가 쉬우며 해당 패턴이 격리되어 있는 경우에는 더욱 그러합니다. 데이터는 디스크에서 특정 위치를 차지하지 않으므로 백업 작업 등의 유지 관리를 위해 해당 데이터를 격리할 수는 없습니다.
SQL Server 2005의 분할된 테이블을 사용하면 분할 키가 같은 모든 행을 특정 위치에 직접 배치하고 항상 그 위치로 이동하도록 함수 및 스키마를 통해 테이블을 디자인할 수 있습니다. 함수는 분할 경계를 정의할 뿐 아니라 첫 번째 값이 배치되어야 하는 분할도 정의합니다. LEFT 분할 함수의 경우 첫 번째 값은 첫 번째 분할에서 상한 역할을 합니다. 그리고 RIGHT 분할 함수의 경우 첫 번째 값은 두 번째 분할의 하한 역할을 합니다. 분할 함수는 이 백서의 뒷부분에서 좀 더 자세하게 다룹니다. 함수를 정의하고 나면 분할 스키마를 만들어 분할 함수를 기반으로 데이터베이스 내에서 해당 위치에 분할의 실제 매핑을 정의할 수 있습니다. 여러 테이블이 같은 함수를 사용할 때(같은 스키마를 사용하는 것은 아님) 분할 키가 같은 행은 비슷하게 그룹화됩니다. 이 개념을 맞춤이라고 합니다. 동일하거나 서로 다른 실제 디스크에 있는 여러 테이블로부터 같은 분할 키를 가지고 있는 행을 맞춤으로써 SQL Server는 최적화 프로그램이 선택하는 경우 각 테이블에서 필요한 데이터 그룹만을 사용할 수 있습니다. 맞춤을 수행하기 위해 두 개의 분할된 테이블이나 인덱스에는 각각의 분할 간에 일부 상응하는 부분이 있어야 합니다. 즉, 분할 열에 대해 동일한 분할 함수를 사용해야 합니다. 두 분할 함수는 다음과 같은 경우에 데이터를 맞추는 데 사용할 수 있습니다.
두 분할 함수가 모두 같은 수의 인수와 분할을 사용하는 경우
각 함수에서 사용되는 분할 키가 같은 유형인 경우(길이, 해당되는 경우 정밀도 및 배율, 데이터 정렬 포함)
경계 값이 동등한 경우(LEFT/RIGHT 경계 조건 포함)
참고 데이터를 맞추기 위해 두 분할 함수를 디자인한 경우에도 인덱스가 분할된 테이블로 동일한 열에서 분할되지 않는 경우에는 인덱스가 맞춰지지 않을 수도 있습니다.
병치(collocation)는 맞춤보다 강력한 형식이며, 맞춰진 두 개체가 분할 열에 있는 동등 조인(equi-join) 조건부로 조인됩니다. 이는 동등 조인(equi-join) 조건부가 있을 수 있는 쿼리, 하위 쿼리 또는 기타 유사한 구문에서 중요합니다. 분할 열에서 테이블을 조인하는 쿼리가 일반적으로 훨씬 더 빠르므로 병치(collocation)는 중요한 기능입니다. 그림 2에서 설명한 Orders 및 OrderDetails 테이블을 예로 들어 봅시다. 파일을 비례적으로 채우는 대신 3개의 파일 그룹에 매핑되는 분할 스키마를 만들 수 있습니다. Orders 및 OrderDetails 테이블을 정의할 때 같은 스키마를 사용하도록 정의합니다. 분할 키에 대해 같은 값을 가진 관련 데이터는 같은 파일 내에 배치되어 조인을 위해 필요한 데이터를 격리합니다. 여러 테이블의 관련 행이 동일한 방식으로 분할되면 SQL Server는 전체 테이블 또는 여러 분할(테이블이 서로 다른 분할 함수를 사용하는 경우)에서 일치하는 행을 검색할 필요 없이 분할을 조인할 수 있습니다. 이 경우 개체는 동일한 키를 사용하기 때문에 맞춰질 뿐만 아니라, 동일한 데이터가 같은 파일 내에 상주하므로 저장소 맞춤이 적용됩니다.
그림 3은 두 개의 개체가 동일한 분할 스키마를 사용할 수 있으며 분할 키가 같은 모든 데이터 행이 동일한 파일 그룹에 배치됨을 보여 줍니다. 관련 데이터가 맞춰지면 SQL Server 2005는 대형 집합에 대해 효율적으로 병렬 방식으로 작업할 수 있습니다. 예를 들어 Orders 및 OrderDetails 테이블의 모든 1월 판매 데이터는 첫 번째 파일 그룹에 있고, 2월 데이터는 두 번째 파일 그룹에 있는 식입니다.
.gif "ms345146.sql2k5partition_03(ko-kr,SQL.90).gif")
그림 3. 저장소 맞춤 테이블
SQL Server에서는 범위를 기반으로 분할을 수행할 수 있으며 테이블 및 인덱스가 같은 스키마를 사용하도록 디자인하여 보다 효과적으로 맞춤 작업을 할 수 있습니다. 뛰어난 디자인으로 인해 전체적인 성능은 크게 향상됩니다. 그러나 데이터 사용이 시간에 따라 변경되는 경우는 어떨까요 그리고, 추가 분할이 필요한 경우는 어떨까요 분할 추가, 제거 및 분할된 테이블 외부에서의 분할 관리에 있어서 관리 작업을 단순화하는 것이 SQL Server 2005의 주요 디자인 목표였습니다.
SQL Server 2005에서는 관리, 개발 및 사용을 염두에 두고 분할을 단순화했습니다. 다음은 일부 성능 및 관리 이점입니다.
성능 및 관리 목적을 위해 분할해야 하는 대형 테이블의 디자인 및 구현 단순화
나머지 분할의 데이터에 액세스할 때 방해를 최소화하며 기존에 분할된 테이블의 새 분할로 데이터 로드
비어 있는 새 테이블에 동일한 데이터를 로드할 때와 동일한 성능으로 기존에 분할된 테이블의 새 분할로 데이터 로드
테이블의 나머지 부분에 대한 액세스에 주는 영향을 최소화하며 분할된 테이블 일부를 보관 및/또는 제거
분할된 테이블에서 분할을 포함/제외하여 분할을 유지 관리할 수 있도록 함
여러 개의 관련 테이블에 대한 대규모 작업을 더욱 뛰어나게 확장 및 병렬 처리할 수 있도록 함
모든 분할에 대해 성능 향상
각 분할을 별도로 최적화할 필요가 없으므로 쿼리 최적화 시간 단축
정의 및 용어
SQL Server 2005에서 분할을 구현하려면 몇 가지 새로운 개념, 용어 및 구문에 익숙해야 합니다. 이러한 새 개념을 이해하기 위해 우선 만들고 배치하는 방법을 중심으로 테이블 구조를 살펴보겠습니다. 이전 릴리스에서 테이블은 항상 실제적이자 논리적인 개념이었지만, SQL Server 2005의 분할된 테이블 및 인덱스를 사용하면 다양한 테이블 저장 위치 및 방법 중에서 선택할 수 있습니다. SQL Server 2005에서는 테이블 및 인덱스를 이전 릴리스와 동일한 구문을 사용하여 만들 수 있습니다. 즉, DEFAULT 파일 그룹 또는 사용자 정의 파일 그룹에 배치되는 단일 테이블 형식 구조로 만들 수 있습니다. 또한 SQL Server 2005에서는 테이블 및 인덱스를 분할 스키마에 만들 수 있습니다. 분할 스키마는 개체를 하나 이상의 파일 그룹에 매핑합니다. 적절한 실제 위치로 이동되는 데이터를 결정하기 위해 분할 스키마는 분할 함수를 사용합니다. 분할 함수는 행 위치를 지정하는 데 사용될 알고리즘을 정의하며 스키마는 분할을 해당 실제 위치(파일 그룹)와 연결합니다. 다시 말해 테이블은 여전히 논리적 개념이지만 디스크에서 테이블의 실제 배치는 이전 릴리스와 크게 다릅니다. 이제는 테이블에 스키마가 포함될 수 있는 것입니다.
범위 분할
범위 분할은 사용자 지정 가능한 특정 데이터 범위에 의해 정의되는 테이블 분할입니다. 범위 분할 경계는 개발자가 선택하며 데이터 사용 패턴이 바뀌면 변경될 수 있습니다. 일반적으로 이러한 범위는 데이터를 기준으로 하거나 순서가 지정된 데이터 그룹화를 기준으로 합니다.
범위 분할은 주로 데이터 보관, 의사 결정 지원(지정된 달이나 분기와 같이 종종 특정 데이터 범위만이 필요한 경우) 및 데이터 사용이 행 수명에 따라 달라지는 결합된 OLTP 및 DSS(의사 결정 지원 시스템)에 사용됩니다. SQL Server 2005 분할된 테이블 및 인덱스의 가장 큰 이점은 특히 보관 및 유지 관리와 관련하여 매우 구체적인 데이터 범위를 조정할 수 있는 기능입니다. 범위 분할을 사용하면 이전 데이터를 매우 빠르게 보관 및 대체할 수 있습니다. 범위 분할은 보통 광범위한 데이터에 대한 의사 결정 지원을 위해 데이터에 액세스하는 경우에 가장 적합합니다. 이 경우 필요할 때 적절한 분할에만 액세스할 수 있어야 하므로 데이터의 위치가 구체적으로 지정되어 있어야 합니다. 또한 트랜잭션 데이터를 사용할 수 있으므로 데이터를 쉽고 빠르게 추가할 수 있습니다. 각 분할에 대해 경계 조건을 정의해야 하므로 범위 분할은 처음에는 정의하기가 조금 더 복잡합니다. 또한 스키마를 만들어 하나 이상의 파일 그룹에 각 분할을 매핑해야 합니다. 그러나 범위 분할은 보통 일관성 있는 패턴을 따르므로 일단 정의하고 나면 프로그래밍 방식으로 쉽게 유지 관리할 수 있습니다(그림 4 참조).
.gif "ms345146.sql2k5partition_04(ko-kr,SQL.90).gif")
그림 4. 12 개의 분할이 있는 범위 분할된 테이블
분할 키 정의
테이블 및 인덱스 분할 작업의 첫 단계는 분할에 키가 지정되는 데이터를 정의하는 것입니다. 분할 키는 테이블에 단일 열로 존재해야 하며 특정 조건을 만족해야 합니다. 분할 함수는 데이터의 논리적 구분이라고도 하는 키의 기준이 되는 데이터 형식을 정의합니다. 함수는 이 키는 정의하지만 디스크에서 데이터의 실제 배치는 정의하지 않습니다. 데이터 배치는 분할 스키마가 결정합니다. 즉, 스키마는 데이터를 특정 파일과 디스크에 매핑하는 하나 이상의 파일 그룹에 데이터를 매핑합니다. 스키마는 이 작업을 수행하기 위해 항상 함수를 사용합니다. 함수가 5개의 분할을 정의하면 스키마는 5개의 파일 그룹을 사용해야 합니다. 파일 그룹은 다르지 않아도 되지만 CPU와 디스크가 여러 개이면 성능이 보다 향상됩니다. 스키마를 테이블과 함께 사용할 때는 분할 함수의 인수로 사용되는 열을 정의해야 합니다.
범위 분할에서 데이터 집합은 논리적인 데이터 중심 경계로 구분됩니다. 사실 데이터 분할의 균형을 완전히 조정하지는 못할 수도 있습니다. 테이블이 범위라고도 하는 특정 분석 경계를 정의하는 패턴으로 사용될 때는 데이터 사용에 따라 범위 분할이 결정됩니다. 범위 함수의 분할 키는 열 하나만으로 구성될 수 있으며, 분할 함수는 데이터 무결성/제약 조건으로 인해 테이블 내에 해당 데이터가 존재하지 않는 경우에도 전체 도메인을 포함합니다. 다시 말해서, 경계는 각 분할에 대해 정의되지만 첫 번째 분할과 두 번째 분할에는 가장 왼쪽(가장 낮은 경계 조건보다 작은 값)의 행 및 가장 오른쪽(가장 큰 경계 조건보다 큰 값)의 행이 포함될 수 있습니다. 그러므로 값 도메인을 특정 데이터 집합으로 제한하려면 분할을 CHECK 제약 조건으로 결합해야 합니다. CHECK 제약 조건을 사용하여 비즈니스 규칙 및 데이터 무결성 제약 조건을 적용하면 데이터 집합을 무제한의 범위가 아닌 유한한 범위로 제한할 수 있습니다. 범위 분할은 유지 및 관리 작업에서 정기적으로 광범위한 데이터를 보관해야 하며 쿼리가 범위의 하위 집합 내에 있는 많은 양의 데이터에 액세스하는 경우에 적합합니다.
인덱스 분할
테이블의 데이터 집합을 분할할 수 있을 뿐 아니라 인덱스도 분할할 수 있습니다. 같은 함수를 사용하여 테이블 및 해당 인덱스를 분할하면 성능이 최적화되는 경우가 많습니다. 인덱스와 테이블이 같은 분할 함수와 열을 같은 순서로 사용하는 경우 테이블과 인덱스는 맞춰진 것입니다. 이미 분할되어 있는 테이블에서 인덱스가 만들어지면 SQL Server는 인덱스가 명시적으로 다르게 분할되어 있지 않는 한 자동으로 새 인덱스를 테이블의 분할 스키마에 맞춥니다. 테이블 및 해당 인덱스가 맞춰지면 모든 관련 데이터 및 인덱스는 같은 알고리즘으로 나누어지므로 SQL Server는 분할을 분할된 테이블 내외부로 보다 효과적으로 이동할 수 있습니다.
같은 분할 함수뿐 아니라 같은 분할 스키마를 사용하여 정의되는 테이블 및 인덱스는 저장소 맞춤이 적용된 것으로 간주됩니다. 저장소 맞춤의 한 가지 이점은 같은 경계 내의 모든 데이터가 같은 실제 디스크에 배치된다는 것입니다. 이 경우 백업은 특정 시간으로 격리할 수 있으며 전략은 빈도 및 백업 유형 면에서 데이터의 변동에 따라 달라질 수 있습니다. 같은 파일이나 파일 그룹의 테이블 및 인덱스가 조인 또는 집계되는 경우에는 성능이 더욱 향상됩니다. SQL Server는 분할 전체에 걸친 작업 병렬화의 이점을 활용합니다. 저장소 맞춤 및 복수 CPU의 경우 모든 필수 데이터가 같은 디스크에 있으므로 각 프로세서는 데이터 액세스의 충돌 없이 특정 파일이나 파일 그룹에서 직접 작업할 수 있습니다. 이로 인해 보다 많은 프로세스가 중단 없이 병렬로 실행될 수 있습니다.
자세한 내용은 SQL Server 온라인 설명서의 "분할된 인덱스에 대한 특별 지침"을 참조하십시오.
분할의 특수 조건: 분할(split), 병합 및 전환
분할된 테이블의 사용을 돕기 위해 분할 관리와 관련된 몇 가지 새 기능 및 개념이 추가되었습니다. 분할은 확장 가능한 대형 테이블에 사용되므로 분할 함수를 만들 때 선택한 분할 수는 시간에 따라 변경됩니다. ALTER TABLE 문과 새로운 분할(split) 옵션을 사용하여 다른 분할을 테이블에 추가할 수 있습니다. 분할이 분할(split)되면 데이터를 새 분할로 이동할 수 있지만, 성능을 유지하려면 행은 이동하지 않아야 합니다. 이 시나리오는 이 백서 뒷부분의 사례 연구에서 설명합니다.
반대로 분할을 제거하려면 해당 데이터를 제외한 다음 경계 지점을 병합합니다. 범위 분할의 경우 제거해야 하는 경계 지점을 제시함으로써 병합 요청을 하게 됩니다. 특정 기간의 데이터만이 필요하며 데이터가 정기적(예: 월 단위)으로 보관되는 경우에는 당월의 데이터를 사용할 수 있게 되면 하나의 데이터 분할(맨 첫달)을 보관하도록 할 수 있습니다. 예를 들어 1년 동안의 데이터를 사용할 수 있도록 하면 매달 말일에 당월의 데이터가 새로 들어가고 맨 첫달의 데이터는 빠지는 식으로 당월의 읽기/쓰기 OLTP와 전월의 읽기 전용 데이터를 구분할 수 있습니다. 다음 시나리오에 나타나 있듯이 프로세스를 가장 효율적으로 수행할 수 있도록 하는 특정 작업 흐름이 있습니다.
1년분의 읽기 전용 데이터를 사용 가능한 상태로 유지합니다. 테이블에는 현재 2003년 9월부터 2004년 8월까지의 데이터가 보관되어 있습니다. 당월인 2004년 9월의 데이터는 다른 데이터베이스에 있으며, OLTP 성능을 위해 최적화되어 있습니다. 테이블의 읽기 전용 버전에는 13개의 분할이 있습니다. 그 중 12개의 분할에는 2003년 9월부터 2004년 8월까지의 데이터가 포함되어 있으며, 마지막 분할 하나는 비어 있습니다. 범위 분할은 맨 왼쪽 및 맨 오른쪽을 비롯하여 전체 도메인을 항상 포함하므로 이 마지막 분할은 비어 있습니다. 그리고 슬라이딩 윈도우(Sliding-Window) 시나리오에서 데이터를 관리하려는 경우에는 새 데이터가 배치되는 빈 분할이 항상 분할(split)되도록 할 수 있습니다. LEFT 경계 지점으로 정의된 분할 함수에서 빈 분할은 논리적으로 가장 먼 RIGHT에 있습니다. 마지막에 분할을 비워 두면 들어오는 새 데이터를 위해 빈 분할을 분할(split)할 수 있으며, 다른 데이터 청크를 포함하기 위해 분할이 분할(split)될 때 마지막 분할의 행을 추가되는 새 파일 그룹으로 이동하지 않아도 됩니다. 마지막 분할에는 행이 없기 때문입니다. 이는 이 백서 뒷부분의 사례 연구에서 자세하게 설명할 다소 복잡한 개념입니다. 그러나 핵심적인 내용은 모든 데이터 추가 및 삭제 작업은 메타데이터 전용 작업이어야 한다는 것입니다. 메타데이터 전용 작업이 수행되도록 하기 위해 테이블에서 변경되는 부분을 전략적으로 관리할 수 있습니다. 이 분할이 비어 있도록 하려면 CHECK 제약 조건을 사용하여 기본 테이블에서 이 데이터를 제한합니다. 이 경우 OrderDate는 2003년 9월 1일 당일 또는 이후이거나 2004년 9월 1일 이전이어야 합니다. 마지막으로 정의한 경계 지점이 8월 31일 11:59:59.997인 경우(997이 붙는 이유는 이후 설명함) 분할 함수와 이 제약 조건이 조합되어 마지막 분할을 비어 있도록 유지합니다. 이러한 내용은 단지 개념일 뿐이지만, 분할(split) 및 병합은 ALTER PARTITION FUNCTION을 통해 처리되고 전환은 ALTER TABLE을 통해 처리된다는 것이 중요합니다.
.gif "ms345146.sql2k5partition_05_big(ko-kr,SQL.90).gif")
그림 5. 데이터 로드 / 보관 전의 범위 분할 경계
OLTP 데이터베이스에서 10월이 시작되면 9월의 데이터는 분할된 테이블로 옮겨져 분석에 사용됩니다. 테이블을 추가하고 제외하는 프로세스는 고속으로 이루어지며 준비 작업은 분할된 테이블 외부에서 수행할 수 있습니다. 이 시나리오는 이후 나올 사례 연구에서 자세하게 설명되지만 핵심적인 내용은 최종적으로는 분할된 테이블 내의 분할이 되는 "Staging 테이블"을 사용한다는 것입니다. 이 시나리오에 대한 자세한 정보는 이 백서 뒷부분의 사례 연구에서 설명합니다. 이 프로세스에서는 테이블의 분할을 같은 파일 그룹 내에 있는 분할되지 않은 테이블로 빼냅니다(그림 6). 분할되지 않은 테이블이 같은 파일 그룹 내에 이미 있으므로(있어야 작업이 성공적으로 수행됨) SQL Server는 이를 메타데이터 변경으로 수행되도록 할 수 있습니다. 이는 메타데이터 전용 변경이며, 삭제 작업을 실행하면 몇 시간이 걸리고 대형 테이블에서 차단이 생길 수 있는 반면 변경 작업은 단 몇 초 만에 수행할 수 있습니다. 이 분할을 제외한 후에도 분할은 여전히 13개입니다. 가장 오래된 첫 번째 분할은 이제 비어 있는 상태이며, 가장 최신 상태이고 역시 비어 있는 마지막 분할은 분할(split)해야 합니다.
.gif "ms345146.sql2k5partition_06(ko-kr,SQL.90).gif")
그림 6. 분할 제외
가장 오래된 분할(2003년 9월)을 제거하려면 새로운 병합 옵션을 ALTER TABLE과 함께 사용합니다(그림 7 참고). 경계 지점을 병합하면 경계 지점이 효과적으로 제거되므로 분할도 제거할 수 있습니다. 그러면 데이터가 로드되는 분할 수가 n-1(이 경우 12)로 줄어듭니다. 병합할 경계 지점에 데이터 행이 없어 이동해야 하는 행이 없는 경우에는 분할을 병합하는 작업은 고속으로 진행됩니다. 이 경우 첫 번째 분할이 비어 있으므로 첫 번째 분할에서 두 번째 분할로 옮겨야 하는 행은 없습니다. 첫 번째 분할이 비어 있지 않을 때 경계 지점을 병합하면 행을 첫 번째 분할에서 두 번째 분할로 이동해야 하므로 작업 부담이 매우 커질 수 있습니다. 그러나 대부분의 슬라이딩 윈도우(Sliding-Window) 시나리오에서는 빈 분할을 활성 분할과 병합하여 행을 이동하지 않으므로 이를 피할 수 있습니다.
.gif "ms345146.sql2k5partition_07_big(ko-kr,SQL.90).gif")
그림 7. 분할 병합
마지막으로 새 테이블을 분할된 테이블에 추가해 넣어야 합니다. 이를 메타데이터 변경으로 수행하려면 분할된 테이블 경계 외부에서 새 테이블에 대해 인덱스 로드 및 빌드를 수행해야 합니다. 분할을 추가하려면 먼저 가장 최신 상태의 비어 있는 맨 마지막 범위를 두 개의 분할로 분할(split)합니다. 또한 새 범위를 허용하도록 테이블의 제약 조건을 업데이트해야 합니다. 여기서도 분할된 테이블에는 13개의 분할이 있습니다. 슬라이딩 윈도우(Sliding-Window) 시나리오에서 LEFT 분할 함수를 사용한 마지막 분할은 항상 비어 있는 상태로 유지됩니다.
.gif "ms345146.sql2k5partition_08(ko-kr,SQL.90).gif")
그림 8. 분할 분할 (split) 하기
이제 새로 로드한 데이터를 12번째 분할(2004년 9월)에 추가해 넣을 수 있습니다.
.gif "ms345146.sql2k5partition_09(ko-kr,SQL.90).gif")
그림 9. 분할 추가
그러면 테이블은 다음과 같이 됩니다.
.gif "ms345146.sql2k5partition_10_big(ko-kr,SQL.90).gif")
그림 10. 데이터 로드 / 보관 후의 범위 분할 경계
한 번에 하나의 분할만 추가 또는 제거할 수 있으므로 둘 이상의 분할을 추가하거나 제거해야 하는 테이블은 다시 만들어야 합니다. 이와 같은 새 분할 구조로 변경하려면 먼저 새 분할된 테이블을 만든 다음 데이터를 새로 만든 테이블로 로드합니다. 이는 각 분할(split)에 대해 전체 테이블의 균형을 다시 조정하는 것보다 적절한 방법입니다. 이 프로세스는 새 분할 함수 및 새 분할 스키마를 사용하고 데이터를 새로 분할한 테이블로 이동하여 수행할 수 있습니다. 데이터를 이동하려면 먼저 INSERT 새 테이블SELECT 열 목록 FROM 이전 테이블을 사용하여 데이터를 복사한 다음 원래 테이블을 삭제합니다. 이 프로세스를 실행 중일 때 데이터가 손상되지 않도록 사용자가 수정 작업을 하지 못하도록 하십시오.
자세한 내용은 SQL Server 온라인 설명서의 "ALTER PARTITION FUNCTION" 및 "ALTER TABLE"을 참조하십시오.
분할된 테이블을 만드는 단계
이제 분할된 테이블의 중요성에 대해 이해했으므로 다음 섹션에서는 분할된 테이블 구현 프로세스 및 이 프로세스를 도와 주는 기능에 대해 자세히 설명합니다. 논리의 흐름은 다음과 같습니다.
.gif "ms345146.sql2k5partition_11_big(ko-kr,SQL.90).gif")
그림 11. 분할된 테이블 또는 인덱스를 만드는 단계
개체 분할 여부 결정
분할은 많은 이점을 제공하기도 하지만 한편으로는 개체를 구현할 때 관리 부담이 커지고 과정이 복잡해지므로 득보다는 실이 많을 수도 있습니다. 특히 작은 테이블이나 현재 성능 및 유지 관리 요구 사항을 충족하는 테이블은 분할하기를 원치 않을 수도 있습니다. 앞서 언급한 판매 시나리오에서는 분할을 사용하여 행과 데이터 이동 작업의 부담을 줄였습니다. 분할 구현 여부를 결정할 때는 현 상황에서 이러한 종류의 작업 부담이 있는지를 고려해야 합니다.
분할 키 및 분할 수 결정
대형 데이터 하위 집합에 대해 성능 및 관리 용이성을 향상시키려는 경우 정의된 액세스 패턴이 있으면 범위 분할을 통해 데이터 경합을 줄일 수 있을 뿐만 아니라 읽기 전용 데이터에서 필요로 하지 않는 경우 유지 관리 작업도 줄일 수 있습니다. 분할 수를 결정하려면 데이터 내에 논리 그룹 및 패턴이 있는지를 평가해야 합니다. 이러한 정의된 하위 집합 중 한 번에 몇 개만을 사용하여 작업하는 경우가 많으면, 쿼리가 격리되어 적절한 데이터(특정 분할)로만 작업하도록 범위를 정의합니다.
자세한 내용은 SQL Server 온라인 설명서의 "분할된 테이블 및 인덱스 디자인"을 참조하십시오.
다중 파일 그룹 사용 여부 결정
성능과 유지 관리를 최적화하려면 파일 그룹을 사용하여 데이터를 구분해야 합니다. 파일 그룹 수는 부분적으로 하드웨어 리소스에 의해 결정됩니다. 일반적으로는 분할과 파일 그룹 수가 같은 것이 좋습니다. 이러한 파일 그룹은 서로 다른 디스크에 상주합니다. 그러나 이는 주로 분석이 전체 데이터 집합에 대해 수행되는 시스템에만 해당됩니다. CPU가 여러 개인 경우 SQL Server는 여러 분할을 병렬로 처리할 수 있으므로 대형의 복잡한 보고 및 분석을 처리하는 전체 시간을 크게 줄일 수 있습니다. 이 경우 병렬 처리가 가능할 뿐 아니라 분할된 테이블에서 분할을 추가 및 제외할 수도 있습니다.
파일 그룹 만들기
보다 나은 I/O 균형 조정을 위해 분할된 테이블을 여러 파일에 배치하려는 경우 적어도 하나의 파일 그룹을 만들어야 합니다. 파일 그룹은 하나 이상의 파일로 구성될 수 있으며 각 분할은 파일 그룹에 매핑되어야 합니다. 단일 파일 그룹이 여러 개의 분할에 사용될 수 있지만, 백업을 더욱 세밀하게 제어하는 등 데이터를 보다 효율적으로 관리하려면 관련이 있거나 논리적으로 그룹화된 데이터만이 동일한 파일 그룹에 상주하도록 분할된 테이블을 디자인해야 합니다. ALTER DATABASE를 사용하면 논리 파일 그룹 이름을 추가한 다음 파일을 추가할 수 있습니다. 2003Q3이라는 파일 그룹을 AdventureWorks 데이터베이스에 대해 만들려면 다음과 같은 방식으로 ALTER DATABASE를 사용합니다.
ALTER DATABASE AdventureWorks ADD FILEGROUP [2003Q3]
파일 그룹이 만들어지면 ALTER DATABASE를 사용하여 파일 그룹에 파일을 추가합니다.
ALTER DATABASE AdventureWorks ADD FILE (NAME = N'2003Q3', FILENAME = N'C:\AdventureWorks\2003Q3.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB) TO FILEGROUP [2003Q3]
CREATE TABLE의 ON 절에서 파일 그룹을 지정하여 파일에서 테이블을 만들 수 있습니다. 그러나 테이블을 분할해야 다중 파일 그룹에서 테이블을 만들 수 있습니다. 단일 파일 그룹에서 테이블을 만들려면 CREATE TABLE의 ON 절을 사용합니다. 분할된 테이블을 만들려면 먼저 분할에 대해 실질적으로 작동하는 메커니즘이 있어야 합니다. 분할 조건은 분할 함수의 형태로 테이블과는 논리적으로 구분됩니다. 이 분할 함수는 테이블과는 별개의 정의로 존재하며, 여러 개체가 분할 함수를 사용할 수 있으므로 이러한 실제적 구분이 도움이 됩니다. 그러므로 테이블을 분할하는 첫 번째 단계는 분할 함수를 만드는 것입니다.
범위 분할을 위한 분할 함수 만들기
범위 분할은 경계 조건으로 정의해야 합니다. 또한, 테이블이 CHECK 제약 조건을 통해 제한되어 있다 하더라도 범위 양쪽의 어떤 값도 지울 수 없습니다. 데이터를 주기적으로 테이블에 추가해 넣을 수 있도록 하려면 비어 있는 마지막 분할이 필요합니다.
범위 분할에서는 먼저 경계 지점을 정의합니다. 5개 분할에 대해 4개의 경계 지점 값을 정의하고 각 값이 첫 번째(LEFT) 분할보다 높은 경계인지 아니면 두 번째(RIGHT) 분할보다 낮은 경계인지를 지정합니다. LEFT 또는 RIGHT의 지정에 따라 하나의 분할은 항상 비어 있습니다. 분할에 명시적으로 정의된 경계 지점이 없기 때문입니다.
특히 분할 함수의 첫 번째 값 또는 경계 조건이 '20001001'이면 경계에 있는 분할의 값은 다음과 같습니다.
LEFT의 경우
첫 번째 분할의 모든 데이터는 '20001001'보다 작거나 같음
두 번째 분할의 모든 데이터는 '20001001'보다 큼
RIGHT의 경우
첫 번째 분할의 모든 데이터는 '20001001'보다 작음
두 번째 분할의 모든 데이터는 '20001001'보다 크거나 같음
범위 분할은 datetime 데이터에서 정의되므로 함축된 의미를 염두에 두어야 합니다. datetime 사용에는 특별한 의미가 함축되어 있습니다. 즉, 항상 날짜와 시간을 모두 사용한다는 것입니다. 시간에 대해 지정된 값이 없는 날짜는 오전 12시 정각의 "0"인 시간을 의미합니다. 이러한 데이터 형식에 LEFT가 사용되면 날짜가 10월 1일 오전 12시 정각인 데이터는 첫 번째 분할에 배치되고 10월의 나머지 데이터는 두 번째 분할에 배치됩니다. 논리적으로 시작 값에 RIGHT를 사용하고 끝 값에 LEFT를 사용하는 것이 가장 좋습니다. 이 세 절을 통해 논리적으로 동일한 분할 구조를 만듭니다.
RANGE LEFT FOR VALUES ('20000930 23:59:59.997',
'20001231 23:59:59.997',
'20010331 23:59:59.997',
'20010630 23:59:59.997')
-또는-
RANGE RIGHT FOR VALUES ('20001001 00:00:00.000',
'20010101 00:00:00.000',
'20010401 00:00:00.000',
'20010701 00:00:00.000')
-또는-
RANGE RIGHT FOR VALUES ('20001001', '20010101', '20010401', '20010701')
참고 datetime 데이터 형식을 사용하면 코드가 약간 더 복잡해지지만 경계 조건을 정확하게 설정해야 합니다. RIGHT를 사용하는 경우에는 기본 시간이 오전 12:00:00.000이므로 매우 단순합니다. LEFT의 경우에 복잡해지는 이유는 datetime 데이터 형식의 정밀도 때문입니다. 23:59:59.997을 선택해야 하는 이유는 datetime 데이터가 밀리초 단위의 정밀도를 보장하지 않기 때문입니다. 대신 datetime 데이터의 정밀도는 3.33밀리초 이내입니다. 23:59:59.999의 경우 정확하게 일치하는 시각은 사용할 수 없으며, 대신 값이 가장 가까운 시각인 다음날 오전 12:00:00.000으로 반올림됩니다. 이 반올림으로 인해 경계는 올바르게 정의되지 않습니다. datetime 데이터의 경우 특수하게 제공되는 밀리초 값을 사용할 때는 주의해야 합니다.
참고 분할 함수는 분할 함수 정의의 일부로 함수도 허용합니다 . DATEADD(ms , -3 , '20010101') 를 사용함으로써 '20001231 23:59:59.997'을 통한 명시적 시간 정의 작업을 대신할 수 있습니다.
자세한 내용은 SQL Server 온라인 설명서의 Transact-SQL 참조에서 "날짜 및 시간"을 참조하십시오.
Orders 데이터 중 1/4을 각각 하나의 분기를 나타내는 네 개의 활성 분할에 저장하고 나중에 사용하기 위해 다섯 번째 분할(분할된 테이블 내외부로 데이터를 이동하는 자리 표시자)을 만들려면 LEFT 분할 함수와 4개의 경계 조건을 사용합니다.
CREATE PARTITION FUNCTION OrderDateRangePFN(datetime)
AS
RANGE LEFT FOR VALUES ('20000930 23:59:59.997',
'20001231 23:59:59.997',
'20010331 23:59:59.997',
'20010630 23:59:59.997')
여기서 4개의 정의된 경계 지점이 5개의 분할을 만듭니다. 다음과 같이 집합을 검토하여 이 분할 함수에 의해 만들어진 데이터 집합을 확인하십시오.
경계 지점 '20000930 23:59:59.997'을 LEFT로 지정하는 경우(패턴 설정):
맨 왼쪽 분할에는 '20000930 23:59:59.997'보다 작거나 같은 모든 값이 포함됩니다.
경계 지점 '20001231 23:59:59.997'의 경우:
두 번째 분할에는 '20000930 23:59:59.997'보다 크고 '20001231 23:59:59.997'보다 작거나 같은 모든 값이 포함됩니다.
경계 지점 '20010331 23:59:59.997'의 경우:
세 번째 분할에는 '20001231 23:59:59.997'보다 크고 '20010331 23:59:59.997'보다 작거나 같은 모든 값이 포함됩니다.
경계 지점 '20010630 23:59:59.997'의 경우:
네 번째 분할에는 '20010331 23:59:59.997'보다 크고 '20010630 23:59:59.997'보다 작거나 같은 모든 값이 포함됩니다.
마지막으로 다섯 번째 분할에는 '20010630 23:59:59.997'보다 큰 모든 값이 포함됩니다.
분할 스키마 만들기
분할 함수를 만든 후에는 해당 함수를 분할 스키마와 연결하여 분할을 특정 파일 그룹에 지정할 수 있습니다. 분할 스키마를 정의할 때는 모든 분할에 대해 파일 그룹 이름을 지정해야 합니다. 동일한 파일 그룹에 여러 개의 분할이 있는 경우에도 마찬가지입니다. 앞서 만든 범위 분할(OrderDateRangePFN)의 경우에는 5개의 분할이 있으며 마지막의 빈 분할은 PRIMARY 파일 그룹에서 만들어집니다. 이 분할에는 데이터가 포함되지 않으므로 이를 위한 특수한 위치는 필요하지 않습니다.
CREATE PARTITION SCHEME OrderDatePScheme AS PARTITION OrderDateRangePFN TO ([2000Q3], [2000Q4], [2001Q1], [2001Q2], [PRIMARY])
참고 모든 분할이 같은 파일 그룹에 있으면 다음과 같이 보다 단순한 구문을 사용할 수 있습니다.
CREATE PARTITION SCHEME OrderDatePScheme AS PARTITION OrderDateRangePFN ALL TO ([PRIMARY])
분할된 테이블 만들기
분할 함수(논리 구조) 및 분할 스키마(실제 구조)를 정의하면 테이블을 만들어 이러한 함수와 스키마를 활용할 수 있습니다. 테이블은 사용할 스키마를 정의하며 스키마는 함수를 정의합니다. 이 세 가지를 모두 결합하려면 분할 함수를 적용할 열을 지정해야 합니다. 범위 분할은 항상 분할 함수 내에서 정의된 경계 조건의 데이터 형식과 일치하는 테이블의 정확히 한 열에 매핑됩니다. 또한 테이블이 음의 무한대에서 양의 무한대가 아니라 구체적으로 데이터 집합을 제한하는 경우 CHECK 제약 조건 또한 추가해야 합니다.
CREATE TABLE [dbo].[OrdersRange]
(
[PurchaseOrderID] [int] NOT NULL,
[EmployeeID] [int] NULL,
[VendorID] [int] NULL,
[TaxAmt] [money] NULL,
[Freight] [money] NULL,
[SubTotal] [money] NULL,
[Status] [tinyint] NOT NULL ,
[RevisionNumber] [tinyint] NULL ,
[ModifiedDate] [datetime] NULL ,
[ShipMethodID] [tinyint] NULL,
[ShipDate] [datetime] NOT NULL,
[OrderDate] [datetime] NOT NULL
CONSTRAINT OrdersRangeYear
CHECK ([OrderDate] >= '20030701'
AND [OrderDate] <= '20040630 11:59:59.997'),
[TotalDue] [money] NULL
)
ON OrderDatePScheme (OrderDate)
GO
인덱스 만들기: 분할 여부 지정
기본적으로 분할된 테이블에서 만들어진 인덱스 역시 동일한 분할 스키마 및 분할 열을 사용합니다. 이 사항이 적용되는 경우 인덱스는 테이블에 맞춰집니다. 이 작업은 필수적인 요구 사항은 아니지만 테이블과 해당 인덱스를 맞추면 관리 작업이 보다 쉬워집니다. 이는 슬라이딩 윈도우(Sliding-Window) 시나리오에서 더욱 그러합니다.
예를 들어 고유한 인덱스를 만들려면 분할 열은 키 열 중 하나여야 합니다. 이를 통해 적절한 분할을 확인할 수 있으므로 인덱스를 고유하게 만들 수 있습니다. 그러므로 한 열에서 테이블을 분할하고 다른 열에서 고유한 인덱스를 만들어야 하는 경우에는 테이블과 인덱스를 맞출 수 없습니다. 이 경우 인덱스는 고유한 열에서 분할할 수 있습니다. 이 열이 다중 열의 고유한 키인 경우에는 키 열 중 어떤 열이라도 가능합니다. 또는 인덱스를 분할하지 않을 수도 있습니다. 분할된 테이블에서 데이터를 추가 및 제외할 때는 이 인덱스를 삭제하고 다시 만들어야 합니다.
참고 테이블에 기존 데이터를 로드하고 즉시 인덱스를 추가하려는 경우, 분할되지 않고 인덱싱되지 않은 테이블로 로드한 후에 인덱스를 만들어 데이터를 분할하면 성능이 향상되는 경우가 많습니다. 분할 스키마에 클러스터된 인덱스를 정의하면 로드 후에 효과적으로 테이블을 분할할 수 있습니다. 이는 또한 기존 테이블을 분할하는 훌륭한 방법이기도 합니다. 분할되지 않은 테이블과 같은 테이블을 만들고 클러스터된 인덱스를 클러스터된 분할 인덱스로 만들려면 CREATE TABLE의 ON 절을 단일 파일 그룹 대상으로 대체합니다. 그런 다음 데이터가 로드된 후에 분할 스키마에 클러스터된 인덱스를 만듭니다.
전체 통합: 사례 연구
분할과 관련된 개념, 이점 및 코드 샘플을 살펴보았다면 해당 프로세스를 잘 이해하셨을 것입니다. 그러나 각 단계에서 사용 가능한 특정 설정 및 옵션이 있으며 일부 경우에는 다양한 조건을 충족해야 합니다. 이 섹션에서는 이러한 모든 것을 통합하는 방법을 알아봅니다.
범위 분할: 판매 데이터
판매 데이터는 용도가 다양합니다. 당월의 데이터는 거래 데이터이며 전월의 데이터는 보통 분석용으로 많이 쓰입니다. 분석은 보통 월별, 분기별 및/또는 연도별 데이터에 대해 이루어집니다. 여러 분석가가 동시에 다양한 대량의 데이터를 보고자 할 수 있으므로, 분석을 통해 이러한 작업을 보다 효율적으로 구분할 수 있습니다. 이 시나리오의 활성 데이터는 283개의 분기 위치로부터 들어오며 두 개의 표준 형식 ACSII 파일로 전달됩니다. 모든 파일은 매달 1일 오전 3시 이전에 중앙 파일 서버에 배치됩니다. 각 파일의 크기는 다양하지만 평균적으로 매달 약 86,000건의 판매(주문) 내역이 들어 있습니다. 각 주문은 평균 2.63 라인 항목이므로 OrderDetails 파일에는 평균 226,180개의 행이 있습니다. 매월 약 2천 5백만 개의 새 Orders 및 6천 4백만 개의 OrderDetails 행이 추가되며 기록 분석 서버는 분석용으로 2년에 해당하는 데이터를 활성 상태로 유지 관리합니다. 2년에 해당하는 데이터는 6억 개가 약간 못 되는 Orders 및 15억 개가 조금 넘는 OrderDetails 행입니다. 분석은 보통 같은 분기 내의 달 또는 전년의 같은 달과 비교함으로써 수행되므로 범위 분할이 사용됩니다. 각 범위의 경계는 월입니다.
그림 11에 설명되어 있는 단계를 사용하면 테이블은 OrderDate를 기준으로 범위 분할을 통해 분할됩니다. 이 새 서버의 요구 사항을 살펴보면 분석가들은 최대 연속 6개월간의 데이터 또는 최대 3개월간의 금년 및 전년(예: 2003년 1-3월 및 2004년 1-3월) 데이터를 집계 및 분석하려는 경향이 있습니다. 디스크 스트립을 최대화하고 대부분의 데이터 그룹화를 격리하기 위해 다중 파일 그룹이 같은 실제 디스크를 사용하지만, 파일 그룹은 디스크 경합을 줄이기 위해 6개월 단위로 오프셋됩니다. 최신 데이터는 2004년 10월분이며 283개 상점은 모두 최신 판매를 로컬에서 관리하고 있습니다. 서버는 2002년 10월부터 2004년 9월 말까지의 데이터를 보관합니다. 새로운 16중 프로세서 컴퓨터 및 SAN(Storage Area Network)을 활용하기 위해 매달의 데이터는 파일 그룹에 고유한 파일을 가지며 스트립된 미러(RAID 1+0) 디스크 집합에 상주합니다. 파일 그룹을 통한 논리 드라이브로의 실제 데이터 배치의 경우, 다음 다이어그램(그림 12)은 데이터가 월을 기준으로 상주하는 위치를 보여 줍니다.
.gif "ms345146.sql2k5partition_12_big(ko-kr,SQL.90).gif")
그림 12. 분할된 테이블 Orders
각각의 12개 논리 드라이브는 RAID 1+0으로 구성되어 있으므로 Orders 및 OrderDetails 데이터에 필요한 총 디스크 수는 48개입니다. SAN은 78개의 디스크를 지원하므로 나머지 30개는 트랜잭션 로그, TempDB, 시스템 데이터베이스 및 Customers(9백만 개)와 Products(386,750개 행) 등의 기타 작은 테이블에 사용됩니다. Orders 및 OrderDetails 테이블은 동일한 경계 조건을 사용하고 디스크에서 같은 위치에 배치되며 동일한 분할 스키마를 사용합니다. 그 결과(그림 13에 있는 두 논리 드라이브 [드라이브 E:\ 및 F:\]만 고려함) Orders 및 OrderDetails의 데이터는 같은 월에 대해 같은 디스크에 상주합니다.
.gif "ms345146.sql2k5partition_13a_big(ko-kr,SQL.90).gif")
그림 13. 디스크 배열의 범위 배치에 대한 범위 분할
이는 복잡해 보이지만 매우 만들기 쉽습니다. 분할된 테이블 디자인에 있어서 가장 힘든 부분은 여러 소스로부터 데이터를 전달받는 것입니다. 즉, 283개의 상점에는 표준 전달 메커니즘이 있어야 합니다. 그러나 중앙 서버에는 정의할 Orders 테이블 및 OrderDetails 테이블이 하나씩밖에 없습니다. 두 테이블을 모두 분할된 테이블로 만들려면 먼저 분할 함수와 분할 스키마를 만들어야 합니다. 분할 스키마는 디스크에 대한 실제 분할 위치를 정의하므로 파일 그룹도 있어야 합니다. 이 테이블에서는 파일 그룹이 필요하므로 다음 단계는 파일 그룹을 만드는 것입니다. 모든 파일 그룹의 구문은 다음과 동일하지만 24개 파일 그룹을 모두 만들어야 합니다. 24개의 파일 그룹을 모두 만들기 위한 전체 스크립트는 RangeCaseStudyFilegroups.sql 스크립트를 참조하십시오.
참고: 적절한 드라이브 문자가 있어야 이 스크립트를 실행할 수 있습니다. 그러나 스크립트에는 단순화된 테스트를 위해 수정할 수 있는 "설정" 테이블이 들어 있습니다. 드라이브 문자/위치를 단일 드라이브로 변경하여 구문을 테스트하고 익힐 수 있습니다. 파일 크기는 GB가 아닌 MB로 지정해야 하며, 사용 가능한 디스크 공간에 따라서는 초기 크기를 보다 작게 지정해야 합니다.
SalesDB 데이터베이스에 대해 24개의 파일 및 파일 그룹이 만들어집니다. 각각은 위치, 파일 이름 및 파일 그룹 이름을 제외하고는 동일한 구문을 사용합니다.
ALTER DATABASE SalesDB ADD FILE (NAME = N'SalesDBFG1File1', FILENAME = N'E:\SalesDB\SalesDBFG1File1.ndf', SIZE = 20GB, MAXSIZE = 35GB, FILEGROWTH = 5GB) TO FILEGROUP [FG1] GO
24개 파일 및 파일 그룹을 모두 만들었다면 분할 함수 및 분할 스키마를 정의할 수 있습니다. 파일 및 파일 그룹을 확인하려면 각각 sp_helpfile 및 sp_helpfilegroup을 사용하십시오.
분할 함수는 OrderDate 열에서 정의됩니다. 사용되는 데이터 형식은 datetime이며 두 테이블을 모두 이 값으로 분할하려면 두 테이블은 OrderDate를 저장해야 합니다. 두 테이블이 같은 키 값에서 분할되는 경우 사실상 분할 키 값은 중복되는 정보입니다. 그러나 이는 맞춤 기능을 활용하는 데 필요하며 대부분의 경우 이 값은 비교적 좁은 열(datetime 날짜 형식은 8바이트)입니다. 이 백서의 앞부분인 "범위 분할을 위한 분할 함수 만들기" 에서 설명한 것과 같이 함수는 첫 번째 경계 조건이 LEFT(첫 번째) 분할인 범위 분할 함수입니다.
CREATE PARTITION FUNCTION TwoYearDateRangePFN(datetime)
AS
RANGE LEFT FOR VALUES ('20021031 23:59:59.997', -- Oct 2002
'20021130 23:59:59.997', -- Nov 2002
'20021231 23:59:59.997', -- Dec 2002
'20030131 23:59:59.997', -- Jan 2003
'20030228 23:59:59.997', -- Feb 2003
'20030331 23:59:59.997', -- Mar 2003
'20030430 23:59:59.997', -- Apr 2003
'20030531 23:59:59.997', -- May 2003
'20030630 23:59:59.997', -- Jun 2003
'20030731 23:59:59.997', -- Jul 2003
'20030831 23:59:59.997', -- Aug 2003
'20030930 23:59:59.997', -- Sep 2003
'20031031 23:59:59.997', -- Oct 2003
'20031130 23:59:59.997', -- Nov 2003
'20031231 23:59:59.997', -- Dec 2003
'20040131 23:59:59.997', -- Jan 2004
'20040229 23:59:59.997', -- Feb 2004
'20040331 23:59:59.997', -- Mar 2004
'20040430 23:59:59.997', -- Apr 2004
'20040531 23:59:59.997', -- May 2004
'20040630 23:59:59.997', -- Jun 2004
'20040731 23:59:59.997', -- Jul 2004
'20040831 23:59:59.997', -- Aug 2004
'20040930 23:59:59.997') -- Sep 2004
GO
맨 왼쪽 및 맨 오른쪽 경계가 모두 포함되어 있으므로 이 분할 함수는 25개의 분할을 만듭니다. 테이블은 비어 있는 상태로 유지되는 25번째 분할을 유지 관리합니다. 제약 조건이 테이블의 데이터를 제한하므로 이 빈 분할에는 아무런 데이터도 상주하지 않기 때문에 특수한 파일 그룹은 필요하지 않습니다. 데이터 위치를 적절한 디스크로 지정하기 위해 분할 스키마를 사용하여 분할을 파일 그룹에 매핑합니다. 분할 스키마는 데이터를 포함할 24개 파일 그룹 각각에 대해 명시적 파일 그룹 이름을 사용하며 25번째의 빈 분할에 대해서는 PRIMARY 파일 그룹을 사용합니다.
CREATE PARTITION SCHEME [TwoYearDateRangePScheme] AS PARTITION TwoYearDateRangePFN TO ( [FG1], [FG2], [FG3], [FG4], [FG5], [FG6], [FG7], [FG8], [FG9], [FG10],[FG11],[FG12], [FG13],[FG14],[FG15],[FG16],[FG17],[FG18], [FG19],[FG20],[FG21],[FG22],[FG23],[FG24], [PRIMARY] ) GO
기본 파일 그룹 또는 사용자 정의 파일 그룹을 분할되지 않은 테이블로 사용하거나 분할된 테이블을 만드는 데 스키마를 사용함으로써 이전 릴리스에서 지원했던 것과 동일한 구문을 사용하여 테이블을 만들 수 있습니다. 어떤 옵션이 더 적합한지는 테이블을 채우는 방법 및 만들 분할 수에 따라 달라집니다. 대개의 경우 힙을 채운 다음 클러스터된 인덱스를 빌드하는 작업을 통해 이미 인덱싱된 테이블로 로드하는 것보다 성능을 향상시킬 수 있습니다. 또한 CPU가 여러 개인 경우에는 병렬 BULK INSERT에서 테이블로 데이터를 로드한 다음 인덱스도 병렬로 빌드할 수 있습니다. Orders 테이블의 경우 일반적인 방법으로 테이블을 만든 다음 데이터를 AdventureWorks 샘플 데이터베이스에서 가져오는 INSERT SELECT 문을 통해 기존 데이터를 로드합니다. Orders 테이블을 분할된 테이블로 만들려면 테이블의 ON 절에서 분할 스키마를 지정합니다. Orders 테이블은 다음 구문을 사용하여 만듭니다.
CREATE TABLE SalesDB.[dbo].[Orders] ( [PurchaseOrderID] [int] NOT NULL, [EmployeeID] [int] NULL, [VendorID] [int] NULL, [TaxAmt] [money] NULL, [Freight] [money] NULL, [SubTotal] [money] NULL, [Status] [tinyint] NOT NULL, [RevisionNumber] [tinyint] NULL, [ModifiedDate] [datetime] NULL, [ShipMethodID] tinyint NULL, [ShipDate] [datetime] NOT NULL, [OrderDate] [datetime] NULL CONSTRAINT OrdersRangeYear CHECK ([OrderDate] >= '20021001' AND [OrderDate] < '20041001'), [TotalDue] [money] NULL ) ON TwoYearDateRangePScheme(OrderDate) GO
OrderDetails 테이블도 이 스키마를 사용할 것이며 OrderDate를 포함해야 하므로 OrderDetails 테이블은 다음 구문을 사용하여 만듭니다.
CREATE TABLE [dbo].[OrderDetails]( [OrderID] [int] NOT NULL, [LineNumber] [smallint] NOT NULL, [ProductID] [int] NULL, [UnitPrice] [money] NULL, [OrderQty] [smallint] NULL, [ReceivedQty] [float] NULL, [RejectedQty] [float] NULL, [OrderDate] [datetime] NOT NULL CONSTRAINT OrderDetailsRangeYearCK CHECK ([OrderDate] >= '20021001' AND [OrderDate] < '20041001'), [DueDate] [datetime] NULL, [ModifiedDate] [datetime] NOT NULL CONSTRAINT [OrderDetailsModifiedDateDFLT] DEFAULT (getdate()), [LineTotal] AS (([UnitPrice]*[OrderQty])), [StockedQty] AS (([ReceivedQty]-[RejectedQty])) ) ON TwoYearDateRangePScheme(OrderDate) GO
데이터를 로드하기 위한 다음 단계는 두 개의 INSERT 문을 통해 처리됩니다. 이러한 문은 복사한 데이터가 있었던 새 AdventureWorks 데이터베이스를 사용합니다. AdventureWorks 샘플 데이터베이스를 설치하여 이 데이터를 복사합니다.
INSERT dbo.[Orders] SELECT o.[PurchaseOrderID] , o.[EmployeeID] , o.[VendorID] , o.[TaxAmt] , o.[Freight] , o.[SubTotal] , o.[Status] , o.[RevisionNumber] , o.[ModifiedDate] , o.[ShipMethodID] , o.[ShipDate] , o.[OrderDate] , o.[TotalDue] FROM AdventureWorks.Purchasing.PurchaseOrderHeader AS o WHERE ([OrderDate] >= '20021001' AND [OrderDate] < '20041001') GO INSERT dbo.[OrderDetails] SELECT od.PurchaseOrderID , od.LineNumber , od.ProductID , od.UnitPrice , od.OrderQty , od.ReceivedQty , od.RejectedQty , o.OrderDate , od.DueDate , od.ModifiedDate FROM AdventureWorks.Purchasing.PurchaseOrderDetail AS od JOIN AdventureWorks.Purchasing.PurchaseOrderHeader AS o ON o.PurchaseOrderID = od.PurchaseOrderID WHERE (o.[OrderDate] >= '20021001' AND o.[OrderDate] < '20041001') GO
데이터가 분할된 테이블로 로드되었으므로 새 기본 제공 시스템 함수를 사용하여 데이터가 상주할 분할을 결정할 수 있습니다. 다음 쿼리는 데이터가 포함된 각 분할에 대해 각 분할 내에 있는 행 수와 최소 및 최대 OrderDate 정보를 반환하므로 유용합니다. 행이 포함되지 않은 분할은 이 쿼리에 의해 반환되지 않습니다.
SELECT $partition.TwoYearDateRangePFN(o.OrderDate) AS [Partition Number] , min(o.OrderDate) AS [Min Order Date] , max(o.OrderDate) AS [Max Order Date] , count(*) AS [Rows In Partition] FROM dbo.Orders AS o GROUP BY $partition.TwoYearDateRangePFN(o.OrderDate) ORDER BY [Partition Number] GO SELECT $partition.TwoYearDateRangePFN(od.OrderDate) AS [Partition Number] , min(od.OrderDate) AS [Min Order Date] , max(od.OrderDate) AS [Max Order Date] , count(*) AS [Rows In Partition] FROM dbo.OrderDetails AS od GROUP BY $partition.TwoYearDateRangePFN(od.OrderDate) ORDER BY [Partition Number] GO
마지막으로, 테이블을 채운 다음 클러스터된 인덱스를 빌드할 수 있습니다. 이 경우 분할 키가 두 테이블을 모두 식별하므로 클러스터된 인덱스는 기본 키에서 정의됩니다. OrderDetails의 경우에는 LineNumber를 인덱스에 추가하여 고유하게 만듭니다. 분할된 테이블에 빌드된 인덱스의 기본 동작은 같은 스키마에서 인덱스를 분할된 테이블과 맞추는 것입니다. 스키마는 지정하지 않아도 됩니다.
ALTER TABLE Orders ADD CONSTRAINT OrdersPK PRIMARY KEY CLUSTERED (OrderDate, OrderID) GO ALTER TABLE dbo.OrderDetails ADD CONSTRAINT OrderDetailsPK PRIMARY KEY CLUSTERED (OrderDate, OrderID, LineNumber) GO
분할 스키마를 지정하는 전체 구문은 다음과 같습니다.
ALTER TABLE Orders ADD CONSTRAINT OrdersPK PRIMARY KEY CLUSTERED (OrderDate, OrderID) ON TwoYearDateRangePScheme(OrderDate) GO ALTER TABLE dbo.OrderDetails ADD CONSTRAINT OrderDetailsPK PRIMARY KEY CLUSTERED (OrderDate, OrderID, LineNumber) ON TwoYearDateRangePScheme(OrderDate) GO
분할된 테이블 조인
맞춘 테이블을 조인할 때 SQL Server 2005는 테이블을 한 단계 또는 여러 단계로 조인할 수 있는 옵션을 제공합니다. 개별 분할을 먼저 조인한 다음 하위 집합을 함께 추가합니다. 분할을 조인하는 방법에 관계없이 SQL Server는 일정 수준의 분할 제거가 가능한지를 항상 평가합니다.
분할 제거
다음 쿼리에서 데이터는 이전 시나리오에서 만든 Order 및 OrderDetails 테이블에서 쿼리됩니다. 쿼리는 3/4분기에서만 정보를 반환합니다. 일반적으로 3/4분기는 주문 처리가 다소 낮은 달이 포함됩니다. 그러나 2004년에는 이 기간에 주문이 가장 많았습니다. 이 경우 3/4분기에 대해 Products와 관련된 동향(주문량 및 주문 날짜)을 파악해 보고자 합니다. 맞춰진 분할 테이블을 조인할 때 분할 제거 기능을 활용하려면 각 테이블의 분할 범위를 지정해야 합니다. 이 경우 Orders 테이블의 기본 키는 OrderDate 및 OrderID의 복합 키이므로 이 두 테이블을 조인하려면 테이블 간에 OrderDate가 같아야 합니다. SARG(검색 인수)가 두 분할된 테이블에 모두 적용됩니다. 이 데이터를 검색할 쿼리는 다음과 같습니다.
SELECT o.OrderID, o.OrderDate, o.VendorID, od.ProductID, od.OrderQty FROM dbo.Orders AS o INNER JOIN dbo.OrderDetails AS od ON o.OrderID = od.OrderID AND o.OrderDate = od.OrderDate WHERE o.OrderDate >= '20040701' AND o.OrderDate <= '20040930 11:59:59.997' GO
그림 14와 같이 실제 또는 예상 실행 계획 출력을 검토할 때는 확인해야 할 몇 가지 주요 요소가 있습니다. 먼저, SQL Server Management Studio를 사용하는 경우 액세스 중인 테이블 중 하나를 가리키면 "예상 실행 수" 또는 "실행 수"가 표시됩니다. 이 경우 한 분기, 즉 3개월분의 데이터가 표시됩니다. 각 달에는 고유한 분할이 있으며 이 데이터를 찾기 위해 각 테이블당 한 번씩 세 번의 실행이 이루어집니다.
.gif "ms345146.sql2k5partition_14a(ko-kr,SQL.90).gif")
그림 14. 실행 수
그림 15와 같이 SQL Server는 불필요한 분할을 모두 제거하고 올바른 데이터가 들어 있는 분할만을 선택합니다. 평가되는 내용에 대해서는 인수 섹션의 PARTITION ID:([PtnIds1017]) 을 검토해 보십시오. "PtnIds1017" 식에 대해 간단하게 설명하겠습니다. 이 식은 이 쿼리에서 액세스하는 분할의 논리적 표현입니다. 실행 계획 위쪽의 상수 검색을 가리키면 VALUES(((21)), ((22)), ((23)))이라는 인수가 표시됩니다. 이는 분할 번호를 나타냅니다.
.gif "ms345146.sql2k5partition_15_big(ko-kr,SQL.90).gif")
그림 15. 분할 제거
각 분할에 있는 데이터만 확인하려면 이전에 사용했던 쿼리를 약간 수정한 버전을 사용하여 분할의 새 기본 제공 시스템 함수에 액세스합니다.
SELECT $partition.TwoYearDateRangePFN(o.OrderDate) AS [Partition Number] , min(o.OrderDate) AS [Min Order Date] , max(o.OrderDate) AS [Max Order Date] , count(*) AS [Rows In Partition] FROM dbo.Orders AS o WHERE $partition.TwoYearDateRangePFN(o.OrderDate) IN (21, 22, 23) GROUP BY $partition.TwoYearDateRangePFN(o.OrderDate) ORDER BY [Partition Number] GO
여기서 분할 제거를 그래픽으로 확인할 수 있습니다. 분할된 테이블 및 인덱스에 대해, 특히 테이블 및 인덱스가 조인 중인 테이블에 맞춰져 있는 경우에는 추가적인 최적화 기술을 사용할 수 있습니다. SQL Server는 각 분할을 먼저 조인하여 다중 조인을 수행할 수 있습니다.
맞춤 테이블 미리 조인
동일한 쿼리 내에서 SQL Server는 분할을 제거할 뿐 아니라 나머지 분할 간에 조인을 개별적으로 실행하기도 합니다. 각 테이블 액세스에 대한 실행 수를 검토하고 병합 조인 관련 정보를 확인하십시오. 병합 조인을 가리키면 병합 조인이 세 번 실행되었음이 표시됩니다.
.gif "ms345146.sql2k5partition_16_big(ko-kr,SQL.90).gif")
그림 16. 분할된 테이블 조인
그림 16에서는 중첩된 루프 조인이 추가로 수행됩니다. 이는 병합 조인 뒤에 수행되는 것처럼 보이지만 실제로 분할 ID는 이미 각 테이블 찾기 또는 검색으로 전달된 상태입니다. 이 마지막 조인은 데이터의 각 부분 집합이 시작 시 상수 검색 식에서 정의된 분할 ID를 따르도록 데이터의 두 부분 집합을 한 곳에서 모은 것일 뿐입니다.
슬라이딩 윈도우(Sliding-Window) 시나리오
다음 달(이 기사에서는 2004년 10월)의 데이터를 사용할 수 있는 경우에는 기존 파일 그룹을 사용하고 데이터를 추가 및 제외하기 위해 따라야 하는 특정 순서의 단계가 있습니다. 이 판매 시나리오에서 현재 FG1에 있는 데이터는 2002년 10월 데이터입니다. 이제 2004년 10월 데이터를 사용할 수 있으므로 사용 가능한 공간 및 보관 요구 사항에 따라 두 가지 옵션 중에서 선택할 수 있습니다. 테이블에서 분할을 빠르게 추가하거나 제외하려면 이러한 추가/제외 작업에서 메타데이터만을 변경해야 함을 기억하십시오. 특히 새 테이블(원본 또는 대상, 즉 가장된 분할)은 추가/제외 작업이 수행되는 것과 같은 파일 그룹에서 만들어야 합니다. 같은 파일 그룹(여기서는 FG1)을 계속 사용할 계획이라면 공간 및 보관 요구 사항을 처리할 방법을 결정해야 합니다. 테이블에 2년분의 데이터가 모두 포함되지 않는 기간을 최소화하려면 공간이 있는 경우에는 보관할 데이터(2002년 10월)를 제거하지 않고도 최신 데이터(2004년 10월)를 FG1에 로드할 수 있습니다. 그러나 공간이 부족하여 당월 및 보관할 달의 데이터를 모두 보관할 수 없다면 먼저 이전 분할을 빼내서 제거하거나 삭제해야 합니다.
물론 보관은 쉬운 작업이며 이미 수행 중일 수도 있습니다. 효과적으로 보관하려면 분할을 제외하기로 한 직전이 아닌 새 분할을 로드하여 교체해 넣은 후에 즉시 파일 그룹을 백업합니다. 예를 들어 RAID 배열에서 오류가 발생한 경우 데이터를 다시 빌드하거나 다시 로드하지 않고 파일 그룹을 복원할 수 있습니다. 특히 이 경우 데이터베이스를 최근에 분할했으므로 분할 구조가 안정화된 후에 전체 백업을 수행했을 수도 있습니다. 전체 데이터베이스 백업만이 유일한 방법은 아닙니다. SQL Server 2005에는 구현 가능한 다양한 백업 전략이 있으며, 이 중 대부분은 백업 및 복원을 보다 세밀하게 제어할 수 있도록 합니다. 데이터의 많은 부분은 변경되지 않으므로 개별 파일 그룹을 로드한 다음 백업할 수 있습니다. 사실 이 작업은 진행 중인 분할 전략의 일부가 되어야 합니다. 자세한 내용은 SQL Server 온라인 설명서의 "SQL Server 관리"에서 "파일 및 파일 그룹 백업"을 참조하십시오.
전략을 지정한 후에는 정확한 프로세스 및 구문을 이해해야 합니다. 구문 및 단계 수는 복잡해 보일 수 있지만 프로세스는 매달 같습니다. 동적 SQL 실행을 사용하면 다음 단계를 통해 이 프로세스를 쉽게 자동화할 수 있습니다.
추가해 넣을 분할의 Staging 테이블을 관리합니다.
제외할 분할의 두 번째 Staging 테이블을 관리합니다.
분할 테이블에서 오래된 데이터는 빼고 새 데이터를 넣습니다.
Staging 테이블을 삭제합니다.
파일 그룹을 백업합니다.
각 단계의 구문과 최적의 방법을 비롯하여 동적 SQL 실행을 통해 이 프로세스를 자동화할 수 있도록 하는 참고 사항이 다음 섹션에 설명되어 있습니다.
추가해 넣을 분할의 Staging 테이블 관리
나중에 분할이 될 가장 Staging 테이블을 만듭니다. 이 Staging 테이블에는 해당 데이터를 앞으로 만들 분할에 대해 유효한 데이터로만 제한하는 제약 조건이 있어야 합니다. 성능을 향상시키려면 데이터를 제약 조건이 없고 인덱싱되지 않은 힙에 로드한 다음 테이블을 분할된 테이블로 추가해 넣기 전에 WITH CHECK 제약 조건을 추가합니다(3단계 참조).
CREATE TABLE SalesDB.[dbo].[OrdersOctober2004]
( [OrderID] [int] NOT NULL, [EmployeeID] [int] NULL, [VendorID] [int] NULL, [TaxAmt] [money] NULL, [Freight] [money] NULL, [SubTotal] [money] NULL, [Status] [tinyint] NOT NULL, [RevisionNumber] [tinyint] NULL, [ModifiedDate] [datetime] NULL, [ShipMethodID] [tinyint] NULL, [ShipDate] [datetime] NOT NULL, [OrderDate] [datetime] NOT NULL, [TotalDue] [money] NULL ) ON [FG1] GO
**자동화:** 이 테이블은 항상 최근 달이므로 쉽게 만들 수 있습니다. 프로세스 실행 시기에 따라 DATENAME(m, getdate()) 같은 기본 제공 함수를 사용하여 달을 쉽게 검색할 수 있습니다. 테이블 구조가 기존 테이블과 일치해야 하므로 매달 가장 크게 변경되는 것은 테이블 이름입니다. 그러나 매달 같은 이름을 사용할 수도 있습니다. 분할에 추가한 뒤에는 테이블이 없어도 되기 때문입니다. 데이터를 분할된 테이블에 추가한 후에도 테이블이 존재하기는 하지만 추가 작업이 완료되고 나면 Staging 테이블을 삭제할 수 있습니다. 또한 날짜 범위도 변경되어야 합니다. **datetime** 데이터로 작업 중이고 시간 저장 방법에 대한 반올림 문제점이 있기 때문에 적절한 밀리초 값을 프로그래밍 방식으로 결정할 수 있어야 합니다. 월말에 대한 마지막 **datetime** 값을 찾는 가장 쉬운 방법은 작업 중인 달에 한 달을 더한 다음 거기서 2 또는 3밀리초를 빼는 것입니다. 59.999는 다음 달 첫날인 .000으로 반올림되기 때문에 1밀리초만 빼면 안 됩니다. -2밀리초는 .997로 버림되고 3밀리초는 .997이 되기 때문에 2 또는 3밀리초를 빼는 것입니다. .997은 저장 가능한 유효한 값입니다. 그러면 **datetime** 범위에 대해 정확한 끝 값을 얻을 수 있습니다.
<pre IsFakePre="true" xmlns="http://www.w3.org/1999/xhtml">DECLARE @Month nchar(2),
@Year nchar(4), @StagingDateRange nchar(10) SELECT @Month = N'11', @Year = N'2004' SELECT @StagingDateRange = @Year + @Month + N'01' SELECT dateadd(ms, -2, @StagingDateRange)
테이블은 데이터가 추가 및 제외되는 파일 그룹에서 상주해야 하므로 매달 새로 만들어집니다. 작업할 적절한 파일 그룹을 결정하려면 다음 시스템 테이블 쿼리를 앞서 제시되었던 **$partition** 함수와 결합하여 사용하십시오. 현재 제외 중인 범위 내의 날짜를 지정해야 합니다. 이는 모든 작업을 수행할 분할 및 파일 그룹입니다. 사용 중인 특정 테이블, 분할 함수 및 특정 날짜에 적용할 때는 밑줄이 그어진 섹션을 변경해야 합니다.
<pre IsFakePre="true" xmlns="http://www.w3.org/1999/xhtml">SELECT ps.name AS PSName,
dds.destination_id AS PartitionNumber, fg.name AS FileGroupName FROM (((sys.tables AS t INNER JOIN sys.indexes AS i ON (t.object_id = i.object_id)) INNER JOIN sys.partition_schemes AS ps ON (i.data_space_id = ps.data_space_id)) INNER JOIN sys.destination_data_spaces AS dds ON (ps.data_space_id = dds.partition_scheme_id)) INNER JOIN sys.filegroups AS fg ON dds.data_space_id = fg.data_space_id WHERE (t.name = 'Orders') AND (i.index_id IN (0,1)) AND dds.destination_id = $partition.TwoYearDateRangePFN('20021001')
데이터와 함께 Staging 테이블을 로드합니다. 파일이 일정하면 이 프로세스는 BULK INSERT 문을 통해 처리됩니다.
자동화: 이 프로세스는 자동화가 가장 복잡합니다. 모든 파일이 로드되었는지 확인해야 하며, 파일을 병렬로 로드하는 것을 고려해야 합니다. 로드된 파일 및 파일의 위치를 추적하는 테이블을 통해 이 프로세스를 제어할 수 있습니다. 몇 분에 한 번씩 파일을 확인하고 새 파일을 선택한 다음 다중 대량 삽입 문을 실행하는 SQL Agent 작업을 만들 수 있습니다.
데이터가 로드되고 나면 제약 조건을 추가할 수 있습니다. 데이터를 트러스트된 상태로 만들려면 제약 조건에 WITH CHECK를 추가해야 합니다. WITH CHECK 설정은 기본값이므로 지정할 필요가 없지만 WITH NOCHECK는 사용하지 않아야 합니다.
Staging 테이블을 인덱싱합니다. 이 테이블은 분할이 될 때 속해 있을 테이블과 같은 클러스터된 인덱스를 가져야 합니다.
ALTER TABLE [OrdersOctober2004]
ADD CONSTRAINT OrdersOctober2004PK PRIMARY KEY CLUSTERED (OrderDate, OrderID) ON [FG1] GO
자동화: 쉬운 단계입니다. 1단계의 달 및 파일 그룹 정보를 사용하여 이 클러스터된 인덱스를 만들 수 있습니다.
<pre IsFakePre="true" xmlns="http://www.w3.org/1999/xhtml">ALTER TABLE SalesDB.[dbo].[OrdersOctober2004]
WITH CHECK ADD CONSTRAINT OrdersRangeYearCK CHECK ([OrderDate] >= '20041001' AND [OrderDate] <= '20041031 23:59:59.997') GO
제외될 분할의 두 번째 Staging 테이블 관리
두 번째 Staging 테이블을 만듭니다. 이는 분할을 제외될 때 해당 데이터를 보관하는 빈 테이블입니다.
CREATE TABLE SalesDB.[dbo].[OrdersOctober2002]
( [OrderID] [int] NOT NULL, [EmployeeID] [int] NULL, [VendorID] [int] NULL, [TaxAmt] [money] NULL, [Freight] [money] NULL, [SubTotal] [money] NULL, [Status] [tinyint] NOT NULL, [RevisionNumber] [tinyint] NULL, [ModifiedDate] [datetime] NULL, [ShipMethodID] [tinyint] NULL, [ShipDate] [datetime] NOT NULL, [OrderDate] [datetime] NOT NULL, [TotalDue] [money] NULL ) ON [FG1] GO
Staging 테이블을 인덱싱합니다. 이 테이블은 분할이 될 속해 있을 테이블과 같은 클러스터된 인덱스를 가져야 합니다. 분할은 이 테이블이 됩니다.
ALTER TABLE [OrdersOctober2002]
ADD CONSTRAINT OrdersOctober2002PK PRIMARY KEY CLUSTERED (OrderDate, OrderID) ON [FG1] GO
분할 테이블에서 오래된 데이터 빼기 및 새 데이터 넣기
이전 데이터를 빼내 두 번째 Staging 테이블로 옮깁니다.
ALTER TABLE Orders
SWITCH PARTITION 1 TO OrdersOctober2002 GO
분할 함수를 변경하여 2002년 10월의 경계 지점을 제거합니다.
ALTER PARTITION FUNCTION TwoYearDateRangePFN()
MERGE RANGE ('20021031 23:59:59.997') GO
그러면 파일 그룹과 분할 스키마 간의 연결도 제거됩니다. 특히, FG1은 더 이상 분할 스키마의 일부분이 아닙니다. 기존과 같은 24개 분할을 통해 새 데이터를 넣으므로 FG1을 분할(split)에 사용되는 다음 분할이 되는 "다음으로 사용되는" 분할이 되도록 해야 합니다.
ALTER PARTITION SCHEME TwoYearDateRangePScheme
NEXT USED [FG1] GO
분할 함수를 변경하여 2004년 10월에 대한 새 경계 지점을 포함합니다.
ALTER PARTITION FUNCTION TwoYearDateRangePFN()
SPLIT RANGE ('20041031 23:59:59.997') GO
기본 테이블에 제약 조건 정의가 있는 경우 변경하여 새 데이터 범위를 허용합니다. 제약 조건을 추가하면 데이터 확인에 많은 작업이 필요할 수 있으므로 제약 조건을 삭제하고 다시 만드는 대신 날짜를 계속 확장하는 것이 좋습니다. 지금은 제약 조건이 OrdersRangeYearCK 하나만 있습니다만 이후 날짜에 대해서는 두 개의 제약 조건이 사용될 것입니다.
ALTER TABLE Orders
ADD CONSTRAINT OrdersRangeMaxOctober2004 CHECK ([OrderDate] < '20041101') GO ALTER TABLE Orders ADD CONSTRAINT OrdersRangeMinNovember2002 CHECK ([OrderDate] >= '20021101') GO ALTER TABLE Orders DROP CONSTRAINT OrdersRangeYearCK GO
첫 번째 Staging 테이블에서 새 데이터를 가져와 추가합니다.
ALTER TABLE OrdersOctober2004
SWITCH TO Orders PARTITION 24 GO
Staging 테이블 삭제
모든 데이터는 다음 마지막 단계에서 보관되므로 Staging 데이터는 필요하지 않습니다. 이러한 테이블을 가장 빨리 제거하는 방법은 테이블을 삭제하는 것입니다.
DROP TABLE dbo.OrdersOctober2002 GO DROP TABLE dbo.OrdersOctober2004 GO
파일 그룹 백업
마지막 단계에서 백업할 내용은 백업 전략에 따라 달라집니다. 파일 또는 파일 그룹 기반 백업 전략을 선택한 경우 파일이나 파일 그룹 백업을 수행해야 합니다. 전체 데이터베이스 기반 백업 전략을 선택한 경우에는 전체 데이터베이스 백업 또는 차등 백업을 수행할 수 있습니다.
BACKUP DATABASE SalesDB FILEGROUP = 'FG1' TO DISK = 'C:\SalesDB\SalesDB.bak' GO
목록 분할: 지역 데이터
테이블에 여러 지역의 데이터가 있고 분석은 보통 한 지역 내에서 이루어지는 경우 또는 각 지역의 데이터를 주기적으로 받는 경우에는 정의된 범위 분할을 목록 형태로 사용할 수 있습니다. 즉, 각 분할을 지역의 값으로 명시적으로 정의하는 함수를 사용하는 것입니다. 예를 들어 스페인, 프랑스, 독일, 이탈리아 및 영국에 고객이 있는 스페인 회사를 가정해 봅시다. 이 회사의 판매 데이터는 항상 국가별로 분석됩니다. 그리고 테이블에는 국가당 하나씩 5개의 분할이 있습니다.
이 목록 분할을 만드는 작업은 범위의 경계에는 실제 분할 키 외의 값은 없다는 것을 제외하고는 날짜에 대한 범위 분할 만들기와 거의 동일합니다. 대신 목록 분할은 범위가 아니라 목록입니다. 목록이라고 해도 경계 조건에는 맨 왼쪽 및 맨 오른쪽이 포함되어야 합니다. 5개의 분할을 만드는 경우 분할 함수에는 4개 분할만 지정합니다. 값의 순서는 SQL Server에서 내부적으로 지정하므로 따로 지정할 필요는 없지만, 정확한 분할 번호를 결정하는 가장 논리적인 방법은 분할 값 순서를 지정한 다음 가장 큰 값을 마지막 분할(LEFT 분할 함수로 정의된 경우)을 위해 남겨 두거나 분할 값 순서를 지정하고 두 번째로 작은 값부터 시작하는 것입니다(RIGHT의 경우).
분할이 5개이므로 5개의 파일 그룹이 있어야 합니다. 이 경우 파일 그룹 이름은 저장되는 데이터 이름에 따라 지정됩니다. 스크립트 파일 RegionalRangeCaseStudyFilegroups.sql은 이 구문의 내용을 모두 보여 주는 스크립트입니다. 각 파일 그룹은 동일한 설정을 사용하여 만들어지지만 데이터 균형이 조정되지 않는 경우에는 반드시 설정이 같지 않아도 됩니다. 스페인에 대한 파일 그룹 및 파일만이 표시되어 있습니다. 추가적인 4개 파일 그룹 및 파일의 매개 변수는 같습니다. 다만 이들은 서로 다른 드라이브에 있으며 특정 국가 분할 이름이 지정됩니다.
ALTER DATABASE SalesDB ADD FILEGROUP [Spain] GO ALTER DATABASE SalesDB ADD FILE (NAME = N'SalesDBSpain', FILENAME = N'C:\SalesDB\SalesDBSpain.ndf', SIZE = 1MB, MAXSIZE = 100MB, FILEGROWTH = 5MB) TO FILEGROUP [Spain] GO
다음 단계는 경계 조건으로 LEFT를 사용하여 4개의 분할만을 지정하는 함수를 만드는 것입니다. 이 경우 목록에는 영문으로 알파벳 순서상 마지막인 영국을 제외한 모든 국가가 포함됩니다.
CREATE PARTITION FUNCTION CustomersCountryPFN(char(7))
AS
RANGE LEFT FOR VALUES ('France', 'Germany', 'Italy', 'Spain')
GO
이름을 따온 파일 그룹에 데이터를 넣기 위해 분할 스키마는 알파벳순으로 나열됩니다. 5개 파일 그룹은 모두 분할 스키마 구문 내에서 지정되어야 합니다.
CREATE PARTITION SCHEME [CustomersCountryPScheme] AS PARTITION CustomersCountryPFN TO ([France], [Germany], [Italy], [Spain], [UK]) GO
이제 Customers 테이블을 새 CustomersCountryPScheme에서 만들 수 있습니다.
CREATE TABLE [dbo].[Customers]( [CustomerID] [nchar](5) NOT NULL, [CompanyName] [nvarchar](40) NOT NULL, [ContactName] [nvarchar](30) NULL, [ContactTitle] [nvarchar](30) NULL, [Address] [nvarchar](60) NULL, [City] [nvarchar](15) NULL, [Region] [nvarchar](15) NULL, [PostalCode] [nvarchar](10) NULL, [Country] [char](7) NOT NULL, [Phone] [nvarchar](24) NULL, [Fax] [nvarchar](24) NULL ) ON CustomersCountryPScheme (Country) GO
범위 분할은 범위만을 지원하는 것으로 정의되지만 목록 분할 등 다른 형식의 분할을 수행하는 방법도 제공합니다.
요약
SQL Server 2005는 분할을 통해 대형 테이블 및 인덱스를 쉽고 일관성 있게 관리하는 방법을 제공합니다. 또한 분할을 사용하여 활성 테이블 외부에서 데이터 하위 집합을 관리할 수 있습니다. 분할 스키마는 응용 프로그램으로부터 완전히 독립적이므로 이로 인해 관리가 단순화되고 성능이 향상되며 응용 프로그램 논리가 간단해집니다. 데이터에 논리적 그룹화(범위 또는 목록)가 있고 대형 쿼리가 이러한 미리 정의된 일관성 있는 범위 내에서 해당 데이터를 분석하고 들어오는 데이터 및 나가는 데이터를 관리해야 하는 경우 범위 분할을 선택하는 것이 간편합니다. 사용할 특정 범위가 없는 대량의 데이터에 대해 분석을 수행하거나 모든 쿼리가 전체 또는 대부분의 데이터에 액세스하는 경우에는 특정 배치 기술 없이 다중 파일 그룹을 사용하는 것이 보다 쉽고 성능도 향상됩니다.
이 백서의 스크립트
이 백서의 코드 샘플에서 사용되는 스크립트는 SQLServer2005PartitionedTables.zip 파일에 들어 있습니다. 다음은 압축 파일에 있는 각 파일에 대한 설명입니다.
RangeCaseStudyScript1-Filegroups.sql-범위 분할된 테이블 사례 연구에 필요한 파일 그룹 및 파일을 만드는 구문이 들어 있습니다. 이 스크립트는 수정이 가능하므로 크기가 작은 파일(GB가 아닌 MB 단위)이 있는 작은 디스크 집합에서 이 샘플을 만들 수 있습니다. 또한 INSERT...SELECT를 통해 데이터를 가져오는 코드가 있으므로 적절한 분할 함수를 통해 데이터가 배치되는 위치를 평가할 수 있습니다.
RangeCaseStudyScript2-PartitionedTable.sql-범위 분할 테이블 사례 연구와 연관된 분할 함수, 분할 스키마 및 범위 분할 테이블을 만드는 구문이 들어 있습니다. 또한 이 스크립트에는 적절한 제약 조건 및 인덱스도 포함됩니다.
RangeCaseStudyScript3-JoiningAlignedTables.sql-SQL Server가 분할 테이블에 대해 제공하는 다양한 조인 전략을 보여 주는 쿼리가 들어 있습니다.
RangeCaseStudyScript4-SlidingWindow.sql-범위 분할 테이블 사례 연구의 월별 관리와 연결된 구문 및 프로세스가 들어 있습니다. 이 스크립트에서는 데이터를 Orders 테이블 내외부로 "슬라이드"합니다. 또한 선택적으로 OrderDetails 테이블 내외부로 데이터를 이동하는 동일한 프로세스를 직접 만들 수도 있습니다. 힌트: OrderDetails에 삽입할 테이블 및 정확한 데이터 열은 RangeCaseStudyScript2에서 사용된 Insert를 참조하십시오.
RegionalRangeCaseStudyFilegroups.sql-지역별로 분할된 테이블 사례 연구에 필요한 파일 그룹 및 파일을 만드는 구문이 들어 있습니다. 사실 이것은 목록 분할 스키마를 시뮬레이션하기 위한 범위 분할입니다.
RegionalRangeCaseStudyPartitionedTable.sql-범위 분할 테이블 사례 연구와 연관된 분할 함수, 분할 스키마 및 지역별로 분할된 테이블을 만드는 구문이 들어 있습니다.