테이블 파티션을 사용하면 데이터 하위 집합을 빠르고 효율적으로 관리 및 액세스하는 동시에 데이터 컬렉션의 무결성을
유지할 수 있으므로 큰 테이블 또는 인덱스를 보다 편리하게 관리할 수 있습니다. 테이블 파티션을 사용하면 이전 릴리스에서는 몇 분 내지 몇 시간이 걸렸던 작업(예: OLTP에서 OLAP 시스템으로 데이터 로드)이 몇 초 안에 끝납니다.
데이터 하위 집합에서 수행되는 유지 관리 작업도 보다 효율적으로 수행할 수 있습니다. 전체 테이블 대신 필요한 데이터만
대상으로 삼기 때문입니다.
참고 분할 테이블 및 인덱스는 Microsoft SQL Server 2005 Enterprise Edition 및 Developer Edition에서만 지원됩니다.
분할된 테이블 및 인덱스의 데이터는 데이터베이스에서 두 개 이상의 파일 그룹으로 분할될 수 있는 단위로 나누어집니다. 행 그룹이 개별 파티션에 매핑되도록 데이터는 수평적으로 분할됩니다. 데이터에서 쿼리나 업데이트가 수행되면 테이블이나 인덱스는 단일 논리적 엔터티로 처리됩니다. 단일 인덱스나 테이블의 모든 파티션은 동일 데이터베이스에 상주해야 합니다.
분할된 테이블 및 인덱스는 제약 조건, 기본값, ID 및 타임스탬프 값, 트리거 등 표준 테이블 및 인덱스를 디자인하고 쿼리하는 작업과 관련된 모든 속성과 기능을 지원합니다. 따라서 한 서버의 로컬에만 있는 분할된 뷰를 구현하려는 경우에는 분할된 테이블을 대신 구현할 수도 있습니다.
현재 테이블의 크기, 증가된 테이블의 크기, 테이블의 사용 방법, 테이블이 사용자 쿼리 및 유지 관리 작업을 얼마나 잘 수행하는지에 따라 분할을 구현할지 여부를 결정합니다.
일반적으로 큰 테이블은 다음의 두 조건이 모두 충족될 때 분할에 적합합니다.
▷ 테이블에 서로 다른 방법으로 사용되는 데이터가 많거나 많아질 것으로 예상됩니다.
▷ 테이블에 대한 쿼리나 업데이트가 의도 대로 수행되지 않거나 유지 관리 비용이 미리 정의된 유지 관리 기간을 초과합니다.
예를 들어 데이터가 이번 달의 경우 주로 INSERT, UPDATE 및 DELETE 작업에 사용되는 반면 이전 달의 경우 SELECT 쿼리에 사용된 경우에는 테이블을 월별로 분할하는 것이 관리하기에 더 수월할 수도 있습니다. 테이블의 정기적인 유지 관리 작업에서 데이터 하위 집합만을 대상으로 해야 하는 경우에 이러한 이점이 특히 부각됩니다. 테이블이 분할되어 있지 않으면 이러한 작업이 전체 데이터 집합에서 많은 리소스를 소비하게 됩니다. 예를 들어 분할을 사용하면 인덱스 다시 작성 및 조각화 모음 같은 유지 관리 작업을 단일 월의 읽기 전용 데이터에서 수행할 수 있습니다. 이때에도 읽기 전용 데이터는 여전히 온라인 액세스가 가능합니다.
이 예를 좀 더 확장하여 분석을 위해 1개월의 읽기 전용 데이터를 이 테이블에서 데이터 웨어하우스 테이블로 이동하려는 경우를 가정해 보십시오. 분할을 사용하면 데이터 하위 집합을 오프라인 유지 관리를 위한 준비 영역으로 빠르게 나눈 다음 기존 분할 테이블에 파티션으로 추가할 수 있습니다. 이때 이러한 테이블은 모두 동일한 데이터베이스 인스턴스에 있다고 가정합니다. 이전 릴리스에서 몇 분에서 몇 시간까지 걸렸던 이러한 작업은 일반적으로 몇 초밖에 걸리지 않습니다.
테이블 파티션을 만들기 위해서는 다음과 같은 순서로 작업하면 데이타를 분할해서 테이블에 저장할 수 있습니다.
일반적으로 작업하는 경우에는 테이블을 생성하고 Insert를 계속 수행하면 단순하게 입력됩니다. 이런 일반 테이블과는 달리 파티션 테이블에서는 사전 작업이 필요합니다.
필요하다면 파일 그룹을 생성해서 서로 다른 디스크 드라이브에 저장 공간을 할당하고 파티션 함수와 파티션 스키마를 적용시켜서 필요한 규칙대로 데이터가 분할되어서 저장되게 합니다.
1) 파일그룹 생성
우선 테이블을 나누기 휘한 파티션 그룹이 필요하다.
USE TP_Test
GO
ALTER DATABASE TP_Test ADD FILEGROUP fg1
ALTER DATABASE TP_Test ADD FILEGROUP fg2
ALTER DATABASE TP_Test ADD FILEGROUP fg3
ALTER DATABASE TP_Test ADD FILEGROUP fg4
ALTER DATABASE TP_Test
ADD FILE
( NAME = data1,
FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Test1.ndf',
SIZE = 1MB,
MAXSIZE = 100MB,
FILEGROWTH = 1MB)
TO FILEGROUP fg1
ALTER DATABASE TP_Test
ADD FILE
( NAME = data1,
FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Test2.ndf',
SIZE = 1MB,
MAXSIZE = 100MB,
FILEGROWTH = 1MB)
TO FILEGROUP fg2
ALTER DATABASE TP_Test
ADD FILE
( NAME = data1,
FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Test3.ndf',
SIZE = 1MB,
MAXSIZE = 100MB,
FILEGROWTH = 1MB)
TO FILEGROUP fg3
ALTER DATABASE TP_Test
ADD FILE
( NAME = data1,
FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Test4.ndf',
SIZE = 1MB,
MAXSIZE = 100MB,
FILEGROWTH = 1MB)
TO FILEGROUP fg4
하나의 데이타 베이스에 FILEGROUP 을 fg1~fg4까지 4개로 생성을 합니다.
2) PARTITION FUNCTION 생성
파티션 생성 함수 문법은 다음과 같습니다.
CREATE PARTITION FUNCTION Partition_Function (InputType)
AS RANGE [LEFT | RIGHT]
FOR VALUES (Value1,[Value2],...)
테이블 파티션 작업을 하기 위해서는 위와 같이 우선 테이블 파티션 생성 함수가 필요합니다.
Partition_Function : 파티션 함수명
InputType : 기준이 되는 파라메터 타입
Range : 구분 기준(기본값은 LEFT)
VALUES : 구분값(1개이상은 적용해아 분할이 됨)
위 문법을 적용한 예제는 아래와 같습니다.
CREATE PARTITION FUNCTION CalenderPF (NVARCHAR(4))
AS RANGE LEFT
FOR VALUES ('4월','7월','10월')
위와 같이 함수를 생성하면 월별로 입력받는다고 가정을 한다면
1~3월까지는 첫번째 파티션에,
4~6월까지는 두번째 파티션에,
7~9월까지는 세번째 파티션에,
10~12월까지는 네번째 파티션에 저장됩니다.
모두를 합하면 전체 데이타가 되고 각각 파티션 별로 분기별 데이타가 됩니다.
그리고 AS RANGE LEFT 는 각 파티션별 구분값을 앞에서 비교할것인지 뒤에서 비교할것인지를 확인 하는 기준값입니다.
3) PARTITION SCHEME 생성
파티션 스키마 생성 문법은 다음과 같습니다.
CREATE PARTITION SCHEME Partition_Schema
AS PARTITIOIN Partition_Function
[ALL] TO (File_Group1|[PRIMERY],[File_Group2],...)
파티션 함수가 생성되면 그다음은 위와 같이 스키마를 생성해야 합니다.
Partition_Schema : 파티션 스키마명
Partition_Function : 파티션 함수명
FileGroup : 파티션을 나눌 파일 그룹
위 문법을 적용한 예제는 다음과 같습니다.
CREATE PARTITION SCHEME CalenderPS
AS PARTITIOIN CalenderPF
TO (fg1, fg2, fg3, fg4)
위와 같이 스키마를 생성하면 파일 그룹별로 파티션이 생성됩니다.
파일 그룹을 동일 드라이브로 잡아도 되고 그룹별로 다른 드라이브를 잡아서 작업을 하셔도 됩니다.
4) 파티션 테이블 생성
마지막으로 테스트할 파티션 테이블을 생성하면 됩니다.
CREATE TABLE tempdb.TestCalender
( Seq INT,
Month NVARCHAR(4))
ON CalenderPS ( Month )
테이블 생성시 위에서 만들어준 파티션 스키마와 파티션을 나눌 파라메터 값을 등록합니다.
이제 모든 준비는 끝났습니다.
테스트를 위해 데이타를 등록해 보겠습니다.
INSERT tempdb.TestCalender VALUES(1, '2월')
INSERT tempdb.TestCalender VALUES(1, '4월')
INSERT tempdb.TestCalender VALUES(1, '8월')
INSERT tempdb.TestCalender VALUES(1, '12월')
데이타를 모드 등록하고 등록된 정보를 조회하면 파티션 별로 데이타가 등록된걸 볼수 있습니다.
SELECT Month, $partition.CalenderPF(Month) FileGroup
FROM tempdb.TestCalender
위와 같이 SELECT 조회를 하면 데이타 별로 입력된 파티션 정보가 나타납니다.